Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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
share|improve this answer
    
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" –  problemofficer Feb 25 '13 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. –  Daniel Vérité Feb 26 '13 at 11:46
    
I changed the password with ALTER ROLE postgres WITH PASSWORD 'MYPASSWORD'; –  problemofficer Feb 28 '13 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. –  problemofficer Jan 23 at 18:42

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.