---

• Here, I'm be using Postgresql-9.5; on any other version, only the string 9.5 needs to be replaced with the version being used.

---

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 all bound to filesystem access, no socket involved. As a result, less overhead and resources being used.

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.<random_ints> UNIX socket(s).

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 file we have:

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

which means that, on localhost and using UNIX doamin socket, when someone runs psql, the postmaster will get the invoking username from the system and use the same username for database login (this is called peer authentication mechanism). 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.cong, see later), then we have the system username (spamegg), then postgres username to map topostgres`.

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 (host based authentication configuration file), to modify the line mentioned at start into:

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

i.e. we've just appended map=FOOBAR 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=#

---

Hope that helps!