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

October 2026: This post was reviewed and updated for accuracy.

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 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 to 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 show how to run the statement the query generates for each dependency, 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. These roles were added in PostgreSQL 14.

On PostgreSQL 14, 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, to pg_write_all_data.

At this point pg_write_all_data is a member of 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 includes 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 by running the REVOKE statement the query generates for it, 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.

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 rds_superuser, and pg_write_all_data is a member of app_user. So pg_write_all_data is a member of rds_superuser through app_user. A later major version upgrade cannot complete because of the resulting loop.

To also reproduce the direct-membership shape, 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 database before you start the upgrade. It returns each grant that makes pg_write_all_data or pg_read_all_data a member of rds_superuser, together with the statement that removes it. It covers both the transitive and direct shapes.

SELECT am.member::regrole::text AS built_in_role,
       am.roleid::regrole::text AS offending_role,
       format('REVOKE %I FROM %I;', am.roleid::regrole::text, am.member::regrole::text) AS fix_statement
FROM pg_catalog.pg_auth_members am
WHERE am.member IN (
        SELECT pg_catalog.to_regrole(r)
        FROM unnest(ARRAY['pg_write_all_data', 'pg_read_all_data']) AS r
        WHERE pg_catalog.to_regrole(r) IS NOT NULL
      )
  AND pg_catalog.pg_has_role(am.roleid, 'rds_superuser', 'MEMBER')
ORDER BY 1, 2;

The following is an example of the output:

   built_in_role   | offending_role |                fix_statement
-------------------+----------------+----------------------------------------------
 pg_write_all_data | app_user       | REVOKE app_user FROM pg_write_all_data;
 pg_write_all_data | rds_superuser  | REVOKE rds_superuser FROM pg_write_all_data;

Each row in the output is one membership that you must remove before you upgrade. If the query returns no rows, your database is not affected and you can proceed with the upgrade.

The output contains the following columns:

• built_in_role – The built-in role that is part of the circular dependency.

• offending_role – The role that the built-in role is a member of. This is either rds_superuser or a role that is a member of rds_superuser.

• fix_statement – The REVOKE statement that removes the membership.

The query does not return memberships in roles that are not members of rds_superuser, because those memberships do not affect the upgrade.

Resolving the circular dependency

If the detection query returns any rows, complete the following steps before you upgrade:

Check which roles will lose privileges. Each REVOKE statement removes only the built-in role’s membership in the offending role. The offending role keeps its membership in rds_superuser and all of its privileges. However, roles that are members of the built-in role lose the privileges they inherited through that membership, including those of rds_superuser. To list these roles, run the following query:

SELECT roleid::regrole AS built_in_role,
       member::regrole AS affected_role
FROM pg_catalog.pg_auth_members
WHERE roleid IN ('pg_write_all_data'::regrole, 'pg_read_all_data'::regrole);

If the query returns no rows, no role loses privileges. If an affected role needs any of these privileges, grant them to the role directly.

Run the statement in the fix_statement column for each row. For the example output, run the following statements:

REVOKE app_user FROM pg_write_all_data;
REVOKE rds_superuser FROM pg_write_all_data;

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

Preventing the issue

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

• Do not grant rds_superuser, or any role that is a member of rds_superuser, to pg_write_all_data or pg_read_all_data.

• Run the detection query before you upgrade from PostgreSQL 14 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 for members of the built-in role. Running a fix_statement does not change the privileges of the offending role. It removes the privileges that members of pg_write_all_data or pg_read_all_data inherited from the offending role. Before you run it, review the affected roles as described in the Resolving the circular dependency section.

• Scope of the issue. This condition applies only to upgrades from PostgreSQL 14 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.

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. 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