Postgresql: Running `psql` as user `postgres` from different system username on localhost

Postgresql's postmaster (Postgresql cluster [not cluster in the usual notion] manager), listens on TCP socket and also on UNIX domain socket (for localhost use only). Using UNIX doamin sockets to connect to Postgresql is very useful as it is bound to filesystem access only, no network socket involved. As a result, we get less overhead and less resource usage.

Let's discuss about connecting to some Postgres database using UNIX socket, on localhost. Just to note, postmaster usually listens on /var/run/postgresql/.s.PGSQL.<TCP_port> UNIX socket(s).

Here, I'll be using Postgresql-9.5; on any other version, only the string 9.5 needs to be replaced with the version being used, in the configuration files mentioned below.

On Ubuntu (and possibly in all other major distros as well), postgres user can login and do all database activities because in the /etc/postgresql/9.5/main/pg_hba.conf (host based authentication configuration) file we have:

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

Here, peer method in the last column means that, on localhost and using UNIX doamin socket (local), when someone runs psql, the postmaster will get the invoking username from the system and use the same username for database login. As a result, we often tend to use sudo (or su) to run psql and brothers as username postgres like:

sudo -u postgres psql

But this is not quite scriptable as we need to provide sudo password or allow the invoking user to run psql (and other alike/relevant commands) as user postgres without asking for any password (NOPASSWD in sudo terms) -- which might lead to security issues; maintainability is also another consideration to look into.

So, to make any invoking user run psql we need to make couple of modifications to Postgresql configurations:

At first, edit the /etc/postgresql/9.5/main/pg_ident.conf file to append:

FOOBAR        spamegg        postgres

First column contains a map name, which could be any unique string (to be used in pg_hba.conf, see later), then we have the system username (spamegg), then postgres username to map to (postgres).

So, by this configuration, we are mapping the system username spamegg to postgres in Postgres system, and naming this mapping as FOOBAR.

Then, we need to edit the /etc/postgresql/9.5/main/pg_hba.conf, to modify the line mentioned at start to make it like:

# Database administrative login by Unix domain socket
local        all        postgres        peer      map=FOOBAR

i.e. we've just appended map=FOOBAR (mapping name used in pg_ident.conf above) option to the authentication rule.

Now, let's check this:

Before:

spamegg@host:~$ psql --username=postgres
psql: FATAL:  Peer authentication failed for user "postgres"

After:

spamegg@host:~$ psql --username=postgres
psql (9.5.12)
Type "help" for help.

postgres=#

Working as expected. Hope it helps!