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

This article explains how circular role dependencies can cause Amazon RDS and Aurora PostgreSQL major version upgrades to fail when upgrading from PostgreSQL 14 or earlier to PostgreSQL 15 or later.

• Circular dependencies occur when pg_write_all_data or pg_read_all_data built-in roles are members of rds_superuser, blocking upgrade grants
• Use a recursive SQL query to detect problematic role membership paths before upgrading
• Resolve dependencies by revoking the loop-closing role membership from the built-in role
• Confirm the detection query returns no rows before proceeding with the upgrade
• Prevent future issues by avoiding grants between rds_superuser and built-in roles

Following these pre-upgrade checks and remediation steps ensures smooth major version upgrades for PostgreSQL databases on RDS and Aurora.