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.'*

How easy or difficult is it to patch the driver to add a variable for this
parameter? Does something in the driver inherently prevent us from adding
these parameters?
I had simplified my setup for the sake of the post. In reality, the client
application has a single process which initiates the connections. Once the
connections are tested successfully, it spins multiple processes for
whatever work it is supposed to be doing. Since it's a single process, it
does not make sense to use process scope variables. Also, the idea of
multiple processes to simply test connections seems like an overkill.

On Wed, Jan 18, 2017 at 12:05 PM, 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
>>
>
>