Re: Fwd: Connection string parameter sslrootcert does not work

A few years ago I wrote a patch to add a pgservice parameter (link below),
and I'd never written any C before that, so I'd say it's not difficult.
Adding a parameter was more or less a matter of adding to the list of
key/values processed and passed to the libpq connection call. Note that the
patch may not work directly anymore since it's diff'd against a rather old
commit, but it should demonstrate the principle.

https://www.postgresql.org/message-id/attachment/45215/add_service_dsn_parameter.patch

On 19 January 2017 at 13:08, Apurva Paralkar <apurva12mar(at)gmail(dot)com> wrote:

> *'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/mes
>>>> sage-id/CAB7nPqSF%2BVLH5TB0rDPF2UaMhjoBCJSJNCeL9NYh6WqEuPUL7
>>>> w%40mail.gmail.com
>>>>
>>>> __ __
>>>>
>>>> Is there an update on this?
>>>>
>>>>
>>>> Thanks,____
>>>>
>>>> Apurva____
>>>>
>>>>
>>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian(dot)klaver(at)aklaver(dot)com
>>>
>>
>>
>