PostgreSQL Authentication – Cannot Peer Authenticate Using Usermap

authenticationpostgresqlpostgresql-9.4

I am trying to peer authenticate some users using usermap. What I am trying to do is to map user mailreader as you can see:

postgres=# \du
                              List of roles
 Role name  |                   Attributes                   | Member of 
------------+------------------------------------------------+-----------
 mailreader |                                                | {}
 postgres   | Superuser, Create role, Create DB, Replication | {}

Into system's user root,dovecot,postfix. Therefore I edited my pg_ident.conf and put the following content:

mailmap         dovecot                 mailreader
mailmap         postfix                 mailreader
mailmap         root                    mailreader

I also edited the pg_hba.conf and appended with the following content:

local    mail        all                     peer map=mailmap

When I try to connect via sudo psql -U mailreader -d mail command I get:

psql: FATAL: Peer authentication failed for user "mailreader"

error.

I also tried the following configuration:

local    mail            all                                     ident map=mailmap

Without any progress.

May I have some help?

Edit 1

The logs regarding the error is:

2017-06-27 19:10:10 UTC [1188-1] mailreader@mail LOG:  provided user name (mailreader) and authenticated user name (root) do not match
2017-06-27 19:10:10 UTC [1188-2] mailreader@mail FATAL:  Peer authentication failed for user "mailreader"
2017-06-27 19:10:10 UTC [1188-3] mailreader@mail DETAIL:  Connection matched pg_hba.conf line 90: "local   all             all                                     peer"

Best Answer

In the end I had to comment the line:

local   all             all                                     peer

Or to put it below the following line:

local    mail            all                             ident map=mailmap

Related Solutions

Postgresql – Authentification failure for PostgreSQL server (9.3)

I found a temporary solution to my problem. I edited the pg_hba.conf of the 9.3 server to say trust in the first two active lines. And after restarting (sudo service postgresql restart) I can connect to the server again using pgadmin. The downside is that the server might now not be password protected (I will open a separate question if I encounter any problems with that).

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

    # TYPE  DATABASE        USER            ADDRESS                 METHOD

    # "local" is for Unix domain socket connections only
    local   all             all                                     trust
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            md5
    # 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

Postgresql – Connect to PgAdmin III via localhost

The basic misunderstanding: "localhost" is not a local connection. Your "trust" line in pg_hba.conf is not applicable for your connection:

local   all   all   trust

That only applies for connections via Unix-domain socket. Best choice for you is to connect via this route. Per pgAdmin documentation:

The host is the IP address of the machine to contact, or the fully qualified domain name. On Unix based systems, the address field may be left blank to use the default PostgreSQL Unix Domain Socket on the local machine, or be set to an alternate path containing a PostgreSQL socket. If a path is entered, it must begin with a “/”. The port number may also be specified.

Bold emphasis mine.

More about connecting without password in this related answer on SO:
Run batch file with psql command without password

Related Question