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
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
su) to run
psql and brothers as username
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)
postgres without asking for any password (
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 (
So, by this configuration, we are mapping the system username
postgres in Postgres system, and naming this mapping as
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:
spamegg@host:~$ psql --username=postgres psql: FATAL: Peer authentication failed for user "postgres"
spamegg@host:~$ psql --username=postgres psql (9.5.12) Type "help" for help. postgres=#
Working as expected. Hope it helps!