Re: [ODBC] Fwd: Connection string parameter sslrootcert does not work - Mailing list pgsql-odbc

'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@gmail.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.klaver@aklaver.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=<database-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%2BVLH5TB0rDPF2UaMhjoBCJSJNCeL9NYh6WqEuPUL7w%40mail.gmail.com

__ __

Is there an update on this?


Thanks,____

Apurva____

--
Adrian Klaver
adrian.klaver@aklaver.com