Thread: Connection string parameter "sslrootcert" does not work

On 11/10/2014 04:25 PM, Ed Hutchinson wrote:
> Hi,
>      I am using the psqlODBC driver to connect to Amazon RDS. I am able
> to connect successfully after enabling SSL encryption via the connection
> string parameter "sslmode=require". I want to now enable verification of
> server identity too, which means that I need to provide the CA
> certificate path to the Postgres driver. I tried the connection
> parameters "sslmode=verify-full;sslrootcert=<path to CA file>", but the
> driver is not able to pick up the file from the specified path (I tried
> on Windows and Mac OS X). Things work, however, when the certificate is
> placed in the default place the driver looks in -
> %APPDATA%\Roaming\postgresql\root.crt on Windows; ~/.postgresql/root.crt
> on Mac.
>
>      Is this a bug that needs to be fixed or am I doing something wrong?
> I am using psqlodbc version 09_03_0300-1.

Not sure, how are you specifying the path to the certificate?

>
> Thanks,
> Ed
>
> The resources I consulted:
> http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.SSL
> http://www.postgresql.org/docs/9.3/static/libpq-ssl.html
> http://www.postgresql.org/docs/9.3/static/libpq-connect.html


--
Adrian Klaver
adrian.klaver@aklaver.com

On 11/11/2014 09:52 AM, Ed Hutchinson wrote:
> Thanks, Adrian.
>
>
> Sorry, I should have provided more details.
>

> I get back:
> root certificate file \"/Users/edhutch/.postgresql/root.crt\" does not
> exist\nEither provide the file or change sslmode to disable server
> certificate verification.

Have you tried doing a psql connection using a redirected sslrootcert to
determine if this a more general problem?

Have you tried to quote the path names?


>
> When I rename the pem file to root.crt and place it in the default
> location that the driver expects, the connection goes through fine.
>

>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Thanks, Adrian.

Sorry, I should have provided more details. 

1) Using this connection string on Windows:

DRIVER={PostgreSQL Unicode};DATABASE=dbedhTest;SERVER=edhpostgresql.cn4dj2uqcnwe.us-west-1.rds.amazonaws.com;UID=MyUser;PWD=********;PORT=5432;BOOLSASCHAR=0;LFCONVERSION=0;UseDeclareFetch=1;sslmode=verify-full;sslrootcert=D:\\temp\\rds-ssl-ca-cert.pem

I get back:

root certificate file \"C:\\Users\\edhutch\\AppData\\Roaming/postgresql/root.crt\" does not exist\nEither provide the file or change sslmode to disable server certificate verification.

2) Using this connection string on Windows:

DRIVER={PostgreSQL Unicode};DATABASE=dbedhTest;SERVER=edhpostgresql.cn4dj2uqcnwe.us-west-1.rds.amazonaws.com;UID=MyUser;PWD=********;PORT=5432;BOOLSASCHAR=0;LFCONVERSION=0;UseDeclareFetch=1;sslmode=verify-full;sslrootcert=D:/temp/rds-ssl-ca-cert.pem

I get back the same error:

root certificate file \"C:\\Users\\edhutch\\AppData\\Roaming/postgresql/root.crt\" does not exist\nEither provide the file or change sslmode to disable server certificate verification.

3) Using this connection string on Mac OS X:

DRIVER={PostgreSQL Unicode};DATABASE=dbedhTest;SERVER=edhpostgresql.cn4dj2uqcnwe.us-west-1.rds.amazonaws.com;UID=MyUser;PWD=********;PORT=5432;BOOLSASCHAR=0;LFCONVERSION=0;UseDeclareFetch=1;sslmode=verify-full;sslrootcert=/Users/edhutch/temp/rds-ssl-ca-cert.pem

I get back:

root certificate file \"/Users/edhutch/.postgresql/root.crt\" does not exist\nEither provide the file or change sslmode to disable server certificate verification.

When I rename the pem file to root.crt and place it in the default location that the driver expects, the connection goes through fine.