How to check the syntax of pg_hba.conf and other postgresql conf files on ubuntu/debian/linux?

Question

Many times, the postgresql service can't restart after some configuration changes.

Is there any command line tool allowing to check the syntax of pg_hba.conf and other pg *.conf files before reloading/restarting the service, or even better, after any config change ?

Answer 1

You can run:

postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=# 

.. from within psql. or kill -HUP the postmaster process.

Any config validation errors will then get put into the Postgres log file, and it won't reload the config. Example log:

2016-10-05 10:31:57 BST LOG:  received SIGHUP, reloading configuration files
2016-10-05 10:31:57 BST LOG:  invalid connection type "awdawdawdawd"
2016-10-05 10:31:57 BST CONTEXT:  line 1 of configuration file "/etc/postgresql/9.3/main/pg_hba.conf"
2016-10-05 10:31:57 BST WARNING:  pg_hba.conf not reloaded

I'm not aware of a tool that just checks config for validity, but there was a thread on the pgsql-hackers mailing list here that discussed a proposal for a validator.

Answer 2

pg_reload_conf() (v14 documentation, also see documentation on pgPedia) tells you nothing about whether the configuration is valid. Rather, it just sends a SIGHUP to the Postgres process, causing it to reload the configuration. The return value of pg_reload_conf() merely tells you if this signalling attempt was successful, not whether the config is valid. If the configuration is bad, Postgres will still load it, but only the valid lines will take effect.

Given this, there is no reason to use pg_reload_conf() over the preferred method of reloading the service in Debian/Ubuntu, which is to do so via SystemD: systemctl reload postgresql. Both that and pg_reload_conf() perform the same task.

To check the validity of the configuration files, there are a few views:

• pg_file_settings
• pg_hba_file_rules
• pg_ident_file_mappings (introduced in PostgreSQL 15)

Select all records from one of these views to see the validity of each line in each config file that is parsed. Refer to the documentation linked above to see what info is provided by each view.

In particular:

• For pg_file_settings, there is a column applied which is true iff the config line is valid. Thus:

  • to see whether the entire config is valid: SELECT bool_and(applied) FROM pg_file_settings;
  • to see which lines are invalid and why: SELECT * FROM pg_file_settings WHERE NOT applied;

• For pg_hba_file_rules, there is a column error which is NULL iff the config line is valid. Thus:

  • to see whether all the HBA rules are valid: SELECT bool_and(error IS NULL) FROM pg_hba_file_rules;

  • to see which lines are invalid and why: SELECT * FROM pg_hba_file_rules WHERE error IS NOT NULL;