PostgreSQL “pg_hba.conf” Explained: part2
In part1, we understand the basic rules of pg_hba.conf. Let’s review the entry I put in the pg_hba.conf in part1. It was:
host all all 192.168.20.0/24 scram-sha-256Translation: All clients (users) connecting from 192.168.20.0/24 subnet try to access ALL databases will use scram-sha-256 password. From a dba perspective, this entry is still too open. I would suggest narrow it down to something like the following:
host dvdrental remote_user1 192.168.20.21/32 scram-sha-256As you can see, the CIDR is narrow to one single database (dvdrental) from single IP. This is more rigid but well…you start to think: Oh dear, maintaning this pg_hba.conf will be fun. For example:
[postgres@master1 data]$ cat pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
# IPv6 local connections:
host all all ::1/128 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
host all all 192.168.20.0/24 scram-sha-256
host all all 192.168.20.0 scram-sha-256 #typo here
After modifying the pg_hba.conf, you follow the book to reload the configuration. Looking good…right?
[postgres@master1 data]$ /usr/pgsql-13/bin/pg_ctl reload -D /var/lib/pgsql/13//data
server signaled
[postgres@master1 data]$ ps -ef|grep postgres
postgres 15786 1 0 03:19 ? 00:00:00 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
postgres 15830 15786 0 03:19 ? 00:00:00 postgres: logger
postgres 15860 15786 0 03:19 ? 00:00:00 postgres: checkpointer
postgres 15862 15786 0 03:19 ? 00:00:00 postgres: background writer
postgres 15863 15786 0 03:19 ? 00:00:00 postgres: walwriter
postgres 15864 15786 0 03:19 ? 00:00:00 postgres: autovacuum launcher
postgres 15866 15786 0 03:19 ? 00:00:00 postgres: stats collector
postgres 15867 15786 0 03:19 ? 00:00:00 postgres: logical replication launcher
root 47706 47503 0 04:02 pts/0 00:00:00 sudo su - postgres
root 47708 47706 0 04:02 pts/0 00:00:00 su - postgres
postgres 47709 47708 0 04:02 pts/0 00:00:00 -bash
postgres 47751 47709 0 04:05 pts/0 00:00:00 psql
postgres 47752 15786 0 04:05 ? 00:00:00 postgres: postgres postgres [local] idle
root 49178 49148 0 16:58 pts/1 00:00:00 sudo su - postgres
root 49180 49178 0 16:58 pts/1 00:00:00 su - postgres
postgres 49181 49180 0 16:58 pts/1 00:00:00 -bash
postgres 49455 49181 0 18:55 pts/1 00:00:00 ps -ef
postgres 49456 49181 0 18:55 pts/1 00:00:00 grep --color=auto postgresA couple weeks later, you want to restart the server in your maintenance windows and see this.
[postgres@master1 data]$ /usr/pgsql-13/bin/pg_ctl restart -D /var/lib/pgsql/13//data
waiting for server to shut down.... done
server stopped
waiting for server to start....2020-12-22 18:58:15.873 UTC [49464] LOG: redirecting log output to logging collector process
2020-12-22 18:58:15.873 UTC [49464] HINT: Future log output will appear in directory "log".
stopped waiting
pg_ctl: could not start server
Examine the log output.The problem here is that “reloading” the pg_hba.conf will not show you there is a syntax error in your pg_hba.conf but “restart” will show you something is preventing the server from starting.
My own opinion about this pg_hba.conf is “make it short and don’t change it”.
But how? Delegating the authentication to “an” external source. Remember the rule of thumb? pg_hba does not fall through. There are many authentications, e.g., gss(gssapi), ssapi, ldap, radius, cert, pam, and bds. Let’s begin with LDAP. LDAP stands for lightweight directory access protocol, which is a standard protocol that many IdMs support. I use FreeIPA in this demo. In my github repo, the terraform and ansible script prepare the FreeIPA server.
We need to setup FreeIPA client on pg-master and pg-client VMs and you should have both hosts/vms managed (PKI, KRB, LDAP, and DNS) by FreeIPA. Take note on the following information. You will need it in pg_hba.conf auth_options.
[root@client ~]# ipa-client-install --domain=ysung.vmware.lab --server=myipa.ysung.vmware.lab --realm=YSUNG.VMWARE.LAB -p admin -w VMware1! --hostname=client.ysung.vmware.lab -U
This program will set up IPA client.
Version 4.8.7Client hostname: client.ysung.vmware.lab
Realm: YSUNG.VMWARE.LAB
DNS Domain: ysung.vmware.lab
IPA Server: myipa.ysung.vmware.lab
BaseDN: dc=ysung,dc=vmware,dc=labSynchronizing time
No SRV records of NTP servers found and no NTP server or pool address was provided.
Using default chrony configuration.
Attempting to sync time with chronyc.
Time synchronization was successful.
Successfully retrieved CA cert
Subject: CN=Certificate Authority,O=YSUNG.VMWARE.LAB
Issuer: CN=Certificate Authority,O=YSUNG.VMWARE.LAB
Valid From: 2020-12-22 03:23:31
Valid Until: 2040-12-22 03:23:31Enrolled in IPA realm YSUNG.VMWARE.LAB
Created /etc/ipa/default.conf
Configured sudoers in /etc/authselect/user-nsswitch.conf
Configured /etc/sssd/sssd.conf
Configured /etc/krb5.conf for IPA realm YSUNG.VMWARE.LAB
Systemwide CA database updated.
Hostname (client.ysung.vmware.lab) does not have A/AAAA record.
Failed to update DNS records.
Missing A/AAAA record(s) for host client.ysung.vmware.lab: 192.168.20.21.
Incorrect reverse record(s):
192.168.20.21 is pointing to pg-client.c.vmware-ysung.internal. instead of client.ysung.vmware.lab.
Adding SSH public key from /etc/ssh/ssh_host_ed25519_key.pub
Adding SSH public key from /etc/ssh/ssh_host_ecdsa_key.pub
Adding SSH public key from /etc/ssh/ssh_host_rsa_key.pub
Could not update DNS SSHFP records.
SSSD enabled
Configured /etc/openldap/ldap.conf
Configured /etc/ssh/ssh_config
Configured /etc/ssh/sshd_config
Configuring ysung.vmware.lab as NIS domain.
Client configuration complete.
The ipa-client-install command was successful
[root@client ~]#
Once ipa-client-install is done, we can add some users in ipa-server simulating your enterprise environment.
[ysung@myipa ~]$ kinit admin
Password for admin@YSUNG.VMWARE.LAB:
[ysung@myipa ~]$ klist
Ticket cache: KCM:1000
Default principal: admin@YSUNG.VMWARE.LABValid starting Expires Service principal
12/22/2020 19:44:38 12/23/2020 19:44:33 krbtgt/YSUNG.VMWARE.LAB@YSUNG.VMWARE.LAB
[ysung@myipa ~]$ ipa user-add pguser1
First name: Stan
Last name: Sung
--------------------
Added user "pguser1"
--------------------
User login: pguser1
First name: Stan
Last name: Sung
Full name: Stan Sung
Display name: Stan Sung
Initials: SS
Home directory: /home/pguser1
GECOS: Stan Sung
Login shell: /bin/sh
Principal name: pguser1@YSUNG.VMWARE.LAB
Principal alias: pguser1@YSUNG.VMWARE.LAB
Email address: pguser1@ysung.vmware.lab
UID: 333800001
GID: 333800001
Password: False
Member of groups: ipausers
Kerberos keys available: False
[ysung@myipa ~]$ ipa passwd pguser1
New Password: <test123>
Enter New Password again to verify: <test123>
-----------------------------------------------
Changed password for "pguser1@YSUNG.VMWARE.LAB"
-----------------------------------------------
Now we have our first user in ipa-server. If everything is good, you should see the following in pg-master: pguser1 is not a local user of pg-master.
[ysung@master1 ~]$ cat /etc/passwd | grep pguser1
[ysung@master1 ~]$ id pguser1
uid=333800001(pguser1) gid=333800001(pguser1) groups=333800001(pguser1)Now we can go back to pg_hba.conf to set up ldap authentication. For testing purposes, I prefer restarting the db instead of reloading the configuration. Note that you will need to create roles/users inside Postgresql for external authentication.
[postgres@master1 data]$ cat pg_hba.conf
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
host all remote_user1 192.168.20.21/32 scram-sha-256
host all all 0.0.0.0/0 ldap ldapserver=myipa.ysung.vmware.lab ldaptls=1 ldapbasedn="cn=compat,dc=ysung,dc=vmware,dc=lab" ldapsearchattribute=uid
[postgres@master1 ~]$ psql
psql (13.1)
Type "help" for help.postgres=# create user pguser1;
CREATE ROLE
postgres=# \q
Let’s go back to pg-client vm and try to connect to db using pguser1.
[ysung@client ~]$ psql -h master1 -U pguser1 -d postgres
Password for user pguser1:
psql (13.1)
Type "help" for help.postgres=>
In the following parts, we will explore pam authentication. Then we jump into TLS1.2 and KRB to secure the postgres process. Once the db is secured, I will show cert authentication. Cert authentication is a good fit for postgresql on K8S. Stay tuned.