pg_hba_file_rules
A system catalogue view providing a summary of the contents of pg_hba.conf
pg_hba_file_rules is a system catalogue view providing a summary of the contents of pg_hba.conf.
pg_hba_file_rules was added in PostgreSQL 10.
Usage
pg_hba_file_rules provides an interpretation of the current contents of pg_hba.conf, not the applied settings (which as of PostgreSQL 14 are not available). It also does not give any indication as to how rules would be applied, merely whether they can be parsed.
Definition by PostgreSQL version
pg_hba_file_rules (PostgreSQL 15)
View "pg_catalog.pg_hba_file_rules"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
line_number | integer | | |
type | text | | |
database | text[] | | |
user_name | text[] | | |
address | text | | |
netmask | text | | |
auth_method | text | | |
options | text[] | | |
error | text | | |
Documentation: pg_hba_file_rules
pg_hba_file_rules (PostgreSQL 14)
View "pg_catalog.pg_hba_file_rules"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
line_number | integer | | |
type | text | | |
database | text[] | | |
user_name | text[] | | |
address | text | | |
netmask | text | | |
auth_method | text | | |
options | text[] | | |
error | text | | |
Documentation: pg_hba_file_rules
pg_hba_file_rules (PostgreSQL 13)
View "pg_catalog.pg_hba_file_rules"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
line_number | integer | | |
type | text | | |
database | text[] | | |
user_name | text[] | | |
address | text | | |
netmask | text | | |
auth_method | text | | |
options | text[] | | |
error | text | | |
Documentation: pg_hba_file_rules
pg_hba_file_rules (PostgreSQL 12)
View "pg_catalog.pg_hba_file_rules"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
line_number | integer | | |
type | text | | |
database | text[] | | |
user_name | text[] | | |
address | text | | |
netmask | text | | |
auth_method | text | | |
options | text[] | | |
error | text | | |
Documentation: pg_hba_file_rules
pg_hba_file_rules (PostgreSQL 11)
View "pg_catalog.pg_hba_file_rules"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
line_number | integer | | |
type | text | | |
database | text[] | | |
user_name | text[] | | |
address | text | | |
netmask | text | | |
auth_method | text | | |
options | text[] | | |
error | text | | |
Documentation: pg_hba_file_rules
pg_hba_file_rules (PostgreSQL 10)
View "pg_catalog.pg_hba_file_rules"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
line_number | integer | | |
type | text | | |
database | text[] | | |
user_name | text[] | | |
address | text | | |
netmask | text | | |
auth_method | text | | |
options | text[] | | |
error | text | | |
Documentation: pg_hba_file_rules
Change history
- PostgreSQL 10
- added (commit de16ab72)
Examples
Given a simple pg_hba.conf file like this (IPv6 lines omitted for brevity):
# TYPE DATABASE USER ADDRESS METHOD local all postgres trust host all postgres 127.0.0.1/32 trust host all postgres 192.168.0.0/16 trust # TYPE DATABASE USER ADDRESS METHOD local replication repuser trust host replication repuser 127.0.0.1/32 trust host replication repuser 192.168.0.0/16 trust
pg_hba_file_rules renders the contents like this:
postgres=# SELECT * FROM pg_hba_file_rules ;
line_number | type | database | user_name | address | netmask | auth_method | options | error
-------------+-------+---------------+------------+-------------+-----------------+-------------+---------+-------
2 | local | {all} | {postgres} | | | trust | |
3 | host | {all} | {postgres} | 127.0.0.1 | 255.255.255.255 | trust | |
4 | host | {all} | {postgres} | 192.168.0.0 | 255.255.0.0 | trust | |
7 | local | {replication} | {repuser} | | | trust | |
8 | host | {replication} | {repuser} | 127.0.0.1 | 255.255.255.255 | trust | |
9 | host | {replication} | {repuser} | 192.168.0.0 | 255.255.0.0 | trust | |
(6 rows)
Adding a nonsensical line such as:
# TYPE DATABASE USER ADDRESS METHOD foo !!! --- ()()()
will result in the line being reported as containing an error:
postgres=# SELECT * FROM pg_hba_file_rules WHERE error IS NOT NULL;
line_number | type | database | user_name | address | netmask | auth_method | options | error
-------------+------+----------+-----------+---------+---------+-------------+---------+-------------------------------
2 | | | | | | | | invalid connection type "foo"
References
- PostgreSQL documentation: pg_hba_file_rules