Allow two login methods for the same user in PostgreSQL

Question

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?

Answer 1

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