PostgreSQL with peer authentication

sysadmin PostgreSQL postgresql auth

PostgreSQL has always been my favorite SQL database, especially compared to MySQL which I’ve always found a pain to configure for my uses. Peer authentication allows to map OS user names to database user names for local connections. It can be used for password-less database access in a multi-user environment.

This guide shows how to install PostgreSQL and configure peer authentication on Fedora.


# Install and run PostgreSQL.
yum install postgresql postgresql-devel \
            postgresql-server postgresql-contrib

# Initialize the database cluster.
mkdir -p /var/lib/pgsql/data
chown -R postgres:postgres /var/lib/pgsql/data
su postgres
  initdb -D /var/lib/pgsql/data -E UTF8 --locale=en_US.UTF8

If you install at a location other than /var/lib/pgsql/data, create this file to tell the PostgreSQL service where to look:

# File: /etc/systemd/system/postgresql.service
.include /lib/systemd/system/postgresql.service

The package provides a systemd service which you can enable on boot and start like this:

systemctl enable postgresql.service
systemctl start postgresql.service

Peer Authentication

By default, peer authentication allows each system user to authenticate as the database user with the same name, but that’s usually not sufficient for my purposes. For example, I also want my root system user to be able to authenticate as postgres (the godlike database user).

To do this, you must edit pg_ident.conf in the PostgreSQL data directory. Here you can create user name maps like this one:

  adminmap   postgres         postgres
  adminmap   root             postgres

SYSTEM-USERNAME is the user name detected by the operating system. PG-USERNAME is the database user name that this user should have. You must group user name pairs under a MAPNAME which you will user later in the authentication configuration. The map above defines that both the postgres and root system users can connect as the postgres database user.

Once you have created your user name maps, you can use them in pg_hba.conf, the authentication configuration file. Here is an example:

  local  all       postgres           peer    map=adminmap

This defines that the postgres database user can access all databases. Notice that we selected the peer authentication method and that we reference adminmap, the user name map we created earlier. Therefore, with this configuration both the postgres and root system users can connect to all databases as postgres.

Now go forth and multiply the user name maps.