PostGreSQL Authentication Failure with "trust" option

Question

I'm trying to setup a PostGIS db using PostGreSQL 9.1 on Ubuntu 12.04. I've set up the database, created a user account "jay" with no password, added spatial functionality following section 2.5 here, and set my pg_hba.conf for local to:

# "local" is for Unix domain socket connections only
local   all             all                                     trust

I can can connect to the database using the PGAdminIII, requiring no password to connect, but connection fails when I try to connect via PostGIS or with QuantumGIS. Here's a screencap of the connection window in QuantumGIS:

Looking into the problem a bit, I came across this post which led me to try connecting via the terminal using psql -U jay -h localhost gis. psql prompted me for a password, I hit enter because I had not set a password, and it returned psql: fe_sendauth: no password supplied. I tried again, this time supplying my Ubuntu user password, which returned

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

I then tried setting a password for jay using ALTER USER, and entering this password in the prompt, and this failed as well. Clearly, I am unable to connect. However, I'm having trouble figuring out what troubleshooting steps to take next.

Any ideas??

Answer 1

PostgreSQL supports two different transports for connections: TCP/IP network sockets, and unix domain sockets. These are configured with different entries in pg_hba.conf.

unix domain socket connections are the local entries, and TCP/IP sockets are the host, hostssl and hostnossl entries, as per the pg_hba.conf documentation.

In this case it seems highly likely that you've set local connections to trust but left TCP/IP host entries as md5. If your PostGIS tools are using TCP/IP to localhost instead of unix sockets then they'd be expected to supply a password. Some client libraries (like PgJDBC) don't support unix sockets and some programs will default to TCP/IP even if their client library supports unix sockets, so this isn't something you can control.

Make sure you have a host entry for 127.0.0.1/32 with trust auth and do a pg_ctl reload or otherwise reload/restart Pg.

BTW, please don't use trust when you move into production. It's OK for local access to test databases with no important data but that's about it.