Securely connecting to an AWS RDS Postgres database

It took me a little bit of investigation on how to securely connect to an AWS RDS Postgres database from my Mac.

Get your RDS Postgres instance up and running, make sure you have the Internet Gateway Route added to your Route Table, otherwise you won’t be able to connect to it.

Install psql on your Mac, I use homebrew:

brew install postgresql

Download the AWS RDS pem file from the Amazon website and store it somewhere on your local machine. I’ve renamed it to awsrds.pem and stored it in the following location:

~/.ssh/awsrds.pem

When you follow the AWS documentation then the examples show you to use command line flags:

psql \
   --host=<DB instance endpoint> \
   --port=<port> \
   --username <master user name> \
   --password <master user password> \
   --dbname=<database name>

When you then try to add:

--slrootcert=/Users/lee/.ssh/awsrds.pem
--sslmode=verify-full

It will give you the following error:

psql: unrecognized option ` — sslmode=verify-full’

That’s because the sslmode option is only possible when you start the psql connection using a connection string:

psql "host=<DB instance endpoint> port=<port> user=<master user name> dbname=<database name> sslmode=verify-full sslrootcert=<path>"

Some useful links: