Fix circular role dependencies before upgrading Amazon RDS and Amazon Aurora PostgreSQL

Circular role dependencies can cause a major version upgrade of Amazon Relational Database Service (Amazon RDS) for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition to stall or roll back. This happens when you upgrade from PostgreSQL 14 or earlier to PostgreSQL 15 or later. During this upgrade, the service grants the built-in roles pg_write_all_data and pg_read_all_data to the rds_superuser role. If your database already has one of those built-in roles as a member of rds_superuser, that grant would close a membership loop, which PostgreSQL does not allow. As a result, the upgrade cannot finish.

This affects only the upgrade from PostgreSQL 14 or earlier into 15 or later, because that is when the grants are first added. If your database already runs PostgreSQL 15 or later, it has already received these grants and will not encounter this problem during a later upgrade.

In this post, we explain how circular role dependencies arise and how to detect them with a single diagnostic query that you run as a pre-upgrade check. We also walk through the steps to resolve any dependencies the query finds, so you can clear them before you start the upgrade.

Prerequisites

To follow along with this post, complete the following prerequisites:

• An Amazon Aurora PostgreSQL-Compatible DB cluster or an Amazon RDS for PostgreSQL DB instance. For instructions, refer to Creating and connecting to an Aurora PostgreSQL DB cluster or Creating and connecting to a PostgreSQL DB instance.

• A PostgreSQL client, such as psql, to connect to your database and run the SQL statements in this post. Connect using the primary user, which is a member of the rds_superuser role.

Understanding role membership in PostgreSQL

In PostgreSQL, a role can represent a user, a group, or both. You grant one role membership in another with the GRANT statement, so the member role can use the privileges of the role it is granted. For example, the following statement makes role_a a member of role_b:

GRANT role_b TO role_a;

Role memberships form a directed graph, and that graph must stay acyclic. PostgreSQL rejects any grant that would introduce a loop. This rule is documented in Role Membership, and the pg_auth_members catalog stores only a non-circular set of relationships.

Two roles matter for this post:

• rds_superuser is the administrative role in Amazon RDS and Aurora, and your primary user is a member of it.

• pg_write_all_data and pg_read_all_data are built-in roles that grant write and read access, respectively, to all tables, views, and sequences.

On PostgreSQL 14 and earlier, rds_superuser is not a member of either built-in role. The upgrade adds these memberships so that the administrative role keeps full data access on the new engine version.

How the circular dependency occurs

During the upgrade, the service runs this grant:

GRANT pg_write_all_data TO rds_superuser;

On its own, the grant is fine. It breaks only if the reverse relationship already exists, meaning pg_write_all_data or pg_read_all_data is already a member of rds_superuser (directly, or through some roles in between). Now the grant would close the loop. Because the upgrade cannot complete this grant, it fails and is rolled back, returning the database to its original version.

Databases usually reach this state through everyday grants that are each valid on their own. Consider these two steps:

1. You grant rds_superuser to an administrative or application user so that the user can perform management tasks. Granting rds_superuser this broadly is not a recommended practice.

2. You grant that same user, or a group role the user belongs to, membership in pg_write_all_data.

At this point pg_write_all_data is reachable from rds_superuser through the application user. When the upgrade later grants pg_write_all_data to rds_superuser, the loop closes.

The conflict shows up in two shapes, and the query in the next section catches both:

• Transitive membership – The built-in role sits at the end of a membership chain that runs through members of rds_superuser. For example, rds_superuser includes role1, which includes app_user, which is a member of pg_write_all_data.

• Direct membership – Someone granted rds_superuser to a built-in role, for example GRANT rds_superuser TO pg_write_all_data, so the built-in role is a direct member of rds_superuser.

Solution overview

The approach has three parts. First, you can optionally reproduce the condition on a test database to understand how it forms. Next, you run a single detection query as a pre-upgrade check to find any circular dependencies. Finally, you resolve each dependency the query reports by revoking the membership that closes the loop, and you confirm the check is clear before you upgrade.

Note: Estimated time to complete: 10–15 minutes. This does not include the major version upgrade itself.

Reproducing the condition in a test environment

Use a test database running PostgreSQL 14 or earlier.

Connect as the primary user and run the following:

CREATE USER app_user WITH PASSWORD 'example_password';
GRANT rds_superuser TO app_user;
GRANT app_user TO pg_write_all_data;

app_user is now a member of both rds_superuser and pg_write_all_data. A later major version upgrade cannot complete because of the resulting loop.

To reproduce the direct-membership shape instead, run the following:

GRANT rds_superuser TO pg_write_all_data;

Detecting the circular dependency

Run the following query as the primary user on your PostgreSQL 14 (or earlier) database before you start the upgrade. It walks the membership graph from rds_superuser and returns any path that reaches pg_write_all_data or pg_read_all_data, covering both the transitive and direct shapes.

