- Here, I'm be using Postgresql-9.5; on any other version, only the string
9.5needs 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
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.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
postgres in Postgres system, and naming this mapping as
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:
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=#
Hope that helps!