Information
A remote host login, via SSH, is arguably the most secure means of remotely accessing and administering the PostgreSQL server. Once connected to the PostgreSQL server, using the psql client, via UNIX DOMAIN SOCKETS, while using the peer authentication method is the most secure mechanism available for local database connections. Provided a database user account of the same name of the UNIX account has already been defined in the database, even ordinary user accounts can access the cluster in a similarly highly secure manner.
NOTE: Nessus has not performed this check. Please review the benchmark to ensure target compliance.
Solution
Creation of a database account that matches the local account allows PEER authentication:
# psql -c 'CREATE ROLE user1 WITH LOGIN;'
CREATE ROLE
Execute the following as the UNIX user account, the default authentication rules should now permit the login:
# su - user1
# whoami
user1
# psql -d postgres
postgres=>
As per the host-based authentication rules in $PGDATA/pg_hba.conf, all login attempts via UNIX DOMAIN SOCKETS are processed on the line beginning with local.
This is the minimal rule that must be in place allowing PEER connections:
# TYPE DATABASE USER ADDRESS METHOD
local all all peer
Once edited, the server process must reload the authentication file before it can take effect. Improperly configured rules cannot update i.e. the old rules remain in place. The PostgreSQL logs will report the outcome of the SIGHUP:
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
The following examples illustrate other possible configurations. The resultant 'rule' of success/failure depends upon the first matching line:
# allow only postgres user logins locally via UNIX socket
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer
# allow all local users via UNIX socket
# TYPE DATABASE USER ADDRESS METHOD
local all all peer
# allow all local users, via UNIX socket, only if they are connecting to a db named the same as their username
# e.g. if user 'bob' is connecting to a db named 'bob'
# TYPE DATABASE USER METHOD
local samerole all peer
# allow only local users, via UNIX socket, who are members of the 'rw' role in the db
# TYPE DATABASE USER ADDRESS METHOD
local all +rw peer