PostgreSQL “pg_hba.conf” Explained: part1

Photo by Daniel Cheung on Unsplash

This article attempts to demystify how to configure pg_hba.conf and integrate “enterprise systems” for different use cases.

The GCE env I demo contains three VMS, ipa-server, pg-master, and pg-client (you can git clone and deploy the same environment from my GitHub repo FreeIPA is like “MS Active Directory.” FreeIPA integrates a Directory Server( 389), MIT Kerberos, NTP, DNS, and DogTag (PKI).

In Postgresql, hba stands for “host-based authentication.” pg_hba.conf contains a set of rules. The first field is the connection type. In the beginning, you need to know two basic types, local and host. “local” means local domain socket and only local processes can access it. “host” means “TCP” connection. The second field is the “database” name that the client wants to access. The rest fields are user, source ip/address (for local access, there is no ip/address), authentication method, and authentication options. The following is an example record.

The user, robo, connects to the database dvdrental from subnet IP range should use scram-sha-256 password to authenticate him/her self.

According to the official Postgresql doc, there is no backoff or fall through in pg_hba.conf. The evaluation of pg_hba.conf is from left to right and top to down; if a record hit, PostgreSQL either grants the access or drops the client connection. PostgreSQL will not try the next pg_hba.conf record. So the order of pg_hba.conf MATTERS.
Let’s start with the default “pg_hba.conf” created by initdb. I use “ps” command to check the postmaster owner and the $PGDATA. You can find the “pg_hba.conf” in $PGDATA.

“id” shows the postmaster process owner, postgres, is a local account. You can use “sudo su — postgres” to switch the role to postgres.

The pg_hba.conf “record order” rule of thumb: narrow range connection with weak authentication first, then open the range of client connection with more robust authentication. As you can see in the first record in the example, all users who connect from the local socket to all databases should use peer authentication. If users are not connecting from the local socket, PostgreSQL tries to evaluate the next record (host all all scram-sha-256) till all are exhausted. In this case, 127.0.01/32 is the only IP that can connect to Postgresql server.

We can try connecting to pg-master from pg-client.

From the master log (/var/lib/pgsql/13/data/log/), you can see the error. “no pg_hba.conf entry” means postmaster has exhausted all entry and can’t find a rule for this connection.

Next step, let’s add the subnet to the pg_hba.conf. Don’t forget to “reload” the config (systemctl reload postgresql-13).

Now go back to the client vm and try again.

Note that scram-sha-256 and md5 are the database role password. Unfortunately, we don’t have postgres scram-sha-256 setup in the db. This connection failed.

Let’s add one more line under pg_hba.conf and reload the system.

Now go back to the client vm and try one more time.

Although we have “trust” after the line scram-sha-256 line, the client still authenticates through the last 2nd line. If it fails that line, the authentication will not go to the next line (trust).

Let’s create a role with a password inside the db, then try to connect the db from client vm. Note, we don’t want to give postgres (superuser of postgresql) a password.

Now we should be able to connect to db as remote_user1 from client.

What can this remote_user1 do in Postgres DB? That’s the authorization of Postgresql.
Next part, we will explore other authentications in pg_hba. Some authentication methods will need the ipa-server vm. Stay tuned.

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