Re: Fwd: Connection string parameter sslrootcert does not work

If you can't patch the driver to add a variable for this parameter, a
workaround I've used before is to set up a launcher script that sets
pgsslrootcert as a process scope environment variable. I used a VBScript
and changed the app shortcut to call the script (on Windows). This should
allow multiple connections.

On 19 Jan 2017 7:05 am, "Apurva Paralkar" <apurva12mar(at)gmail(dot)com> wrote:

Yes, I did. But I need to be able to simultaneously connect to multiple
Postgres instances from the same client, each with its own CA certificate.
Hence the need for a way to specify a file path. Having a single
environment variable does not work for me.

On Wed, Jan 18, 2017 at 12:01 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 01/18/2017 11:29 AM, Apurva Paralkar wrote:
>
>> Hi,____
>>
>> __ __
>>
>> I'm trying to programmatically connect to an RDS Postgres instance with
>> SSL enabled, using the psqlodbc driver (Version:
>> postgresql94-odbc-09.03.0400-1PGDG.rhel6.x86_64.rpm). I’m having trouble
>> with the sslrootcert parameter.____
>>
>>
>> ____
>>
>> To enable SSL for a Postgres connection, I appended the following
>> parameters to the connection string:____
>>
>> sslmode=verify-ca;sslrootcert=<location of root certificate on the
>> client>____
>>
>> The root certificate exists as a .pem file.____
>>
>>
>> ____
>>
>> In addition, I also enabled the debug and comm logs:____
>>
>> debug=1;commlog=1____
>>
>>
>> ____
>>
>> The resulting logs showed the following error:____
>>
>> …____
>>
>> 00028427: 2017-01-17T21:16:57 [SERVER ]I: Going to connect to
>> ODBC connection string: Driver={PostgreSQL
>> Unicode(x64)};Server=<hostname>;Port=-<port>;Database=<datab
>> ase-name>;UseDeclareFetch=1;Fetch=10000;Uid=<username>;Pwd
>> =****;sslmode=verify-ca;sslrootcert=<location
>> of root.pem file on the client>;debug=1;commlog=1____
>>
>> 00028427: 2017-01-17T21:16:57 [SERVER ]E: RetCode: SQL_ERROR
>> SqlState: 08001 NativeError: 101 Message: [unixODBC]root certificate
>> file "/home/<current-user>/.postgresql/root.crt" does not exist____
>>
>> Either provide the file or change sslmode to disable server certificate
>> verification. [122502] ODBC general error.____
>>
>> 00028427: 2017-01-17T21:16:57 [SERVER ]E: Failed to connect
>> [122506] Network error has occurred____
>>
>> …____
>>
>>
>> ____
>>
>> Does this mean the driver cannot recognize the sslrootcert parameter
>> being passed to it? Why does it still refer to the default location of
>> the root certificate? I even tried putting the root certificate in the
>> default location, but it still failed with the same error above.____
>>
>> __ __
>>
>> I was looking up this issue and found a similar thread that was open 3
>> years ago:
>> https://www.postgresql.org/message-id/5462D5AA.2040602%40tpf.co.jp
>> <https://www.postgresql.org/message-id/5462D5AA.2040602%40tpf.co.jp>._
>> _The
>> contributor there had mentioned that there was no option to specify path
>> name. Is that still the case?
>>
>
> In the above did you see the suggestion to use the env variable
> PGSSLROOTCERT?
>
>
>> I found another thread which talked about adding support for the
>> sslxxxxxx
>> parameters: https://www.postgresql.org/message-id/CAB7nPqSF%2BVLH5TB0rDP
>> F2UaMhjoBCJSJNCeL9NYh6WqEuPUL7w%40mail.gmail.com
>>
>> __ __
>>
>> Is there an update on this?
>>
>>
>> Thanks,____
>>
>> Apurva____
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>