# 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 [<mark style="color:blue;">PostgreSQL Documentation</mark>](https://www.postgresql.org/about/news/postgresql-16-released-2715/).

## Version upgrade

IONOS Cloud offers an easy upgrade to PostgreSQL 16 through the [<mark style="color:blue;">DCD</mark>](https://docs.ionos.com/sections-test/guides/databases/postgresql/how-tos/modify-cluster) or [<mark style="color:blue;">API</mark>](https://docs.ionos.com/sections-test/guides/databases/postgresql/api/v1-api/modify-cluster-attributes/upgrade-a-postgresql-cluster) and smoothly manages the [<mark style="color:blue;">Version upgrade considerations</mark>](#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 [<mark style="color:blue;">PostgreSQL Documentation</mark>](https://www.postgresql.org/docs/16/release-16.html#RELEASE-16-MIGRATION).

{% hint style="info" %}
**Note:** Consider the [<mark style="color:blue;">actionable steps</mark>](#actionable-steps) required to manage roles, privileges, [<mark style="color:blue;">actionable steps</mark>](#actionable-steps-1) to manage role inheritance, and your existing cluster setup during a self-managed upgrade to PostgreSQL 16.
{% endhint %}

### Version upgrade considerations

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

* [<mark style="color:blue;">Role and privilege management breakage</mark>](#role-and-privilege-management-breakage)
* [<mark style="color:blue;">Default role inheritance change</mark>](#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.