200

I would like to obtain the path to pg_hba.conf from the shell. The path varies between versions of PostgreSQL. For instance, for 8.4 and 9.1:

/etc/postgresql/8.4/main/pg_hba.conf
/etc/postgresql/9.1/main/pg_hba.conf

I have tried the pg_config command, but it does not seem to include this information.

This is so that I can use a uniform command for opening pg_hba.conf and other PostgreSQL configuration files for editing.

I'm using bash.

Roger Dahl
  • 2,192
  • 1
    How about locate pg_hba.conf ? – Pramod Nov 14 '13 at 17:48
  • 1
    @Pramod There could tens of them lying around. What's if you want to know which one of them is being used by Postgres. – Bleeding Fingers Feb 27 '14 at 09:03
  • 1
    @BleedingFingers Depending on your setup, you could probably just filter by access time. find / -name pg_hba.conf -amin -5 (if the server's been restarted in the last 5 minutes; easy enough to change it to -atime if it was last restarted a longer time ago). Or you could filter by access time newer than change time or one of the other find time-based options... (Although filtering by lsof | grep pg_hba.conf while a server's running doesn't work). – Parthian Shot Jun 08 '15 at 22:21

8 Answers8

265

pg_config is for compliation information, to help extensions and client programs compile and link against PostgreSQL. It knows nothing about the active PostgreSQL instance(s) on the machine, only the binaries.

pg_hba.conf can appear in many other places depending on how Pg was installed. The standard location is pg_hba.conf within the data_directory of the database (which could be in /home, /var/lib/pgsql, /var/lib/postgresql/[version]/, /opt/postgres/, etc etc etc) but users and packagers can put it wherever they like. Unfortunately.

The only valid ways find pg_hba.conf is to ask a running PostgreSQL instance where it's pg_hba.conf is, or ask the sysadmin where it is. You can't even rely on asking where the datadir is and parsing postgresql.conf because an init script might passed a param like -c hba_file=/some/other/path when starting Pg.

What you want to do is ask PostgreSQL:

SHOW hba_file;

This command must be run on a superuser session, so for shell scripting you might write something like:

psql -t -P format=unaligned -c 'show hba_file';

and set the environment variables PGUSER, PGDATABASE, etc to ensure that the connection is right.

Yes, this is somewhat of a chicken-and-egg problem, in that if the user can't connect (say, after screwing up editing pg_hba.conf) you can't find pg_hba.conf in order to fix it.

Another option is to look at the ps command's output and see if the postmaster data directory argument -D is visible there, e.g.

ps aux  | grep 'postgres *-D'

since pg_hba.conf will be inside the data directory (unless you're on Debian/Ubuntu or some derivative and using their packages).

If you're targeting specifically Ubuntu systems with PostgreSQL installed from Debian/Ubuntu packages it gets a little easier. You don't have to deal with hand-compiled-from-source Pg that someone's initdb'd a datadir for in their home dir, or an EnterpriseDB Pg install in /opt, etc. You can ask pg_wrapper, the Debian/Ubuntu multi-version Pg manager, where PostgreSQL is using the pg_lsclusters command from pg_wrapper.

If you can't connect (Pg isn't running, or you need to edit pg_hba.conf to connect) you'll have to search the system for pg_hba.conf files. On Mac and Linux something like sudo find / -type f -name pg_hba.conf will do. Then check the PG_VERSION file in the same directory to make sure it's the right PostgreSQL version if you have more than one. (If pg_hba.conf is in /etc/, ignore this, it's the parent directory name instead). If you have more than one data directory for the same PostgreSQL version you'll have to look at database size, check the command line of the running postgres from ps to see if it's data directory -D argument matches where you're editing, etc.

Craig Ringer
  • 5,304
  • This might not be valid for 9.3, I get: psql: invalid port number: "format=unaligned" when I run that psql command. – jcollum Jun 25 '14 at 15:39
  • @jcollum Nope, just a typo. -P not -p. Fixed. – Craig Ringer Jun 26 '14 at 03:40
  • This is the only answer that works if you have more than one version of PostgreSQL installed. – bonh Jul 18 '16 at 16:10
  • 1
    Is there any hope for Postges to standardize this? That would help with installation headaches a lot... – Scott Skiles Feb 09 '19 at 20:28
  • 1
    @ScottSkiles PostgreSQL doesn't control it, it's up to the packages and distributors, who all have strong (and different) opinions on where it should be. – Craig Ringer Feb 11 '19 at 02:59
  • Thanks, Craig. Does the official PostgreSQL documentation have a strong opinion? Do you have one? :-) – Scott Skiles Feb 11 '19 at 17:01
  • @ScottSkiles I would like the official PostgreSQL docs to better cover common deployments - the PGDG rpms and debs, the Windows installer, etc. While those are distributed by "PostgreSQL" they're strongly influenced by OS-specific packaging policies etc, so we don't have a free hand. The docs should cover them, but right now they pretty much assume you installed from source despite few users (especially those who need the docs) doing so. I posted on the pgsql-docs list about it recently. – Craig Ringer Feb 12 '19 at 05:09
  • @ScottSkiles I'd really love some help revising the documentation to better reflect the installs users actually do. – Craig Ringer Feb 12 '19 at 05:10
  • @CraigRinger - Cool! These docs? https://www.postgresql.org/download/macosx/ – Scott Skiles Feb 12 '19 at 13:21
  • @CraigRinger - Is there a place to update the docs? I'm on GitHub but I am having a bit of difficulty figuring out where the docs are hosted :-) – Scott Skiles Feb 12 '19 at 13:23
  • @ScottSkiles They're DocBook XML in the PostgreSQL source tree, in the doc/src/sgml/ directory. You can use the authorative sources on https://git.postgresql.org/ or the github mirror. – Craig Ringer Feb 13 '19 at 01:55
  • in kubuntu v20.04 i used sudo updatedb and sudo locate pg_hba.conf – SL5net Jan 04 '22 at 18:51
  • Thanks. Works on Postgres 14. – Binita Bharati Feb 24 '22 at 06:13
41

This is how I do it:

john@host:~$ ls /etc/postgresql
9.1
john@host:~$ sudo vim /etc/postgresql/9.1/main/pg_hba.conf

Since the path is like this: /etc/postgresql/[VERSION]/main/pg_hba.conf

its_me
  • 1,928
12

Using command-line, ask your database:

$ psql -U postgres
postgres=# SHOW config_file;
neonidian
  • 291
  • 3
  • 5
7

I am using the following for config files detection:

$(ls /etc/postgresql/*/main/pg_hba.conf)
$(ls /etc/postgresql/*/main/postgresql.conf)
radeklos
  • 171
5

To find the path to pg_hba.conf, use the command:

sudo -u postgres psql -t -P format=unaligned -c 'show hba_file';
Julin Fernando Sandoval Vargas
  • 51
3

Simply ask from PostgreSQL.

Print pg_hba.conf file location:

su - postgres -c "psql -t -P format=unaligned -c 'show hba_file';"

Print postgresql.conf file location:

su - postgres -c "psql -t -P format=unaligned -c 'SHOW config_file';"
Feriman
  • 198
  • 4
3

Editing the correct pg_hba.conf with your default visual editor (vim, emacs, nano, joe, etc.) is as easy as:

$VISUAL /etc/postgresql/$(ls /etc/postgresql)/main/pg_hba.conf
Zanna
  • 70,465
christianbundy
  • 131
-2

To know where the file is simply type:

pg_lsclusters
Seth
  • 58,122
nirvanastack
  • 101