0

I have a PostgreSQL server running on a remote Debian Squeeze server. I would like to login with the user postgres using the ident and md5 method at the same time. The former is needed for debian maintenance. The latter for connecting my local postgresql client through ssh port forwarding.

My first aproach was to add to lines with the user postgress like this:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   all         postgres                          ident
local   all         postgres                          md5

This apparently doesn't work and the official documentation explains:

The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no "fall-through" or "backup": if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied.

Because the first three columns are the same postgresql will simply chose the first it encounters.

My workaround is to create a second superuser named root

CREATE ROLE root LOGIN SUPERUSER CREATEROLE CREATEDB PASSWORD 'newpassword';

and put the following into pg_hba.conf:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   all         postgres                          ident
local   all         root                              md5

Is there a better solution without creating the second user?

1 Answer 1

2

Since you're connecting via port forwarding, your connection is going to be a host type connection anyway, and should be configured as such. I assume this connection would be from 127.0.0.1/32 so you'd configure

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   all         postgres                          ident
host    all         postgres    127.0.0.1/32          md5
4
  • Doesn't work unfortunately:# psql --username postgres --password --host localhost Password for user postgres: psql: FATAL: password authentication failed for user "postgres" FATAL: password authentication failed for user "postgres" Feb 25, 2013 at 14:35
  • By default postgres has no password and you need to provide one before connecting with the md5 method, otherwise you get the error message above. Same as if you type a wrong password. Feb 26, 2013 at 11:46
  • I changed the password with ALTER ROLE postgres WITH PASSWORD 'MYPASSWORD'; Feb 28, 2013 at 23:27
  • I tested this on a new server and it works. Must have been some error on my part. I am accepting this answer. It might be also worth noting, that PostgresSQL does not really use ident but falls back on peer since almost nobody has an ident server running. Jan 23, 2014 at 18:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .