How do I query the running pg_hba configuration?

Question

I want to test if a replication connection is authorized by pg_hba.conf on the provider before issuing the replication-starting command, and don't know how. (I have access to both unix and postgresql shells on both nodes)

For the non-replication connection, I would connect psql using a connstring like 'host=$MASTER_IP port=5432 dbname=$DATABASE user=$DBUSER password=$DBPASSWORD'

Context: I am writing a script to automate the setup of replication between servers, and configuration of the servers is managed through different systems/repositories (legacy reasons). Therefore, I want to test if settings are all right at each step.

Answer 1

Version 10 and above

There is a very convenient view for this called pg_hba_file_rules.

table pg_hba_file_rules ;

 line_number │   type    │   database    │  user_name  │  address  │                 netmask                 │ auth_method │ options │ error 
─────────────┼───────────┼───────────────┼─────────────┼───────────┼─────────────────────────────────────────┼─────────────┼─────────┼───────
           3 │ local     │ {all}         │ {all}       │           │                                         │ trust       │         │ 
           4 │ hostssl   │ {all}         │ {+users}    │ 127.0.0.1 │ 255.255.255.255                         │ pam         │         │ 
           5 │ host      │ {all}         │ {all}       │ 127.0.0.1 │ 255.255.255.255                         │ md5         │         │ 
           6 │ hostssl   │ {all}         │ {+users}    │ ::1       │ ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff │ pam         │         │ 
           7 │ host      │ {all}         │ {all}       │ ::1       │ ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff │ md5         │         │ 
           8 │ hostssl   │ {replication} │ {standby}   │ all       │                                         │ md5         │         │ 
           9 │ hostnossl │ {all}         │ {all}       │ all       │                                         │ reject      │         │ 

Up to version 9.6

For earlier versions, it is a bit cumbersome and error-prone (and works only for superusers, see the notes).

The only built-in way of reading pg_hba.conf I can currently imagine is using pg_read_file().

SELECT pg_read_file('pg_hba.conf');

will return you the whole file, in a single row, which then contains the newline characters (be them whichever flavour your OS likes:

[...]
 # TYPE  DATABASE        USER            ADDRESS                 METHOD↵
                                                                       ↵
 # "local" is for Unix domain socket connections only                  ↵
 local   all             all                                     md5   ↵
 # IPv4 local connections:                                             ↵
 host    all             all             127.0.0.1/32            md5   ↵
[...]

You can further process this to your liking.

Notes:

• The documentation tells us the following:

  The functions shown in Table 9-86 [pg_read_file() included - me] provide native access to files on the machine hosting the server. Only files within the database cluster directory and the log_directory can be accessed. Use a relative path for files in the cluster directory, and a path matching the log_directory configuration setting for log files. Use of these functions is restricted to superusers.

  The bit about superusers shouldn't be a problem for you, as you are setting up replication. If pg_hba.conf is not in the mentioned directories, you can add a symlink to it to trick the system:

postgres@Z22309: cd /var/lib/postgresql/9.5/main
postgres@Z22309:~/9.5/main$ ln -s /etc/postgresql/9.5/main/pg_hba.conf pg_hba.conf

• All this won't tell you if the file was changed but the config not reloaded. For this, you have to compare the results of pg_conf_load_time() and pg_stat_file(), as shown below. At the same time, you will still don't know exactly which changes were made since the last reload. Also, as mentioned in a comment, pg_conf_load_time() will advance even when loading the file was not successful, leading to confusion - at least I am not aware how one could tell the reloading was successful or not.

SELECT pg_conf_load_time() > modification FROM pg_stat_file('pg_hba.conf');

• In another answer, I show a way how to split the lines returned above.