PostgreSQL

PostgreSQL ph_hba Configuration

Host-Based configuration, commonly known as ph_hba.conf, is a special file used for PostgreSQL Client Authentication. You can think of it as a configuration file for managing access policies to the PostgreSQL database cluster.

Before we dive into how to work with the PostgreSQL configuration file, let us install PostgreSQL. If you have it already installed, feel free to skip the section.

Installing PostgreSQL

To follow along with this guide, you need to ensure you have PostgreSQL installed. I will illustrate how to install it on a Debian system.

On Arch System – https://linuxhint.com/install-postgresql-10-arch-linux/

On CentOS/REHL – https://linuxhint.com/install_postgresql_centos8/

Start by updating your system with the command:

sudo apt-get install update
sudo apt-get upgrade

Once updated, create a file repository configuration.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Next, import the signing key and update your system as:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - && sudo apt-get update

Finally, install PostgreSQL server and client with the command:

sudo apt-get install postgresql-13 postgresql-client-13

You can specify another PostgreSQL versions such as PostgreSQL 9, 10, 11, 12, 14, etc.

You can now initialize the PostgreSQL cluster with the command:

pg_ctlcluster 13 main start

Understanding the ph_hba File

By default, the ph_hba configuration file is in the root cluster directory.

In our example, it is in:

/etc/postgresql/13/main/ph_hba.conf

The pg_hba follows a simple configuration format. It includes a set of configuration rules, one rule per line.

The configuration file is not sensitive to whitespaces, and all files preceded by a # are treated as comments and ignored.

The pg_hba rules cannot span for multiple lines as each line is interpreted as a separate rule.

The configuration file contains 5 records in the following format respectively:

  • Type Database User Address Method [Options]
  • Type – Represents the host type.
  • Database – Specifies the database for which the rule is configured.
  • User – The username associated with the set database matching the set rule.
  • Address – The client’s address. It can be a single IP or an entire subnet. PostgreSQL supports IPv4 and IPv6 addresses.
  • Method – Specifies the authentication method for the set rule.
  • Authentication options – Options to add functionality to the auth method specified.
  • Options – Authentication method options in the form of name=value.

PostgreSQL accepts various authentication methods. These include:

  • Trust – Used to set the auth method to accept connections unconditionally. If set to trust, anyone can connect to the database server as any user and any database without needing a password.
  • Reject – This method is similar to the trust method, but it rejects connections unconditionally.
  • Password – if the method is set to password, the connecting client must provide an unencrypted password.
  • Peer – The peer authentication method works on local connections by using the connecting client’s OS username and checking for a matching database for the username. An example would be psql command without the -U flag.
  • Cert – This authentication method uses the SSL certificates of the connecting client.
  • Ldap – Auth using an LDAP server.

The above is a list of some of the supported authentication methods. You can check out other methods in the official documentation.

Example Configurations

The pg_hba configuration is a raw text file that you can edit with any supported text editor.

The following are some configurations you can make either for troubleshooting or security purposes.

Allow any user to connect to any database (local).

TYPE DATABASE USER ADDRESS METHOD
local all all 127.0.0.0.1 trust

Allow any user from specific IP to connect to a specific database if the password is correct.

TYPE DATABASE USER ADDRESS METHOD
host db_name all 192.168.0.112 scram-sha-256

Reject all connections from a specific IP subnet

TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
host  all all 192.168.1.110 255.255.0.0 reject

Allow or reject a specific IP network segment

TYPE DATABASE USER ADDRESS METHOD
host all all 192.168.0.1/24 reject

Specifying addresses in IPv4 protocol.

Allow any user to connect to any database on IPv6

TYPE  DATABASE USER ADDRESS METHOD
host  all      all   ::1    trust

To cover both IPv6 and IPv4, you can specify the hostname instead.

TYPE DATABASE USER ADDRESS METHOD
host all all .local reject

Saving and Applying Configuration

Before saving the configuration, ensure it is in the correct format. To reload and apply the changes, you can use the pg_ctl utility.

The command is:

pg_ctl reload -s -D /etc/postgresql/13

The -D specifies the directory containing the config file. If none is specified, the default is used.

In Summary

The pg_hba.conf configuration file helps specify the client authentication to the PostgreSQL server. Making changes can allow you to secure your system and only allow specific users and hosts.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list