5

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.

9

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.

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.
  • 1
    A heads up that pg_conf_load_time() will return an updated timestamp even when the reloading of a config file fails. My pg_hba.conf had an hostssl entry when SSL was not configured so while @dezso's SELECT ... statement was showing that postgres had re-read the config file none of my changes in pg_hba.conf were being picked up until I removed the hostssl entries. – Scott Woodall Jun 5 '18 at 17:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.