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 (commit de16ab72).

Note that pg_hba_file_rules provides an interpretation of the current contents of pg_hba.conf, not the applied settings (which as of PostgreSQL 12 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 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

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"

Categories

Authentication, Configuration, User management

See also

pg_hba.conf, pg_file_settings