Learning PostgreSQL - Configuration files

 As part of Learning PostgreSQL series, today we will discuss configuration files that are essential in administering PostgreSQL server. These files will always reside in PGDATA, data directory of the PostgreSQL cluster.

In some installation, the configuration files might reside in another location like in Ubuntu based distribution, if you installed the database software using package installation type, then there are chances that these files would reside in `/etc/postgresql/<version>/main/`.

Please note that you can always get the location of configuration file by connecting to the database. I have installed the database using package method on a Linux distribution.

# make sure the PostgreSQL service is up and running

$ sudo service postgresql status
12/main (port 5432): online

# connect using the psql
$ psql
psql (12.17 (Debian 12.17-1.pgdg120+1))
Type "help" for help.

postgres=# show config_file;
               config_file               
-----------------------------------------
 /etc/postgresql/12/main/postgresql.conf
(1 row)

$ ls -ltr /etc/postgresql/12/main/*.conf
-rw-r----- 1 postgres postgres  4933 Dec 23 16:55 /etc/postgresql/12/main/pg_hba.conf
-rw-r--r-- 1 postgres postgres 27033 Dec 23 16:55 /etc/postgresql/12/main/postgresql.conf

So for my server, the files are present under `/etc/postgresql/12/main/` location. So let's start discussion about the configuration files that are important in managing PostgreSQL cluster.

pg_hba.conf

This file is also called as Host Based Authentication file, depending upon the method specified for a particular user, he/she will be authenticated or rejected connection to connect. The format of this file is a set of records, one per line. Blank lines and comments (lines after #) are ignored.

Records present in this file will decide which hosts are allowed to connect, how clients are authenticated, which PostgreSQL users can connect and to which database they have access to.

The record consists of 5 columns, which are:

1. TYPE: This defines if a user is local or remote, using local for local users and host for remote users. The host value includes both SSL-encrypted and non-SSL connections. 
2. DATABASE: Specifies database name(s) to which the users will be allowed/rejected to connect. The values are all, a specific database or list of databases separated by commas or replication. The replication value will come in effect if the connection requests for a physical replication, this is for replicating data from one database to another.
3. USERNAME: Can be all, a username, a group name prefixed with "+" or a comma-separated list of users.
4. ADDRESS: Specifies hostnames, IP addresses or an IP CIDR blocks from which the connection is allowed/rejected.
5. METHOD: Specifies how the client will be authenticated.
• trust: Allows connection without password. Most dangerous, do not use this in PROD.
• reject: Straightaway, reject even if the credentials are valid. This can be used to filter out certain users or certain hosts.
• scram-sha-256: Perform scram-sha-256 authentication to verify user's password.
• md5: Perform scram-sha-256 or md5 authentication to verify the user's password

Please note that I have discussed, some of the widely used values for the records, please check community documentation for more detailed information on the records.

Below are some examples of records in `pg_hba.conf `file:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only

# for local connections ADDRESS field can be left empty
local   all             all                                     peer

# IPv4 local connections

# Allows all users to connect to all databases whose

# client IP is 127.0.0.1/32 (localhost) with password required
host    all             all             127.0.0.1/32            md5

# Remote user connections with IP CIDR, multiple databases

host    db01,db02       akgirme         192.168.0.0/20          scram-sha-256

A server reload is required if you make any changes in `pg_hba.conf` file to be effective.

postgresql.conf

`postgresql.conf` is PostgreSQL's main configuration file and the primary source of configuration parameter settings. This file is generated using `initdb` and normally stored in data directory of the cluster.

The `postgresql.conf` is similar to what we have in Oracle PFile, this for some parameters acts like SPFile, but there is difference. In Oracle, when we modify a dynamic parameter in SPFile then it takes effect immediately without needing of any restart, but in PostgreSQL even dynamic parameters require a reload of services because `postgresql.conf` file is read at Server startup. 

This file also has a major difference in terms of Oracle's Pfile, the Pfile in Oracle comes empty, and we have to manually add the parameter, whereas in `postgresql.conf` file contains all parameters with their default values, so we do not have to search them, we just have to uncomment them and save the file.

postgresql.auto.conf

When a database user makes changes to some parameters using `ALTER SYSTEM` command, then the changes are stored in the `postgresql.auto.conf` file. This file is managed by PostgreSQL cluster itself and there not should be no manual changes made to this file. This file is read at the end, so configuration settings present in this file will always override settings in other files.

The `ALTER SYSTEM` command can only be executed by a superuser. Later in PostgreSQL 15, permissions to alter individual configuration parameters can be granted to the non-superusers. The value of the configuration parameter which will be modified by `ALTER SYSTEM` command should be enclosed in single quotes, otherwise the value of that parameter will be reset to DEFAULT and the entry for that parameter will be removed from `postgresql.auto.conf` file.

This is all for this post, I know in this post, I have not discussed any parameters present in the configuration file because if I did this post would have been a very long post. So I kept it short, and I will cover the parameters wherever it is necessary. If you like me to cover any other topic, please leave a comment and I will try to make a post on the same as early as possible.