PG_HBA.conf file

What does this file do? This file controls all access by users to the PostgreSQL server.

In order for clients to connect to the server, their IP address must be in the allowed list of users. The two common authentication methods that you will see being used for Theatre Manager clients are MD5 and trust.

  • MD5 does md5 password authentication and should be used for just about all entries to this file.
  • Trust allows clients to connect without password authentication; the only 'trust' entry should should be for the local server machine and/or localhost.

1. The first changes to make involve the pg_hba.conf file and the postgres.conf file. To do so, you'll need to use VI (a text editor) and be the postgres user in Terminal. To do this, start Terminal and type

su - postgres

enter the password
2. Go to the postgres data directory by typing

cd data

3. Edit the postgres pg_hba.conf file that contains the addresses to listen on. Type

VI pg_hba.conf

(full pathname is /Library/Postgresql/[version]/data/pg_hba.conf)

You should see a page of information. If you do not, then type 'Shift Q' and then just a 'q', after which you can start the process over. If you see the list of text similar to the right, then:

Use the down arrow on your keyboard to go to the very end of the file.

4. EditHba

When you are at the end of the file, use the up arrow on your keyboard so that you are right after the first line in the IPv4 settings, where it says 'host all all 127.0.0.1/32 trust'. In the example the cursor is on the '#' on the line after.

type the

I

key and the message at the bottom will change to Insert.
5. EditHba

Edit the pg_hba.conf so that its final settings are similar to the window on the right.

Type directly into Terminal so the data looks like the window above. Use the Delete key to get rid of characters. You will likely end up typing the following lines where the first one is your subnet. This is the most typical example we've seen at venues

host all all 192.168.1.0/24 md5

NOTE: For the 127.0.0.1/32 option, edit the handshaking to be TRUST at the end of the line to allow backups to run unaided.

NOTE: If your machine uses IPv6 (the new internet IP setting standard), you may also need to set ::1/128 to be TRUST instead of md5. If so, edit that line to look like:

host all all ::1/128 trust

NOTE: If you are running version 9.0 or higher of Postgres in a Mac environment the local all all line should be set to TRUST.

NOTE: You may also need to edit the local all all line from md5 to TRUST. This can be determined if the backup script will not run without a password after changing the settings above for 127.0.0.1 and ::1/128.

The line you added (or need to add) are for:

  • The local subnet - as in 192.168.9.0/24
  • Other subnets that need to access the data - as in 10.1.5.0/24
  • Any single machines that must have access - as in 55.66.77.88/32 (via VPN)

At the end of the subnet, the /24 refers to a complete subnet when you want any machine on the subnet to access the database as per CIDR rules. This is what is used most often.

The /32 refers to a particular machine. If you use this option, you will need to provide the exact computer IP that you want to allow to access the database.
6. EditHba

At the end, type, in this order:

hit the esc key

(the insert mode will dissappear)

Shift Q

(the window will show the 'Entering Ex mode' message)

wq

and the window will clear.

 

Reference for settings in the pg hba.conf file https://www.postgresql.org/docs/current/auth-pg-hba-conf.html
7. In the command line, type the following to reload the configuration:

pg_ctl reload

Terminal will say 'server signalled'