Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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:
PostGIS connection

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??

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
Thanks for the help and the good advice! –  Jay Guarneri Mar 16 '13 at 3:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.