Postgresql – QGIS PostGIS Authentication Failure with “trust” option

postgispostgresqlUbuntu

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?

Best Answer

Just don't leave the Database field empty in the connection dialog.

When left empty, it will attempt to connect to a database named after your OS login name, which probably doesn't exist in your case.

The Host field should be left empty to use a Unix domain socket connection, as hinted by the snippet of your pg_hba.conf shown in the question.

When you put localhost in this field, it attempts a TCP connection which is not what's called local in the pg_hba.conf terminology, so that the entry local all all trust will not match with such a connection.

Related Solutions

PostgreSQL – using password/md5 authentication with empty password

Indeed an empty password is equivalent to no password so it's not going to be accepted by the server when pg_hba.conf says that a password is required.

Still, there are various ways to avoid inputting the password:

trust method in pg_hba.conf, possibly constrained to a specific user and database
client-side PGPASSWORD environment variable
client-side .pgpass file
dummy password like "test" stored in the same place than the login name

Postgresql – How to dump PostgreSQL database whose owner has no password

See this part of the pg_dump manpage:

   -W, --password
       Force pg_dump to prompt for a password before connecting to a database.
       This option is never essential, since pg_dump will automatically prompt
       for a password if the server demands password authentication.

Don't use -W at all. In your case, it's just confusing.

Also, you need to know that the fact that the server asks for a password or not is not driven by the existence of this password.

It's driven by the server-side pg_hba.conf file that you need to study and possibly modify according to your needs (don't forget to reload the server after modifying it).

EDIT: reviewing your pg_hba.conf. The relevant lines are:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

The 1st line concerns the postgres user. It's irrelevant for your pg_dump command since you're using the santa user with -U santa

The 2nd line concerns any other connection through Unix domain sockets (TYPE column is local). From the client, it means when you do not use -h localhost. It says that if the OS user is the same name than the db user, he doesn't need a password.

The 3rd line says that if -h localhost is used (IPv4 TCP connection), a password will always be asked to the client. The 4th line says the same with IPv6.

Based on this, this command run by the santa OS user should not ask or need a password:

 pg_dump --no-owner myapp_db > myapp_db_backup.sql

-U santa is optional because the db username is taken as the OS user by default.

Best Answer

Related Solutions

PostgreSQL – using password/md5 authentication with empty password

Postgresql – How to dump PostgreSQL database whose owner has no password

Related Question