Can not connect to local PostgreSQL

Question

This really looks like a file permissions error. Unix domain sockets are files and have user permissions just like any other. It looks as though the OSX user attempting to access the database does not have file permissions to access the socket file. To confirm this I've done some tests on Ubuntu and psql to try to generate the same error (included below).

You need to check the permissions on the socket file and its directories /var and /var/pgsql_socket. Your Rails app (OSX user) must have execute (x) permissions on these directories (preferably grant everyone permissions) and the socket should have full permissions (wrx). You can use ls -lAd <file> to check these, and if any of them are a symlink you need to check the file or dir the link points to.

You can change the permissions on the dir for youself, but the socket is configured by postgres in postgresql.conf. This can be found in the same directory as pg_hba.conf (You'll have to figure out which one). Once you've set the permissions you will need to restart postgresql.

# postgresql.conf should contain...
unix_socket_directory = '/var/run/postgresql'       # dont worry if yours is different
#unix_socket_group = ''                             # default is fine here
#unix_socket_permissions = 0777                     # check this one and uncomment if necessary.

---

EDIT:

I've done a quick search on google which you may wish to look into to see if it is relavent. This might well result in any attempt to find your config file failing.

http://www.postgresqlformac.com/server/howto_edit_postgresql_confi.html

---

Error messages:

User not found in pg_hba.conf

psql: FATAL:  no pg_hba.conf entry for host "[local]", user "couling", database "main", SSL off

User failed password auth:

psql: FATAL:  password authentication failed for user "couling"

Missing unix socket file:

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Unix socket exists, but server not listening to it.

psql: could not connect to server: Connection refused
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Bad file permissions on unix socket file:

psql: could not connect to server: Permission denied
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

My gut feeling is that this is (again) a mac/OSX-thing: the front end and the back end assume a different location for the unix-domain socket (which functions as a rendezvous point).

Checklist:

• Is postgres running: ps aux | grep postgres | grep -v grep should do the trick
• Where is the socket located: find / -name .s.PGSQL.5432 -ls (the socket used to be in /tmp; you could start looking there)
• even if you locate the (unix-domain) socket, the client could use a different location. (this happens if you mix distributions, or of you have a distribution installed someplace and have another (eg from source) installation elsewhere), with client and server using different rendez-vous addresses.

If postgres is running, and the socket actually exists, you could use:

• psql -h /the/directory/where/the/socket/was/found mydbname

(which attempts to connect to the unix-domain socket)

; you should now get the psql prompt: try \d and then \q to quit. You could also try:

• psql -h localhost mydbname.

(which attempts to connect to localhost (127.0.0.1)

If these attempts fail because of insufficient authorisation, you could alter pg_hba.conf (and SIGHUP or restart) In this case: also check the logs.

A similar question: Can't get Postgres started

Note: If you can get to the psql prompt, the quick fix to this problem is just to change your config/database.yml, add:

host: localhost

or you could try adding:

host: /the/directory/where/the/socket/was/found

In my case, host: /tmp

Try uninstalling the pg gem (gem uninstall pg) then reinstalling -- if you use bundler, then bundle install, else gem install pg. Also, make sure path picks up the right version: Lion has a version of posgresql (prior versions didn't) and it may be in the path before your locally installed version (e.g. MacPorts, homebrew).

In my case: homebrew install of postgresql, updated postgresql, rails, etc. and then got this error. Uninstalling and reinstalling the pg gem did it for me.

The location of the socket file is baked into the gem at compile time. Thus you need to rebuild your pg gem.

gem pristine pg
# or
bundle exec gem pristine pg

This should resolve that particular issue.

If you're getting a similar error:

psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

This might do the trick (it did for me):

initdb /usr/local/var/postgres -E utf8

The directory specified should be different if you're not using OSX/Brew.

Note: This is not the exact error message seen above, but this thread is the first result for that error message.