PostgreSQL “pg_hba.conf” Explained: part3

Photo by Daniel Cheung on Unsplash
[root@master1 pam.d]# cat postgresql
#%PAM-1.0
auth include password-auth
account include password-auth
host all  all  0.0.0.0/0  pam pamservice=postgresql
[ysung@client ~]$ psql -h master1 -U pguser1 -d postgres
Password for user pguser1:
psql: error: FATAL: PAM authentication failed for user "pguser1"
[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"
[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.
[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.
[ysung@client ~]$ psql -h master1 -U pguser1 -d postgres
Password for user pguser1: <=new password
psql (13.1)
Type "help" for help.
postgres=>
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=pguser1

Kerberos

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.LAB
Valid starting Expires Service principal
12/23/2020 01:45:30 12/24/2020 01:45:27 krbtgt/YSUNG.VMWARE.LAB@YSUNG.VMWARE.LAB
[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.21
[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.lab
[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.keytab
[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.LAB
[root@master1 data]# grep krb postgresql.conf
krb_server_keyfile = 'server.keytab'
#krb_caseins_users = off
host all  all  0.0.0.0/0  gss include_realm=0 krb_realm=YSUNG.VMWARE.LAB
[ysung@client ~]$ su - pguser1
Password:
[pguser1@client ysung]$ klist
Ticket cache: KCM:333800001:7653
Default principal: pguser1@YSUNG.VMWARE.LAB
Valid 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=>

A data nerd started from data center field engineer to cloud database reliability engineer.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store