Correct way to edit pg_hba.conf

Question

So, I am attempting to create an install script for my application (targeting Ubuntu 16). It has to create a postgresql user, grant permission to that user to authenticate via password, and grant permission to that user to authenticate locally. I only want to grant permission to do that on one database, the application database. So I need to insert the line local databasename username md5 above the lines that reject unknown connections, e.g., in the "Put your actual configuration here" section of pg_hba.conf. (pg_hba.conf uses position in the file to determine priority: first rule encountered that matches the connection gives the final result.)

To add this line, my script runs:

sudo awk '
/# Put your actual configuration here/ {
    print "local databasename username md5"
}
{ print }
' /etc/postgresql/9.5/main/pg_hba.conf

# other setup

service postgresql restart

But that's less than optimal. First, the version number will change in the future, so hardcoding the directory is poor. Second, that's making a comment in someone else's project an actual structural part of the config file, which is a horrible idea from all possible points of view in all possible universes.

So my question is twopart. First, is there a good, correct, and accepted method to edit pg_hba.conf that I can use in an installation script instead of kitbashing about with text editors?

Second, if there is no good answer to the first part: is there a programmatic way to ask postgresql where it's pulling pg_hba from?

Answer 1

Debian tool chain

So my question is twopart. First, is there a good, correct, and accepted method to edit pg_hba.conf that I can use in an installation script instead of kitbashing about with text editors?

Yes, however, you'll probably find it unsatisfactory.

Upstream, PostgreSQL doesn't support multiple versions and installs with their build tools. Debian does. So Debian has invented a concept of a cluster which is essentially a name and a version number.

Building a tool on Ubuntu or Debian, you should also probably use a name and version number.

Second, if there is no good answer to the first part: is there a programmatic way to ask postgresql where it's pulling pg_hba from?

Yes, there is a tool called pg_conftool. The default cluster's name is main. If you want the 9.5/main cluster. You can do this..

pg_conftool -s 9.5 main show hba_file
/etc/postgresql/9.5/main/pg_hba.conf

You can see conftool can make use of a version and name, but strictly it may not require one.

/usr/bin/pg_conftool [options] [<version> <cluster name>] [<configfile>] <command>

If you want to know more about a cluster in this context, check out check out all the binaries starting with pg_* but first and foremost pg_ctl and pg_ctlcluster (the debian wrapper)

Answer 2

Is there a programmatic way to ask postgresql where it's pulling pg_hba from?

show hba_file;
-- or
select current_setting('hba_file');