CODEWITHSUNDEEP
postgresqlamazon-web-servicessslamazon-ec2amazon-rds
TortillaCurtain
TortillaCurtain

Reputation: 531

How do I connect to an AWS PostgreSQL RDS instance using SSL and the sslrootcert parameter from a Windows environment?

We have a Windows EC2 instance on which we are running a custom command line application (C# console app using NpgSQL) to connect to a PostgreSQL RDS instance. Based on the instructions here:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.SSL

we created a new DB parameter group with rds.force_ssl set to 1 and rebooted our RDS instance. We also downloaded and imported to Windows the pem file referenced on the page.

I was able to connect to the RDS instance from my Windows EC2 instance via pgAdmin by specifying SSL mode as Verify-Full. Our command-line application reads connection strings from a file and they look like this now that I've added the sslmode parameter:

Server=OurInstanceAddress;Port=5432;SearchPath='$user,public,topology';Database=OurDatabase;User Id=username;Password=mypassword;sslmode=verify-full;

Using this connection string failed with the error referenced at the bottom of the page:

FATAL: no pg_hba.conf entry for host "host.ip", user "someuser", database "postgres", SSL off

I tried adding the sslrootcert parameter, but I'm not sure if I'm dealing with it properly. I tried using the example (sslrootcert=rds-ssl-ca-cert.pem) and I tried using the name of the pem that I downloaded. I feel like there is something about the path information that I'm giving to the sslrootcert parameter that isn't right, especially in a Windows environment. I've tried using the name, I've tried using the following paths:

- sslrootcert=C:\keys\rds-combined-ca-bundle.pem - single backslash
 - sslrootcert=C:\\\keys\\\rds-combined-ca-bundle.pem - double backslash
 - sslrootcert=C:/keys/rds-combined-ca-bundle.pem - Linux style backslash

All of these produced the same error mentioned above.

Any insight would be appreciated.

Upvotes: 1

Views: 6182

Answers (1)

Peter
Peter

Reputation: 5884

I solved it using the environment variables instead for specifiying cert paths in connection url

 -DPGSSLROOTCERT=/certs/root.crt 
 -DPGSSLKEY=/certs/amazon-postgresql.key 
 -PGSSLCERT=/certs/amazon-postgresql.crt

Although I'm in cygwin. There are some hints in the documentation when using windows here https://www.postgresql.org/docs/9.0/static/libpq-ssl.html

Upvotes: 2

Related Questions