New roles added in PostgreSQL 16

Introduction:

In this blog, we’ll explore one of the significant updates in PostgreSQL 16: the introduction of new and improved roles. Roles are at the heart of PostgreSQL’s security and access control system, and the latest version brings several changes that promise to refine the way we manage user privileges, access, and authentication. As we delve into these role-related updates, you’ll discover how PostgreSQL is committed to making your database administration experience more robust, secure, and user-friendly. So, let’s dive in and unravel the fresh horizons that PostgreSQL 16 roles offer.

pg_use_reserved_connections:

Before Pg-16, there was no way to allocate connections to one or more users unless they were configured as superusers. As a DBA, most of us have encountered situations where a company executive or a database developer was not able to gain access to the database on a hectic day. When such a situation arose, the attending DBA had two options in front of him: 

1. Have some other user(s) log out of the database so that the freed-up connection can be utilized by the company executive.
2. Add the company executive or developer to the list of superusers.

While these options are able to handle the situation, these approaches are not very elegant so to speak, and not recommended (especially #2) from an IT best practices perspective. 

With the introduction of the ‘pg_use_reserved_connections‘ role in Pg 16, the superusers now have the option to reserve a set of connections specifically for critical users like the company executives, database developers, department heads or core team members within each functional group – ensuring that they always have access to the database even if they are not part of the superuser group.

pg_use_reserved_connections allows the use of connection slots reserved via reserved_connections.

The reserved_connections parameter determines the number of connection “slots” that are reserved for connections by roles with privileges of the pg_use_reserved_connections role. Whenever the number of free connection slots is greater than superuser_reserved_connections but less than or equal to the sum of superuser_reserved_connections and reserved_connections, new connections will be accepted only for superusers and roles with privileges of pg_use_reserved_connections. If superuser_reserved_connections or fewer connection slots are available, new connections will be accepted only for superusers.

The default value reserved_connections  is zero connections. The value must be less than max_connections minus superuser_reserved_connections. This parameter can only be set at the server start.

Example:

In this example, I have set parameters in postgresql.conf file as follows:

max_connections = 5               
reserved_connections = 1          
superuser_reserved_connections = 0

We have set ‘superuser_reserved_connections’ to zero to ensure it does not interfere with our testing.

Here, I am creating a user named ‘reserved_user‘ as follows:

postgres=# create user reserved_user with password 'password';
CREATE ROLE

Now, connect using the ‘reserved_user‘ to database

postgres@localhost:~$ psql -U reserved_user -d postgres
Password for user reserved_user: 
psql (16.0 (Ubuntu 16.0-1.pgdg22.04+1))
postgres=> 

From four other terminals, we will repeat the previous command for testing.

postgres@localhost:~$ psql -U reserved_user -d postgres
Password for user reserved_user: 
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  remaining connection slots are reserved for roles with privileges of the "pg_use_reserved_connections" role

Log in as the superuser and grant permissions to the ‘reserved_user'.

postgres@localhost:~$ psql -U postgres -d postgres
postgres=# grant pg_use_reserved_connections to reserved_user;
GRANT ROLE

Now try to connect reserverd_user and we can establish the fifth session:

postgres@localhost:~$ psql -U reserved_user -d postgres
Password for user reserved_user: 
psql (16.0 (Ubuntu 16.0-1.pgdg22.04+1))
Type "help" for help.
postgres=> 

Now we can successfully establish the 5^th connection.

pg_create_subscription :

Allow users with CREATE permission on the database to issue CREATE SUBSCRIPTION.

Non-superusers can create subscriptions. The non-superusers must have been granted a pg_create_subscription role, and are required to specify a password for authentication.

Superusers can set password_required = false for non-superusers who own the subscription.

Example:

postgres=# select version();
                               version
----------------------------------------------------------------
PostgreSQL 16.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.3.1 20221121 (Red Hat 11.3.1-4), 64-bit

Here, I am creating a user named ‘testuser‘ as follows:

postgres=# create user testuser with password 'password' ;
CREATE ROLE

Log in as the superuser and grant permissions to the ‘testuser‘.

postgres=# GRANT pg_create_subscription TO testuser ;
GRANT ROLE

Now, connect using the ‘testuser‘ to osdb database.

postgres=# \c osdb testuser 
Password for user testuser:
You are now connected to database "osdb" as user "testuser".

osdb=> CREATE SUBSCRIPTION testsub CONNECTION 'host=192.168.113.209 port=5432 user=repuser dbname=osdb'
PUBLICATION testpub ;
ERROR:  password is required
DETAIL:  Non-superusers must provide a password in the connection string.

osdb=> CREATE SUBSCRIPTION testsub CONNECTION 'host=192.168.113.209 port=5432 user=repuser dbname=osdb'
PUBLICATION testpub WITH (password_required=false); 
ERROR: password_required=false is superuser-only
HINT:  Subscriptions with the password_required option set to false may only be created or modified by the superuser.

osdb=> CREATE SUBSCRIPTION testsub CONNECTION 'host=192.168.113.209 port=5432 user=repuser password=postgres dbname=osdb'
PUBLICATION testpub WITH (connect=false);
WARNING:  subscription was created, but is not connected
HINT:  To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
CREATE SUBSCRIPTION