PostgreSQL pgAdmin III SSL connection to AWS RDS instance

6
votes

I am trying to connect to RDS running on AWS (Amazon Web Services) using SSL. I saw limited info in PosgreSQL pgAdmin III docs about fields on SSL tab.

RDS instances are setup to accept SSL connections by default.

I've downloaded the public key from Amazon and converted it from a .pem to a .crt file using openSSL. On the SSL tab in pgAdmin III I entered path to converted key file "Server Root Certificate File" field.

I can connect to instance without issue but there is no indication that the data is being transferred over SSL. AWS does not set their RDS instances to use SSL exclusivly so I may be connected without using SSL and not know it.

Does pgAdmin III show any indication when it's connected using SSL (like a lock icon)? Can anyone provide additional info that describes the fields (SSL dropdown, Client Cert File, Client Key) on the SSL tab in pgAdmin III?

Thanks.

2
postgresqlsslamazon-web-services

2 Answers

-1
votes

I have not used SSL with PGAdmin on AWS, but I have on a server, and I can tell you that you know when you are connected to a server via PGAdmin, I'm not sure how there is ambiguity there, can you see the databases, tables?

The quoted post below might help you with connecting to a server via SSL.

On the client, we need three files. For Windows, these files must be in %appdata%\postgresql\ directory. For Linux ~/.postgresql/ directory. root.crt (trusted root certificate) postgresql.crt (client certificate) postgresql.key (private key)

Generate the the needed files on the server machine, and then copy them to the client. We'll generate the needed files in the /tmp/ directory.

First create the private key postgresql.key for the client machine, and remove the passphrase.

openssl genrsa -des3 -out /tmp/postgresql.key 1024

openssl rsa -in /tmp/postgresql.key -out /tmp/postgresql.key

Then create the certificate postgresql.crt. It must be signed by our trusted root (which is using the private key file on the server machine). Also, the certificate common name (CN) must be set to the database user name we'll connect as.

openssl req -new -key /tmp/postgresql.key -out /tmp/postgresql.csr -subj '/C=CA/ST=British Columbia/L=Comox/O=TheBrain.ca/CN=www-data'

openssl x509 -req -in /tmp/postgresql.csr -CA root.crt -CAkey server.key -out /tmp/postgresql.crt -CAcreateserial

Copy the three files we created from the server /tmp/ directory to the client machine.

Copy the trusted root certificate root.crt from the server machine to the client machine (for Windows pgadmin %appdata%\postgresql\ or for Linux pgadmin ~/.postgresql/). Change the file permission of postgresql.key to restrict access to just you (probably not needed on Windows as the restricted access is already inherited). Remove the files from the server /tmp/ directory.

From: http://www.howtoforge.com/postgresql-ssl-certificates

-1
votes

First, login as your postgresql admin user then run the following to install sslinfo on RDS:

create extension sslinfo;

To verify if you're connected via ssl simply run the following query in your session:

select ssl_is_used();

If it returns true (t), then you're connected via SSL.