Unleashing PG16 New Features – pgHBA Features

In our journey through the ever-evolving landscape of PostgreSQL, last week, we embarked on an exciting exploration of the newest and most intriguing features that PostgreSQL 16 has to offer. Our goal was to delve deep into the core features of the PostgreSQL 16 version. If you missed our first post in the series, fear not and Click here

For today, we continue our voyage by delving further into the HBA changes introduced in PostgreSQL 16. This feature is at the core of making pg_hba.conf and pg_ident.conf more in line with postgresql.conf, even if the parsing logic of the first two is not the same as the third one as GUCs require their own thing, while HBA and ident files need to handle full entries made of a sequence of items.

Prior to delving into the details of these two features, we want to express our sincere gratitude to Julien Rouhaud Michael Paquier and a few more PG Community members for their invaluable contributions to this feature in PG16. 

Commit URL: https://github.com/postgres/postgres/commit/a54b658c

Include directives for pg_hba.conf and pg_ident.conf:

As you know, in PostgreSQL, the pg_hba.conf file is used to define access control rules for client connections and the pg_ident.conf file is used to map system usernames to database usernames. This new change is primarily around the pg_hba_file_rules system catalog view. pg_hba_file_rules provides an interpretation of the current contents of pg_hba.conf, and whether they can be parsed. This view has been in existence since PG10. The configuration files pg_hba.conf and pg_ident.conf, as well as postgresql.conf, now support the directives to include other files: include, include_if_exists, include_dir.

Columns containing the file name and rule/map number have been added to pg_hba_file_rules and pg_ident_file_mappings

Here’s how we can use the include directives:

include:

The include directive allows you to include another file in your configuration. For example, in pg_hba.conf, you can include rules from another file like this:

include '/path/to/another/pg_hba.conf'

This allows you to split your configuration into multiple files for better organization

include_if_exists:

The include_if_exists directive is similar to include, but it only includes the specified file if it exists. This can be useful if you want to include optional configuration files. For example:

include_if_exists '/path/to/optional/pg_hba.conf

include_dir:

The include_dir directive allows you to include all files within a specified directory. This can be helpful for managing a large number of configuration files. For example:

include_dir '/path/to/directory'

PostgreSQL will read all the files in the specified directory as if they were part of the main configuration file.

These directives can help you organize and modularize your PostgreSQL configuration, making it easier to maintain and manage complex setups.

Please note that the specific file paths and directory paths should be adjusted according to your system’s file structure. Additionally, it’s essential to ensure that the PostgreSQL server process has appropriate permissions to read the included files or directories.

---

Commit URL: https://github.com/postgres/postgres/commit/8fea8683

Regular expression matching in pg_hba.conf and pg_ident.conf:

PostgreSQL 16 provides several options for access control and enhances other security features. This release improves the management of pg_hba.conf and pg_ident.conf files, including allowing regular expression matching for user and database names and including directives for external configuration files.

Both pg_hba.conf and pg_ident.conf allows for regular expression matching on usernames. pg_hba.conf also supports regular expression matching on database names.

Now, the format of the file is simple. There are two types of config lines:

local 	DATABASE 	USER 			METHOD [OPTIONS]
host 	DATABASE 	USER 	ADDRESS 	METHOD [OPTIONS]

For the USER field, you have more options:

username

group name (role) prefixed with + sign

name of file that contains a list of usernames, prefixed with @

and, of course, you can have any number of these elements, separated by a comma.

For example:

local db1 osdbuser,postgres,+osdb,@/etc/postgres/cron.users.lst trust

I could use regular expressions in the USER field, for example like this:

host all /-rw$ 192.168.2.0/24 scram-sha-256
host all /-ro$ 192.0.0.0/8  scram-sha-256

This would allow any user with a name ending with -rw to log in, as long as they connect from ip being one of 192.168.2.*, and any user with a name ending with -ro from ip that is one of 192.*.*.*.

In this blog post, we explored some exciting pg_hba features introduced in PostgreSQL 16, showcasing its ongoing commitment to security enhancements. Stay tuned for more insights into the world of PostgreSQL 16 New features, as we continue to uncover the latest advancements in upcoming blogs. Your feedback and engagement are greatly appreciated, so please feel free to share your thoughts and questions in the comments below.