Reload "pg_hba.conf" in self-managed PostgreSQL

 

Modify the $PGDATA/pg_hba.conf, and query to verify the content

Alternatively, use SELECT pg_read_file('pg_hba.conf') to query the contents.

postgres=# select * from pg_hba_file_rules;
-[ RECORD 1 ]----------------------------------------
line_number | 85
type        | local
database    | {all}
user_name   | {all}
address     |
netmask     |
auth_method | peer
options     |
error       |
-[ RECORD 2 ]----------------------------------------
line_number | 87
type        | host
database    | {all}
user_name   | {all}
address     | 127.0.0.1
netmask     | 255.255.255.255
auth_method | scram-sha-256
options     |
error       |
-[ RECORD 3 ]----------------------------------------
line_number | 88
type        | host
database    | {all}
user_name   | {all}
address     | 0.0.0.0
netmask     | 0.0.0.0
auth_method | scram-sha-256
options     |
error       |
-[ RECORD 4 ]----------------------------------------
line_number | 90
type        | host
database    | {all}
user_name   | {all}
address     | ::1
netmask     | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff
auth_method | scram-sha-256
options     |
error       |
-[ RECORD 5 ]----------------------------------------
line_number | 93
type        | local
database    | {replication}
user_name   | {all}
address     |
netmask     |
auth_method | peer
options     |
error       |
-[ RECORD 6 ]----------------------------------------
line_number | 94
type        | host
database    | {replication}
user_name   | {all}
address     | 127.0.0.1
netmask     | 255.255.255.255
auth_method | scram-sha-256
options     |
error       |
-[ RECORD 7 ]----------------------------------------
line_number | 95
type        | host
database    | {replication}
user_name   | {all}
address     | ::1
netmask     | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff
auth_method | scram-sha-256
options     |
error       |

Check the time pg_bha.conf modification time vs load time.

postgres=# SELECT pg_conf_load_time(), modification,current_timestamp FROM pg_stat_file('pg_hba.conf');
-[ RECORD 1 ]-----+------------------------------
pg_conf_load_time | 2022-08-28 11:19:29.742823+00
modification      | 2022-08-28 11:40:19+00
current_timestamp | 2022-08-28 11:52:26.818239+00

Reload the configuration

postgres=# SELECT pg_reload_conf();
-[ RECORD 1 ]--+--
pg_reload_conf | t

Check the time pg_bha.conf modification time vs load time.

postgres=# SELECT pg_conf_load_time(), modification,current_timestamp FROM pg_stat_file('pg_hba.conf');
-[ RECORD 1 ]-----+------------------------------
pg_conf_load_time | 2022-08-28 11:54:03.659547+00
modification      | 2022-08-28 11:40:19+00
current_timestamp | 2022-08-28 11:54:03.659549+00