postgresql - Point Heroku application to AWS RDS database

Question

I need to point my Heroku application to my AWS RDS database. My RDS database is up and running and has a security group with 0.0.0.0/0 access.

Currently, I've removed my Heroku postgreSQL database and I am attempting to point my Heroku application to my RDS database. For some reason my application is crashing. The step that I believe I am on is setting my DATABASE_URL on the Heroku side.

Let's say that my database credentials are:

db instance: mydb
dbname: mydb
user: wcronyn
pass: password

I've tried:

heroku config:set DATABASE_URL=postgres://wcronyn:password@mydb.XXXXXXX.us-east-1.rds.amazonaws.com:5432/mydb

and I've attempted to set the permissions by downloading the .pem file into my config folder and then referencing it:

DATABASE_URL=postgres://wcronyn:password@mydb.XXXXXXX.us-east-1.rds.amazonaws.com:5432/mydb?sslca=config/amazon-rds-ca-cert.pem&sslmode=require&encrypt=true

I have tried these two database urls but my application keeps crashing.

Can someone outline the steps that I need to take to successfully host my RDS database and point my application to it?

Answer 1

The following steps worked for me (Feb 2017), given the following setup:

• AWS RDS Region eu-west-2 (which uses VPC Security Groups, not DB Security Groups)
• Postgres 9.6
• Heroku, hosting a Flask application (eg appname: heroku-app-stage)
• Git with a remote added into the Heroku app (eg remote: stage)
• DATABASE_URL value of postgresql://username:password@awsrdshost:5432/dbname

There are broadly four steps to this:

1. Download and install the Amazon RDS SSL root certificate into your Heroku app
2. Configure your Heroku app to refer to said root certificate
3. Enable SSL on your RDS instance
4. Configure your RDS security group to allow all IP address ranges for Incoming traffic

Download and install Amazon RDS SSL root certificate

1. Download the .pem certificate file from the Amazon RDS link below.
2. Put the file into your app folders (make a note of location, I have placed it in my root folder with my .py files)
3. Commit that file into your git repository, and push said commit into the Heroku remote (git push stage master)
4. Verify that the certificate has been uploaded into the expected path (you can do heroku run bash --app heroku-app-stage to see your files in the dyno)

Configure Heroku to refer to root certificate

1. Via the Heroku dashboard, navigate to heroku-app-stage, go to Settings tab, and click on Reveal Config Vars
2. Update your DATABASE_URL variable by adding ?sslrootcert=rds-combined-ca-bundle.pem&sslmode=require. The new value should now be postgresql://username:password@awsrdshost:5432/dbname?sslrootcert=rds-combined-ca-bundle.pem&sslmode=require

Note that this answer uses a root certificate; there may be other options which may be what you want in which case refer to the following SO:

How to connect to a remote PostgreSQL database with Python

Enable SSL on your RDS instance

1. Via your RDS console, navigate to your instance details and note down the Parameter Group that it is using
2. Go to the Parameter Group screen on the dashboard
3. If you are using the default parameter group, you will need to create another parameter group, as you will not be able to edit the default one.
4. Modify the force_ssl parameter to have value 1 and save.
5. Verify that SSL is now enabled on your RDS instance. If you run psql postgres -h awsrdshost -p 5432 -U username, you should see SSL in the connection details

Configure RDS security group to allow all incoming IP ranges

1. Via your RDS console, check the active Security Group for your instance
2. Navigate to the EC2 console (under Compute > EC2), and select Security Groups
3. Select the relevant security group (from step 1) and go to the Inbound tab at the bottom. You should see a PostgreSQL item listed there. If you hit Edit, you should have an option to change the Source to Anywhere.

Note: instructions are only relevant if you're using an RDS setup that uses VPC Security Groups

That's it!

Links to the reference pages used:

Amazon's guide to SSL on Postgres http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.SSL

Heroku's (very short) guide to Amazon RDS https://devcenter.heroku.com/articles/amazon-rds