PostgreSQL Peer Authentication User Mapping Failed

Question

Logged in to the server as user root, I need to enter a database as user postgres via Unix socket. This is my pg_ident.conf:

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
testmap         postgres                postgres
testmap         root                    postgres

This is my pg_hba.conf:

# TYPE   DATABASE   USER   ADDRESS   METHOD
local    all        all              peer map=testmap

I already did pg_ctl reload, even restarted the postgresql service and the server itself. I'm getting the following when connecting via psql:

[root@<Server> ~]# psql -d postgres
psql: FATAL:  no pg_hba.conf entry for host "[local]", user "root", database "postgres", SSL off

Checked hba_file and ident_file inside the database and can confirm that the locations I'm editing are the correct ones.

Are there any other configurations I'm missing? Do I have to create role/user root in the database?

I've been looking for similar problems here and in stackoverflow for 2 days now and most of them just forgot to include map=<> in pg_hba.conf. I also believe what I did was in line with the instructions in the PostgreSQL manual.

Btw, I'm using PostgreSQL 9.5.12. Thanks

Answer 1

I had the same problem today and it turns out that Postgres will actually try to assume the role passed with the -U parameter. What worked for me was this:

# psql -U postgres -d postgres

Then, the authentication will use your socket peer username (root), but you will become postgres upon authenticating. I'm not sure why Postgres does not map the user on its own - this is probably a documentation issue rather than a bug.