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.


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 = ''

ssl = on

ssl_cert_file = '/usr/local/etc/ssl/'		# (change requires restart)
ssl_key_file  = '/usr/local/etc/ssl/'		# (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


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  www              md5
hostssl  listening            md5
hostssl  reading            md5

# from ingress jail
hostssl  commits            md5
hostssl  reading            md5
hostssl  packager            md5
hostssl  nagios            md5
hostssl  reporter            md5
hostssl  abi_maintainer            md5
hostssl template1       nagios            md5

# from pg01
#host    all             postgres            trust

# rsyncer
hostssl  all             rsyncer            md5

# for snmpd
local    postgres        snmpd                                   md5

# for dan
#local    all             dan                                     md5

Hope this is helpful for you.

Website Pin Facebook Twitter Myspace Friendfeed Technorati Digg Google StumbleUpon Premium Responsive

2 thoughts on “Moving local settings for pg_hba.conf and postgresql.conf out of PGDATA”

Leave a Comment

Scroll to Top