Postgresql "no pg_hba.conf entry" error

Question

I'm setting up my local postgresql database for a rails project (that I'm joining), and have done the following:

• Installed postgres
• Added/bundled 'pg' gem
• Created a db user named "foobar"
• Created a db named "foobar_development"

The rails app comes with a rake db:migrate task. When I run this task, I get the following error output:

FATAL:  no pg_hba.conf entry for host "::1", user "foobar", database "foobar_development", SSL off

I found a pg_hba.conf file in the following location:

/usr/local/var/postgres

Here's the relevant part of the pg_hba.conf file:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.

#local   replication     jimmyli                                trust
#host    replication     jimmyli        127.0.0.1/32            trust
#host    replication     jimmyli        ::1/128                 trust

I am using the postgres that came with my Mac (version 9.2.4), although I'm worried I might have installed another version of it somewhere else (and ought to remove it - anyway I can check this?). I am also using Postgres.app to run psql. (http://postgresapp.com/).

I think my pg_hba.conf file ought to work correctly (since I'm giving all users access to all dbs), so I wonder if there's another pg_hba.conf file out there, and that I'm looking at the wrong one/one that's not being used. How can I test this hypothesis?

Answer 1

Set the following environment variable in command line to require SSL for Postgres connections:

$ export PGSSLMODE=require

Answer 2

Got it! Looks like I was looking at the wrong/irrelevant pg_hba.conf file. To find the right one, I logged into the db with psql and ran "SHOW hba_file"

This gave me the path to the relevant file, which in my case was:

/Library/PostgreSQL/9.1/data

I then added the right lines (see my question) to this question, and everything worked!.

Answer 3

Did you reload postgresql after making the change to your pg_hba.conf file? Try logging into the db as a superuser and issue select pg_reload_conf(); to reload the pg_hba.conf and postgresql.conf files.

Answer 4

pg_hba.conf is supposed to be copied to your data directory. According to the list here, it should be in ~/Library/Application\ Support/Postgres/var. Hope that helps...