Debian – Allow two login methods for the same user in PostgreSQL

debianpostgresql

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?

Best Answer

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
Related Topic