Recently, I upgraded a server running Ubuntu 22.04 to 24.04, that also comes with PostgreSQL 16. For some odd reasons, the Postgres server was not managed by Ansible, and changes previously made to Postgres 15 was not recorded.
I noticed that the backup database (pg_dump) was not working properly, the backup database size was just a few kilobytes. Let’s investigate, shall we?
root@toto:/etc/cron.daily# ./postgresql-backup
pg_dump: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "postgres"
You might be wondering, what’s in postgresql-backup? Well, nothing fancy.
root@toto:/etc/cron.daily# head postgresql-backup
#!/bin/bash
# 2014-06-06
# NOT YET Ansible managed
MAILTO=root
BACKUP_DIR=/srv/backup/database/postgresql
/usr/bin/pg_dump -U postgres -Fc awesomedb | /bin/gzip > ${BACKUP_DIR}/awesomedb.pgdump_`date +'%Y-%m-%d'`.gz
So, the error message is similar to an issue with Postgres I had a long time ago.
This has something to do the authentication configured in the pg_hba.conf which use peer method. The peer authentication method works by obtaining the client’s operating system user name from the kernel and using it as the allowed database user name (with optional user name mapping) based on [1].
Let’s check what’s configured in pg_hba.conf file.
root@toto:/etc/cron.daily# cat /etc/postgresql/16/main/pg_hba.conf | grep peer
# "gss", "sspi", "ident", "peer", "pam", "ldap", "radius" or "cert".
local all postgres peer
local all all peer
local replication all peer
In my backup script, the pg_dump was run as a root user, and it would have worked if had it was run by the operating system postgres user (I did test that). There are a few solutions to this issue according to the internet, however, I’m going to make use the “user name maps” feature [2], to map the system root user to the postgres database user.
The user name maps are defined in the ident map file named pg_ident.conf, it has the following format:
map-name system-username database-username
Currently there is no maps defined in the pg_ident.conf. We can see this by checking the last 2 line in the file.
root@toto:/etc/systemd# tail -2 /etc/postgresql/16/main/pg_ident.conf
# MAPNAME SYSTEM-USERNAME PG-USERNAME
In my case, I want to append 2 lines to the end of the /etc/postgresql/16/main/pg_ident.conf to map root and postgres system users to the postgres database user and call it totomap.
root@toto:~# cat << EOF | tee -a /etc/postgresql/16/main/pg_ident.conf
> totomap postgres postgres
> totomap root postgres
> EOF
totomap postgres postgres
totomap root postgres
root@toto:~# tail -2 /etc/postgresql/16/main/pg_ident.conf
totomap postgres postgres
totomap root postgres
Next step is to add the map=totomap to the postgres user in pg_hba.conf. I’m going to use sed command do this, but if you follow along, you can edit the pg_hba.conf file manually with vim or nano.
root@toto:~# grep postgres /etc/postgresql/16/main/pg_hba.conf
local all postgres peer
root@toto:~# sed -i 's/\(^local.*all.*postgres.*peer$\)/\1 map=totomap/' /etc/postgresql/16/main/pg_hba.conf
root@toto:~# grep postgres /etc/postgresql/16/main/pg_hba.conf
local all postgres peer map=totomap
We’re almost done. Just need to restart the postgres service and try re-run the backup script (pg_dump).
root@toto:~# systemctl restart postgresql
root@toto:~# /etc/cron.daily/postgresql-backup
root@toto:~# echo $?
0
It’s working. Please note, I’m not a database admin, my interaction with PostsgreSQL is very limited. The method used in this post might not be a good practice, but works well enough for my use case.
BONUS:
Normally, when we want to connect to the Postgres server without authentication, we will switch to the postgres (system) user first (as it has a database match this name and allow access with peer authentication method.)
For example, this is what we can do:
root@toto:~# su - postgres
postgres@toto:~$ psql
psql (16.3 (Ubuntu 16.3-0ubuntu0.24.04.1))
Type "help" for help.
postgres=#
If we tried to run psql command from the root user, we would get an error:
root@toto:~# psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "root" does not exist
However, after adding a new user map (totomap in this example), we can connect to the Postgres server from root (system) user directly. Here’s how:
root@toto:~# psql -U postgres
psql (16.3 (Ubuntu 16.3-0ubuntu0.24.04.1))
Type "help" for help.
postgres=#
References: