SSL connection to AWS RDS Postgresql instance from windows client

Question

I am trying to connect to a PG instance hosted on AWS RDS using a secure SSL connection. Where do I store the public key on windows so that it is automatically applied to the connection?

Please note that when I open PGAdmin it only lets me specify .crt and .key files. Is there a way to point it to .pem files?

I downloaded the public key referenced here: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.SSL

On Linux it is a simple matter of putting this key into the default OpenSSL directory and postgres seems to detect it and take care of the rest itself.

On Windows I have not figured out how to do this or where to store the .pem file or how to point PGAdmin to a .pem file.

Answer 1

If you are using PGAdmin, when you create a new connection there is a tab called SSL[1]. There you can input your pem file.

If you are using psql, put your pemfile on %APPDATA%\postgresql\ directory. See more details at the documentation[2].

Hope it helps.

References:

1. https://www.pgadmin.org/docs/dev/connect.html?highlight=ssl
2. https://www.postgresql.org/docs/9.2/static/libpq-ssl.html

Answer 2

I found the answer! Turns out that the connection was encrypted all along, I just didn't realize it. Boy did that make me feel stupid. I ended up downloading Wireshark and sniffing my packets just to make sure.

Note that this applies to ssl-mode "require", but in order to use ssl-mode "verify-full" you do still need a root cert. In this case I just took the rds-combined-ca-bundle.pem and renamed it from .pem to .crt. This allowed me to point to the file from the SSL tab of the connection properties window in PGAdmin.

Doing this I was able to specify ssl-mode "verify-full" and connect to my instance.

Edit:

By default RDS Postgres WILL accept non-SSL connections. It just happens that PGAdmin was initiating an SSL connection by default.

... if you don't provide the ssl mode then postgres connects with default mode as 'prefer' (please refer to documentation mentioned above), according to which, it will prefer ssl connection, but if not available, it will connect with non-ssl connection as well. Source

To make sure you are always using SSL you can set the parameter rds.force_ssl to be 1 (on). More details.

Answer 3

In pgAdmin:

Just load the pem file as the "Root certificate", in my case, it is a root certificate called ca.pem. Worked for me.

(Not sure about the SSL compression, I just clicked it as well, should work without it.)