PostgreSQL “pg_hba.conf” Explained: part3
In this part, I explain the pam authentication in pg_hba.conf. PAM stands for “pluggable authentication modules.” PAM supports four types of services, auth, account, password, and session, but Postgresql pam only supports two services; auth and account. In the last part, we installed ipa-client on pg-master. ipa-client should setup sssd/krb/ldap/pki on pg-master already. After installing PostgreSQL, you should have a default pam configuration in /etc/pam.d/postgresql.
[root@master1 pam.d]# cat postgresql
#%PAM-1.0
auth include password-auth
account include password-authUsing pam in PostgreSQL is as easy as making pg_hba.conf like the following and reloading the configuration.
host all all 0.0.0.0/0 pam pamservice=postgresqlGo back to pg-client and try the same connection as pguser1.
[ysung@client ~]$ psql -h master1 -U pguser1 -d postgres
Password for user pguser1:
psql: error: FATAL: PAM authentication failed for user "pguser1"Hmm it Looks like the pam line in pg_hba is taking effect but failed? Check the log and see if there is any clue there.
[root@master1 data]# tail log/postgresql-Wed.log
2020-12-23 00:53:08.249 UTC [50541] LOG: pam_authenticate failed: Authentication failure
2020-12-23 00:53:08.249 UTC [50541] FATAL: PAM authentication failed for user "pguser1"
2020-12-23 00:53:08.249 UTC [50541] DETAIL: Connection matched pg_hba.conf line 96: "host all all 0.0.0.0/0 pam pamservice=postgresql"
2020-12-23 00:53:23.002 UTC [50546] LOG: pam_acct_mgmt failed: Authentication token is no longer valid; new one required
2020-12-23 00:53:23.002 UTC [50546] FATAL: PAM authentication failed for user "pguser1"
2020-12-23 00:53:23.002 UTC [50546] DETAIL: Connection matched pg_hba.conf line 96: "host all all 0.0.0.0/0 pam pamservice=postgresql"Weird. pam error log should be in /var/log/secure. Switch to root or sudo to tail the secure log for the clue.
[root@master1 log]# tail secure
6 euid=26 tty= ruser= rhost=192.168.20.21 user=pguser1
Dec 23 00:53:22 master1 journal[50546]: postgres 192.168.20.21(50044) authentication: pam_sss(postgresql:auth): received for user pguser1: 12 (Authentication token is no longer valid; new one required)
Dec 23 00:53:23 master1 journal[50546]: postgres 192.168.20.21(50044) authentication: pam_sss(postgresql:account): User info message: Password expired. Change your password now.Here it is. When created the ipa user, the user expects to change the init password. Login as pguser1 and you will see the prompt like this. Make the change and try again.
[ysung@master1 ~]$ su - pguser1
Password:
Password expired. Change your password now.
Current Password:
New password:
Retype new password:
Last failed login: Wed Dec 23 01:10:10 UTC 2020 on pts/0
There was 1 failed login attempt since the last successful login.Now it should work. Try again?
[ysung@client ~]$ psql -h master1 -U pguser1 -d postgres
Password for user pguser1: <=new password
psql (13.1)
Type "help" for help.postgres=>
You can double-check the /var/log/secure and found a similar log like the following.
Dec 23 01:11:49 master1 journal[50618]: postgres 192.168.20.21(50080) authentication: pam_sss(postgresql:auth): authentication success; logname= uid=26 euid=26 tty= ruser= rhost=192.168.20.21 user=pguser1Kerberos
I want to explore Kerberos in Postgresql. Kerberos is a strong authentication service. Postgresql supports Kerberos authentication in pg_hba as “gss” (MIT KRB) or “ssapi” (Active Directory). FreeIPA supports MIT Kerberos and I will use gss as an example. Again, ipa-client-install already set up the Kerberos for us. We need to request a service principal for “postgres,” aka postmaster, and retrieve the keytab. Note the following steps requires “kinit as ipa-server administrator. “
[root@master1 log]# kinit admin
Password for admin@YSUNG.VMWARE.LAB:
[root@master1 log]# klist
Ticket cache: KCM:0
Default principal: admin@YSUNG.VMWARE.LABValid starting Expires Service principal
12/23/2020 01:45:30 12/24/2020 01:45:27 krbtgt/YSUNG.VMWARE.LAB@YSUNG.VMWARE.LAB
GCP has its own default dns (169.254.169.254) to resolve all vm fqdn to “c.project-name.internal”. We need to make our own dns right in ipa-server. I put pg-master and pg-client fqdn to ipa-server dns. Again, DNS is very important for Kerberos.
[root@master1 log]# ip a show eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1460 qdisc mq state UP group default qlen 1000
link/ether 42:01:c0:a8:14:0a brd ff:ff:ff:ff:ff:ff
inet 192.168.20.10/32 scope global dynamic noprefixroute eth0
valid_lft 48450sec preferred_lft 48450sec
inet6 fe80::afde:d40c:694e:6ebc/64 scope link noprefixroute
valid_lft forever preferred_lft forever
[root@master1 log]# ipa dnsrecord-add ysung.vmware.lab master1 --a-rec 192.168.20.10
Record name: master1
A record: 192.168.20.10
[root@master1 log]# ipa dnsrecord-add ysung.vmware.lab client --a-rec 192.168.20.21
Record name: client
A record: 192.168.20.21Now we can add postgres service account to ipa-server.
[root@master1 log]# ipa service-add postgres/master1.ysung.vmware.lab
------------------------------------------------------------------
Added service "postgres/master1.ysung.vmware.lab@YSUNG.VMWARE.LAB"
------------------------------------------------------------------
Principal name: postgres/master1.ysung.vmware.lab@YSUNG.VMWARE.LAB
Principal alias: postgres/master1.ysung.vmware.lab@YSUNG.VMWARE.LAB
Managed by: master1.ysung.vmware.labService account is created. Note that this service account will only work in master1.ysung.vmware.lab! Now let’s download the keytab from ipa-server. As I understand, ipa-server certmonger will monitor and maintain those keytabs and certificates. You don’t need to worry the keytab/cert expired.
[root@master1 log]# ipa-getkeytab -s myipa.ysung.vmware.lab -p postgres/master1.ysung.vmware.lab -k /var/lib/pgsql/13/data/server.keytab
Keytab successfully retrieved and stored in: /var/lib/pgsql/13/data/server.keytabOne more basic step for keytab, the file owner and access mode must be changed.
[root@master1 log]# cd ../lib/pgsql/13/data
[root@master1 data]# chown postgres. server.keytab
[root@master1 data]# klist -kt server.keytab
Keytab name: FILE:server.keytab
KVNO Timestamp Principal
---- ------------------- ------------------------------------------------------
1 12/23/2020 01:49:40 postgres/master1.ysung.vmware.lab@YSUNG.VMWARE.LAB
1 12/23/2020 01:49:40 postgres/master1.ysung.vmware.lab@YSUNG.VMWARE.LABWe confirmed that the keytab for the postgres service is in place. We uncomment/modify the line krb_server_keyfile to the service.keytab in postgresql.conf. Note that the dns must resolve the hostname correctly from pg-master and pg-client.
[root@master1 data]# grep krb postgresql.conf
krb_server_keyfile = 'server.keytab'
#krb_caseins_users = off
In the next step, we change the pg_hba.conf to use gss like the following, then restart the server:
host all all 0.0.0.0/0 gss include_realm=0 krb_realm=YSUNG.VMWARE.LABNow jump to pg-client and login as pguser1. After login, you already authenticated against Kerberos.
[ysung@client ~]$ su - pguser1
Password:
[pguser1@client ysung]$ klist
Ticket cache: KCM:333800001:7653
Default principal: pguser1@YSUNG.VMWARE.LABValid starting Expires Service principal
12/23/2020 02:10:12 12/24/2020 02:10:12 krbtgt/YSUNG.VMWARE.LAB@YSUNG.VMWARE.LAB
[pguser1@client ysung]$ psql -h master1.ysung.vmware.lab -d postgres
psql (13.1)
GSSAPI-encrypted connection
Type "help" for help.postgres=>
In the next part, we will explore cert authentication and TLS for encryption/verify-ca/verify-full. Stay tuned.