PostgreSQL “pg_hba.conf” Explained: part1
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 https://github.com/vmware-ysung/pg_hba_explained). 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.
host dvdrental robo 172.20.10.0/24 scram-sha-256The user, robo, connects to the database dvdrental from subnet 172.20.10.0/24 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.
ysung@master1 ~]$ ps -ef|grep postgres
postgres 15564 1 0 01:01 ? 00:00:00 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
postgres 15608 15564 0 01:01 ? 00:00:00 postgres: logger
postgres 15637 15564 0 01:01 ? 00:00:00 postgres: checkpointer
postgres 15639 15564 0 01:01 ? 00:00:00 postgres: background writer
postgres 15640 15564 0 01:01 ? 00:00:00 postgres: walwriter
postgres 15641 15564 0 01:01 ? 00:00:00 postgres: autovacuum launcher
postgres 15642 15564 0 01:01 ? 00:00:00 postgres: stats collector
postgres 15644 15564 0 01:01 ? 00:00:00 postgres: logical replication launcher
ysung 47317 46959 0 02:19 pts/0 00:00:00 grep --color=auto postgres“id” shows the postmaster process owner, postgres, is a local account. You can use “sudo su — postgres” to switch the role to postgres.
[ysung@master1 ~]$ id postgres
uid=26(postgres) gid=26(postgres) groups=26(postgres)
[ysung@master1 ~]$ sudo su - postgres
[postgres@master1 ~]$ cat /var/lib/pgsql/13/data/pg_hba.conf
# "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-256The 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 127.0.0.1/32 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.
[ysung@client ~]$ 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:15 brd ff:ff:ff:ff:ff:ff
inet 192.168.20.21/32 scope global dynamic noprefixroute eth0
valid_lft 85956sec preferred_lft 85956sec
inet6 fe80::6eb1:432e:607f:d82a/64 scope link noprefixroute
valid_lft forever preferred_lft forever
[ysung@client ~]$ psql -h 192.168.20.10 -U postgres
psql: error: FATAL: no pg_hba.conf entry for host "192.168.20.21", user "postgres", database "postgres", SSL offFrom 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.
2020-12-22 03:42:16.988 UTC [47482] FATAL: no pg_hba.conf entry for host "192.168.20.21", user "postgres", database "postgres", SSL offNext step, let’s add the 192.168.20.0/24 subnet to the pg_hba.conf. Don’t forget to “reload” the config (systemctl reload postgresql-13).
[postgres@master1 data]$ sed -i -e '$ahost\tall\t\tall\t\t192.168.20.0/24\t\tscram-sha-256' pg_hba.conf
[postgres@master1 data]$ tail pg_hba.conf
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-256Now go back to the client vm and try again.
[ysung@client ~]$ psql -h master1 -U postgres
Password for user postgres:
psql: error: FATAL: password authentication failed for user "postgres"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.
[postgres@master1 data]$ sed -i -e '$ahost\tall\t\tall\t\t192.168.20.21/32\t\ttrust' pg_hba.conf
[postgres@master1 data]$ tail pg_hba.conf
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.21/32 trustNow go back to the client vm and try one more time.
[ysung@client ~]$ psql -h master1 -U postgres
Password for user postgres:
psql: error: FATAL: password authentication failed for user "postgres"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.
postgres=# \h create user
Command: CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]where option can be:SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uidURL: https://www.postgresql.org/docs/13/sql-createuser.htmlpostgres=# create user remote_user1 encrypted password 'test123';
CREATE ROLE
Now we should be able to connect to db as remote_user1 from client.
[ysung@client ~]$ psql -h master1 -U remote_user1 -d postgres
Password for user remote_user1:
psql (13.1)
Type "help" for help.postgres=>
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.