Unable to connect with psycopg2, but can via comma

Question

I'm attempting to connect to a remote Postgres database (in this case a Heroku Postgres instance). I have a Fabric command that does some work against the DB using psycopg2.

My Postgres connection occurs as so:

# conf is a hash derived from the heroku config DATABASE_URL
self.conn = psycopg2.connect(
    database=conf.get('database'), # supplied by heroku
    user=conf.get('username'), # is the database username supplied by heroku
    password=conf.get('password'), # supplied by heroku
    host=conf.get('host'), # e.g ec2-00-000-00-00.compute-1.amazonaws.com
    port=conf.get('port') # 5492
)

Error from running the script:

psycopg2.OperationalError: FATAL:  password authentication failed for user "my-server-user"
FATAL:  no pg_hba.conf entry for host "my-ip-address-here", user "my-server-user", database "database-name-here", SSL off

Investigating into pg_hba.conf, I've temporarily introduced the following line:

host    all             all             trust

And restarted Postgres with

sudo /etc/init.d/postgresql-9.3 restart

But I am still incurring the issue. I am, however, able to simply connect using command line client (even without the change to the pg_hba configuration):

psql -h ec2-00-000-00-00.compute-1.amazonaws.com -p 5492 database-name -W

manually supplying the password.

The code runs locally on my Mac, so there is something that is misconfigured or blocked. I just can't figure out what it may be. Any suggestions welcome.

(Obviously, real world values have been replaced with placeholders in samples above)

Answer 1

Heroku requires that you use SSL.

If you are connecting to Heroku, then:

• Modifying the pg_hba.conf of a PostgreSQL server on the local host will have absolutely zero effect, since that's not the server you are connecting to;

• You must use SSL. Pass the sslmode='require' option to psycopg2.