PostgreSQL “pg_hba.conf” Explained: part2

Photo by Alphacolor on Unsplash

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:

Translation: All clients (users) connecting from 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:

As 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:

After modifying the pg_hba.conf, you follow the book to reload the configuration. Looking good…right?

A couple weeks later, you want to restart the server in your maintenance windows and see this.

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.

Once ipa-client-install is done, we can add some users in ipa-server simulating your enterprise environment.

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.

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.

Let’s go back to pg-client vm and try to connect to db using pguser1.

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.

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