Infrastructure at your Service

Joël Cattin

PostgreSQL 10 Beta 1 : pg_hba_file_rules view

This small blog to share a helpful stuff with the pg_hba.conf file :

The pg_hba.conf file is the central configuration file to control client authentication.
It is located in the database cluster’s data directory :
postgres@ppas01:/u02/pgdata/PG10BETA1/ [PG10BETA1] ll pg_hba.conf
lrwxrwxrwx. 1 postgres postgres 59 Jun 30 10:19 pg_hba.conf
postgres@ppas01:/u02/pgdata/PG10BETA1/ [PG10BETA1]

When you add or modify an entry in this file, you have to reload the cluster to take the changes in account :

postgres@ppas01:/u02/pgdata/PG10BETA1/ [PG10BETA1] echo "host all efm 192.168.22.38/35 md5" >> /u02/pgdata/PG10BETA1/pg_hba.conf
postgres@ppas01:/u02/pgdata/PG10BETA1/ [PG10BETA1] postgres@ppas01:/u02/pgdata/PG10BETA1/ [PG10BETA1] pg_ctl -D "/u02/pgdata/PG10BETA1/" reload
server signaled
postgres@ppas01:/u02/pgdata/PG10BETA1/ [PG10BETA1]

Oops… Have you seen my mistake ? Probably not. The system didn’t see it either.
That was in Postgres 9.6 and older versions.

In Postgres 10 there is now a new view called “pg_hba_file_rules” which will returns the current content of the pg_hba.conf file entries AND reports the errors :
postgres=# select version();
version
-------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10beta1 dbi services build on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
(1 row)

postgres=# select line_number, error, type, database, user_name, address, netmask from pg_hba_file_rules;
line_number | error | type | database | user_name | address | netmask
-------------+-------------------------------------------------+-------+---------------+-----------+-----------+-----------------------------------------
80 | | local | {all} | {all} | |
82 | | host | {all} | {all} | 127.0.0.1 | 255.255.255.255
84 | | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff
87 | | local | {replication} | {all} | |
88 | | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255
89 | | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff
90 | invalid CIDR mask in address "192.168.22.38/35" | | | | |
(7 rows)

This will allows you to get a quick look at what is wrong in pg_hba.conf and fixing it as necessary.

 

Leave a Reply


3 + three =

Joël Cattin
Joël Cattin

Consultant