Connecting to Postgres AWS RDS Instance - “certificate verify failed”

Question

I started playing with AWS RDS a bit, I'm trying to connect my python client (local host - python 2.7, ubuntu 14.04) to the AWS RDS Postgres instance, using the certificate generated by AWS (during the instance launch).

    conn = psycopg2.connect(database='db', user='db_user', password="pw",
        host='xxxxx.rds.amazonaws.com', port='5432', sslmode='verify-full',
        connect_timeout=10, sslrootcert = 'rds-combined-ca-bundle.pem')

But everytime I'm trying to connect I'm getting

    Error SSL error: certificate verify failed

I'm not sure what I'm missing here, perhaps something related to the security groups? Am I using the wrong certificate? If so, how do I create the right one?

Answer 1

This is not an answer, but rather a suggestion: try fiddling with your security groups on their inbound rules. Start by making sure that you have a port open for postgres to your IP address. If that fails, briefly test opening to everything and, whether that succeeds for fails, take the rule off again. But if it succeeds you'll at least know the cause is to do with your communications protocols and not your credentials. This is what just worked for me today. --Now I myself am trying to figure out why I can connect when all connections are accepted, but not via my client IP address. HTH.

Answer 2

In my case, I use the SQLAlchemy ORM and connect to postgres using psycopg2 (2.7.1).

So I do not need to call psycopg2.connect() directly, but instead set the SQLALCHEMY_DATABASE_URI variable:

SQLALCHEMY_DATABASE_URI='postgres://dbuser:[email protected]:5432/msdb?sslmode=verify-full&sslrootcert=/etc/pki/pem/rds-combined-ca-bundle.pem'.

SQLAlchemy calls psycopg2.connect() on my behalf. This is working for me.

Looking at your code, it should behave the same way.

Per the AWS documentation, it might not work if your application does not accept certificate chains. So you could try to solve the problem by using either the intermediate certificate specific to your region or the PKCS7 certificate bundle instead of rds-combined-ca-bundle.pem, and see if it works for you.