WITH RECURSIVE role_path AS (
    SELECT roleid, member,
        ARRAY[roleid::regrole::text, member::regrole::text] AS path
    FROM pg_auth_members
    WHERE roleid::regrole::text = 'rds_superuser'
    UNION ALL
    SELECT am.roleid, am.member, rp.path || am.member::regrole::text
    FROM role_path rp
    JOIN pg_auth_members am ON rp.member = am.roleid
    WHERE NOT am.member::regrole::text = ANY(rp.path)
        AND array_length(rp.path, 1) < 50
)
SELECT
    path[1] AS start_role,
    array_to_string(path[2:array_upper(path, 1) - 1], ' -> ') AS intermediate_roles,
    path[array_upper(path, 1)] AS end_role,
    array_length(path, 1) - 1 AS chain_length
FROM role_path
WHERE path[array_upper(path, 1)] IN ('pg_read_all_data', 'pg_write_all_data')
ORDER BY chain_length;

The following is an example of the output:

start_role    | intermediate_roles             | end_role          | chain_length
--------------+--------------------------------+-------------------+-------------
rds_superuser | role1 -> app_user -> role2     | pg_write_all_data | 4
rds_superuser | role1a -> role3a -> app_user_a | pg_read_all_data  | 5

Any rows in the output identify memberships you should clear before you upgrade. If the query returns no rows, your database is not affected by this issue and you can proceed with the next steps. The end_role column names the built-in role involved, and intermediate_roles shows the path that reaches it.

If you run the query through the Amazon RDS Data API instead of a PostgreSQL client, replace the path[2:array_upper(path, 1) - 1] slice with array_to_string(path, ' -> '). The Data API does not parse arithmetic inside an array slice.

Resolving the circular dependency

If the pre-upgrade check returns any rows, resolve each one before you upgrade. The membership to revoke is the one that closes the loop: the role that is a direct member of the built-in role shown in the end_role column. In the query output, start_role is always rds_superuser, and end_role is the built-in role (pg_write_all_data or pg_read_all_data). The role you revoke is the last role in the path before end_role:

• For the transitive shape, this is the last role listed in the intermediate_roles column.

• For the direct shape, intermediate_roles is empty and the role is rds_superuser itself.

Revoke that role’s membership in the built-in role:

REVOKE <role> FROM pg_write_all_data;

For the direct-membership shape, the statement is:

REVOKE rds_superuser FROM pg_write_all_data;

This revoke removes only the loop-closing membership. It does not change the role’s membership in rds_superuser or any of its other privileges. However, if that membership was the role’s source of read-all or write-all access, the role loses that access after the revoke. Before you revoke, confirm the role does not rely on pg_write_all_data or pg_read_all_data for application access. If it does, grant the specific privileges the application needs directly, rather than through the built-in role.

After you revoke the membership, complete the following steps before you upgrade:

1. Run the detection query again and confirm it returns no rows.

2. Once the query returns no rows, the circular dependency is resolved and will not block the upgrade.

Preventing the issue

To avoid this during a future upgrade, follow these practices:

• Do not grant membership in pg_write_all_data or pg_read_all_data to a role that already holds rds_superuser, directly or through another role.

• Do not grant rds_superuser to pg_write_all_data or pg_read_all_data.

• Run the detection query before you upgrade from PostgreSQL 14 or earlier to PostgreSQL 15 or later.

• Test the upgrade against a restored snapshot before you upgrade production.

Considerations and limitations

Consider the following when detecting and resolving circular role dependencies:

• Privilege loss when revoking from a built-in role. Revoking a role’s membership in pg_write_all_data or pg_read_all_data removes only that membership, but if the membership was the role’s source of read-all or write-all access, the role loses that access. Before you revoke, confirm the role does not rely on the built-in role for application access. If it does, grant the specific privileges the application needs directly, rather than through the built-in role.

• Scope of the issue. This condition applies only to upgrades from PostgreSQL 14 or earlier to PostgreSQL 15 or later, because that is when the built-in roles are first granted to rds_superuser. A database already running PostgreSQL 15 or later has already received these grants and is not affected.

• Running the detection query through the Amazon RDS Data API. The Data API does not parse arithmetic inside an array slice. If you run the detection query through the Data API instead of a PostgreSQL client, replace the path[2:array_upper(path, 1) - 1] slice with array_to_string(path, ' -> ').

Conclusion

Circular role dependencies between the pg_write_all_data and pg_read_all_data built-in roles and rds_superuser can stall a major version upgrade of Amazon RDS for PostgreSQL or upgrade of Amazon Aurora PostgreSQL. In this post, we showed why the condition appears when upgrading from PostgreSQL 14 or earlier. We also showed how to detect it with a single query, and how to clear it by revoking the membership that closes the loop.

To learn more, refer to Amazon RDS for PostgreSQL and Working with Amazon Aurora PostgreSQL.

About the author