PostgreSQL - Password authentication fail after adding group roles

Question

I'm pretty new to PostreSQL. I've spent all morning trying to get user logins working properly, and I'm terribly frustrated by now!

So, I have a PostGIS database, version 9.2, as part of the OpenGeo suite of software. I could access the database with the postgres user, but want to make a group role and user with access to a database so that it can create tables and update/select/delete etc. in that database.

I can create a user, that works, and I can login with that user. I can create a group role and assign privileges to the role. I can then add the user to the group role, and then can NO LONGER LOGIN!

It even got so that when I added the user postgres to the group, that user can't login. I remove the group but the inability to login persists.

Now, I have played with pg_hba.conf a lot. And I can now login as postgres, but only if 'trust' is enabled, and I can't login with any software, such as PGAdminIII.

It would be great to get some advice as to what is going wrong, and to enable authenticated logins again.

pg_hba:

Code:
# Database administrative login by Unix domain socket
local   all             postgres                                trust
local all gisadmin trust

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all         opengeo                           md5
local   all         opengeo                           md5

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host all gisadmin localhost trust


# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

Just as a final note, I would like to be able to login via a SSH tunnel. I could do that originally with the 'postgres' user, but now that I added and removed that user from a group, I can't.

Edit: the error messages...

If I login via SSH tunnel in PGAdmin with the correct password, I get the error "FATAL: password authentication failed for the user ..."

If I try to login via SSH tunnel in PGAdmin without a password, while the trust option is set, I get the error: "error connecting to the server: fe_sendauth: no password supplied".

The first error still happens when logging in locally, just via SSH and psql, but the second one goes away and I can log in.

output of \du+:

output of \dg+

Answer 1

I think this behaviour may be related with pgAdminIII, because i'm having similar issues and it seems every time i connect to my db as postgres using pgAIII and look at the definition tab in user properties, the check box for the "expiring date" is checked and either 1/1/1970 or 31/12/1969 are set as expiring date. The solution proposed by Daniel works, so it's obvious the problem is the expiration of the password.

Seems that this bug was corrected in pgAdmin 1.16.2 as you can see in the changelog:

http://www.pgadmin.org/development/changelog.php

Cheers

Answer 2

In the \du+ output, the Password valid until 1970-01-01 00:00:00+00... look quite suspicious. Strictly speaking, the passwords for gisadmin, postgisrw and postgres are no longer valid so that might explain why password-based authentication methods fail for these accounts.

You may try ALTER USER username valid until 'infinity' on these accounts and see if that solves the problem.

Also when connecting to PG through a SSH tunnel, be aware that the pg_hba.conf entries starting with host are ignored. These entries are only considered for connections to Unix domain sockets. Sometimes users confuse that with connections from localhost to itself.