PostgreSQL 16 Upgrade Management

PostgreSQL 16 strengthens security by enforcing the principle of least privilege. It introduces backward-incompatible changes to user, role, and privilege management, along with the following features:

• Query Performance Optimization: Parallelized FULL and RIGHT OUTER joins, incremental sorting for more queries, and performance gains in COPY operations support faster query execution and bulk data loading.

• Privilege Security: The CREATEROLE privilege is restricted; users with this privilege can no longer modify or escalate other roles unless they have explicit ADMIN OPTION. Thus, reduces the risk of unauthorized role modification.

• Enhanced Monitoring: pg_stat_io provides real-time input and output (I/O) performance metrics, helping to diagnose performance bottlenecks and tune I/O-intensive workloads.

For more information, refer to the PostgreSQL Documentation.

Version upgrade

IONOS Cloud offers an easy upgrade to PostgreSQL 16 through the DCD or API and smoothly manages the Version upgrade considerations.

If you want to upgrade from an earlier PostgreSQL release to PostgreSQL 16 by yourself, you can migrate the data using pg_dump. For more information, refer to PostgreSQL Documentation.

Note: Consider the actionable steps required to manage roles, privileges, actionable steps to manage role inheritance, and your existing cluster setup during a self-managed upgrade to PostgreSQL 16.

Version upgrade considerations

You may encounter the following challenges when upgrading to PostgreSQL 16:

• Role and privilege management breakage

• Default role inheritance change

Role and privilege management breakage

PostgreSQL 16 introduces a fundamental tightening of the CREATEROLE privilege, which can impact the existing administrative and deployment scripts.

v15 and earlier	v16	Incompatibilities
Users with CREATEROLE can modify, delete, or grant membership to virtually any non-superuser role and any user they did not create.	Only users with ADMIN OPTION on the target role can modify, delete, or add members to the role.	Administrator User Lockout: An administrator user who was previously able to manage all other roles because they had CREATEROLE can no longer perform administrative tasks such as ALTER ROLE or DROP ROLE on roles created by a superuser or by the administrator before the upgrade, because they lack the required ADMIN OPTION.

Role constraints

With the upgrade to PostgreSQL 16, the CREATEROLE privilege now has restricted capabilities. Its ability to modify other roles is limited, resulting in the following constraints:

• Cannot Modify or Drop Pre-existing Roles: Administrative users, such as non-superuser administrators are unable to modify or drop roles created before the upgrade to PostgreSQL 16, as these roles do not automatically inherit the new ADMIN OPTION.

• Cannot Grant Membership: A CREATEROLE user can no longer add members to a role unless they were granted that role with the ADMIN OPTION. For example, GRANT some_role TO admin_user WITH ADMIN OPTION.

Actionable steps

The following lists the actions that you can perform while upgrading to PostgreSQL 16 to minimize the role restriction impacts:

1. Audit: List roles with CREATEROLE privilege and identify the specific actions they perform.

2. Action: Those roles that are used to grant or revoke role memberships, such as adding users to groups or to alter or drop other roles, grant the ADMIN OPTION on the roles they manage. This way, the roles can continue to perform the actions that were being performed before the upgrade.

Default role inheritance change

In PostgreSQL 16, the default behavior of role inheritance during the GRANT command is changed.

v15 and earlier	v16	Incompatibilities
Role membership grants were controlled solely by the granted role's INHERIT setting.	Role membership grants now have their own inheritance status set at GRANT time, with the ability to override the role's default using WITH INHERIT or NO INHERIT.	Unexpected Privilege Loss: If you relied on all members automatically inheriting privileges from a group role, you might need to explicitly use the WITH INHERIT clause in your GRANT scripts, or check if the roles' new default is correct.

Actionable steps

The following lists the actions that you can take while upgrading to PostgreSQL 16 to minimize the role inheritance impacts:

1. Audit: Identify roles using the NO INHERIT attribute, as their grants might lose the intended restriction.

2. Action: Consider the following:

• Script REVOKE and GRANT operations to reapply memberships using the new explicit syntax.

• Enforce non-inheritance by adding WITH INHERIT FALSE to the re-grants for all affected memberships.

• Update provisioning tools to always use the explicit WITH INHERIT TRUE/FALSE syntax for clarity.