Infrastructure at your Service

Mouhamadou Diaw

PAM Authentication With PostgreSQL

PostgreSQl supports many authentication methods. The PAM authentication method operates similarly to password except that it uses PAM (Pluggable Authentication Modules) as the authentication mechanism. The user must exist in the database before PAM can be used for authentication.
In this blog I will configure PAM authentication for a PostgreSQL cluster 11 running on a centos 7.

postgres=# select version();
                                                 version

--------------------------------------------------------------------------------
-------------------------
 PostgreSQL 11.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (R
ed Hat 4.8.5-28), 64-bit
(1 row)

postgres=#

[root@dbi-pg-essentials ~]# cat /etc/centos-release
CentOS Linux release 7.3.1611 (Core)
[root@dbi-pg-essentials ~]# hostname
dbi-pg-essentials

We suppose that PostgreSQL is already installed with the PAM module. This should be the case if the installation was done with yum. If you decide to install using the sources, be sure to configure with the option –with-pam
With the installation this should exist a service named postgresql in the /etc/pam.d directory. If not you have to create a service for postgresql.

[postgres@dbi-pg-essentials pam.d]# pwd
/etc/pam.d
[root@dbi-pg-essentials pam.d]# ls -l postgresql
-rw-r--r--. 1 root root 71 Nov  7 12:37 postgresql
[root@dbi-pg-essentials pam.d]#

The first step is then to configure PostgreSQL to accept PAM authentication. Like other authentication methods, we have to add the corresponding entries in the pg_hba.conf file

[postgres@dbi-pg-essentials data]$ grep pamservice pg_hba.conf
host    all             all            192.168.22.0/24        pam pamservice=postgresql
[postgres@dbi-pg-essentials data]$

We can note the option pamservice=postgresql. Don’t forget to reload or to restart your cluster after modifying the pg_hba.conf file.
In my case I also had to change the permissions of /etc/shadow file to following

[postgres@dbi-pg-essentials data]$ ls -l /etc/shadow
-r--r--r-- 1 root root 988 Dec 21 11:20 /etc/shadow
[postgres@dbi-pg-essentials data]$

And the configuration is done. For the test let’s create a linux user named for example usrpam in the server

[root@dbi-pg-essentials ~]# useradd -m usrpam
[root@dbi-pg-essentials ~]# passwd usrpam
Changing password for user usrpam.
New password:
BAD PASSWORD: The password is shorter than 8 characters
Retype new password:
passwd: all authentication tokens updated successfully.
[root@dbi-pg-essentials ~]#

As specified earlier, the user should exist in the database before PAM authentication can be used. So let’s create the same user in PostgreSQL but without password

postgres=# create user usrpam with login;
CREATE ROLE
postgres=#

And now the usrpam should be able to connect from any client in the network 192.168.22.0.

[usr2@dbi-pg-essentials_2 ~]$  psql -h dbi-pg-essentials -U usrpam -d postgres  
Password for user usrpam:
psql (11.1)
Type "help" for help.

postgres=> select user;
  user
--------
 usrpam
(1 row)

postgres=>

Leave a Reply

Mouhamadou Diaw
Mouhamadou Diaw

Consultant