Moving local settings for pg_hba.conf and postgresql.conf out of PGDATA

One of the configuration aspects of FreeBSD I have long liked is the concept of default values which are overridden by the user. For example, /etc/defaults/rc.conf (see The /etc directory). The default values in this file can be overridden by the user with their preferred values in /etc/rc.conf (or /etc/rc.conf.local, and other locations if you so choose (search for rc_conf_files)).

With that approach in mind, I wanted to do the same thing with my PostgreSQL installations.

I also wanted to configure pg_hba.conf and postgresql.conf via automated tools (e.g. Ansible). It is easier to drop one file with all your preferred values instead of parsing an existing file. You also don’t have to refresh your copy of the file each time a new release comes out with slight changes.

postgresql.conf

With that in might, I’ve added this entry to the end of postgresql.conf:

include_dir '/usr/local/etc/postgresql.conf.d'

In that directory, I have:

[12:26 r720-02-pg01 dan /var/db/postgres] % cat /usr/local/etc/postgresql.conf.d/postgresql.local.conf
cat: /usr/local/etc/postgresql.conf.d/postgresql.local.conf: Permission denied
[12:26 r720-02-pg01 dan /var/db/postgres] % sudo cat /usr/local/etc/postgresql.conf.d/postgresql.local.conf
listen_addresses = '127.163.54.32'

ssl = on

ssl_cert_file = '/usr/local/etc/ssl/r720-02-pg01.int.unixathome.org.cer'		# (change requires restart)
ssl_key_file  = '/usr/local/etc/ssl/r720-02-pg01.int.unixathome.org.key'		# (change requires restart)
ssl_ca_file   = '/usr/local/etc/ssl/ca.cer'			# (change requires restart)

work_mem = 1MB                         # min 64kB
maintenance_work_mem = 1GB

max_wal_size = 1GB
min_wal_size = 80MB

max_wal_size = 1536

checkpoint_completion_target = 0.7

client_min_messages = notice
log_min_messages = notice
log_min_error_statement = notice

log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on

log_lock_waits = on
log_statement = 'all'

log_timezone = 'UTC'
#datestyle = 'iso, mdy'

timezone = 0

pg_hba.conf

Similarly, at the end of pg_hba.conf, I have:

include_dir "/usr/local/etc/postgresql.pg_hba.d"

NOTE: In this case, I’m not overriding anything. pg_hba is first-match-wins. If I want to override anything in this file, I need to take a different approach. Perhaps an include at the top of the file instead.

As it stands now, all files in this directory will be pulled in. For more information, please refer to Managing Configuration File Contents.

I that directory, we find:

[12:27 r720-02-pg01 dan /var/db/postgres] % ls -l /usr/local/etc/postgresql.pg_hba.d 
total 1
-rw-------  1 postgres postgres 1071 2024.02.18 11:35 pg_hba.local.conf
[12:28 r720-02-pg01 dan /var/db/postgres] % sudo cat /usr/local/etc/postgresql.pg_hba.d/pg_hba.local.conf
# from nginx01 jail
hostssl freshports.org  www            127.163.0.80/32            md5
hostssl freshports.org  listening      127.163.0.80/32            md5
hostssl freshports.org  reading        127.163.0.80/32            md5

# from ingress jail
hostssl freshports.org  commits        127.163.0.10/32            md5
hostssl freshports.org  reading        127.163.0.10/32            md5
hostssl freshports.org  packager       127.163.0.10/32            md5
hostssl freshports.org  nagios         127.163.0.10/32            md5
hostssl freshports.org  reporter       127.163.0.10/32            md5
hostssl freshports.org  abi_maintainer 127.163.0.10/32            md5
hostssl template1       nagios         127.163.0.10/32            md5

# from pg01
#host    all             postgres      127.163.54.32/32            trust

# rsyncer
hostssl  all             rsyncer       127.163.0.32/32            md5

# for snmpd
local    postgres        snmpd                                   md5

# for dan
#local    all             dan                                     md5

Hope this is helpful for you.