2

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)

Veedrac
  • 58,273
  • 15
  • 112
  • 169
Jeff Andersen
  • 332
  • 8
  • 14
  • This doesn't make a ton of sense. You're using Heroku, so how can you modify `pg_hba.conf`? Did you modify the `pg_hba.conf` of an unrelated PostgreSQL server on your local computer? Or are you not in fact trying to use Heroku? – Craig Ringer Oct 02 '14 at 02:52

1 Answers1

4

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • doh! I knew it had to be something silly. I just got into a rabbit hole of answers regarding the error I was receiving and didn't click that obviously the pg_hba.conf wouldn't affect my connection. Will test this out this morning then accept your answer. – Jeff Andersen Oct 02 '14 at 12:14
  • This worked, turns out I also had an issue with the username being supplied. Chalk this up to staring at this problem for too long. Thanks, marking accepted. – Jeff Andersen Oct 02 '14 at 13:44
  • @JeffAndersen That'd usually happen if you didn't specify a username, so the default (the unix user psycopg2 currently runs as) is picked. Perhaps you're making a mistake when assigning `user=` somehow? (Apparently you figured it out while I was writing this and edited the comment, *shrug*) – Craig Ringer Oct 02 '14 at 13:44