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.
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 upgrade
Once updated, create a file repository configuration.
Next, import the signing key and update your system as:
Finally, install PostgreSQL server and client with the command:
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:
Understanding the ph_hba File
By default, the ph_hba configuration file is in the root cluster directory.
In our example, it is in:
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.
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).
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.
host db_name all 192.168.0.112 scram-sha-256
Reject all connections from a specific IP subnet
host all all 192.168.1.110 255.255.0.0 reject
Allow or reject a specific IP network segment
host all all 192.168.0.1/24 reject
Specifying addresses in IPv4 protocol.
Allow any user to connect to any database on IPv6
host all all ::1 trust
To cover both IPv6 and IPv4, you can specify the hostname instead.
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:
The -D specifies the directory containing the config file. If none is specified, the default is used.
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.