Upgrading RDS postgres from 16.8 to 17.4 permission error

Identifying the Role Grant To determine which role has been granted the session_replication_role parameter, run the following SQL:

SELECT * FROM pg_parameter_acl;

For example, you might see output like this:

  oid  |         parname          |                          paracl
-------+--------------------------+-----------------------------------------------------------
 16467 | session_replication_role | {rds_superuser=sA/rds_superuser,postgres=s/rds_superuser}

In this output, notice that the role postgres is listed as having the parameter set GRANT.

Upgrade Error Scenario

When attempting to perform an upgrade, you might encounter an error similar to:

**Database instance is in a state that cannot be upgraded: command: "/rdsdbbin/postgres/bin/psql" --echo-queries --set ON_ERROR_STOP=on --no-psqlrc --dbname=template1 --host /rdsdbdata/log/upgrade --port 8193 --username rdsadmin -f "/rdsdbdata/upgrade/db/pg_upgrade_output.d/20250225T155427.196/dump/pg_upgrade_dump_globals.sql" >> "/rdsdbdata/upgrade/db/pg_upgrade_output.d/20250225T155427.196/log/pg_upgrade_utility.log" 2>&1
...
ERROR: permission denied for parameter session_replication_role**

This error occurs because the postgres role has been granted the session_replication_role parameter, which interferes with the upgrade process.

Resolving the Issue

To resolve the error, you need to revoke the permission from the role that was granted this parameter. if the postgres role is the one set with the parameter, execute:

REVOKE SET ON PARAMETER "session_replication_role" FROM "postgres";

This command removes the parameter assignment from postgres so that the upgrade can proceed without permission issues.

Context and Best Practices

Highest-privileged Role:

In Amazon RDS for PostgreSQL, the rds_superuser is a critical role. It is assigned by default to the master user (postgres) to simulate superuser capabilities (note that the actual RDS superuser is rdsadmin managed by AWS). Although postgres appears to have this parameter, it’s not the same as the native PostgreSQL superuser and has additional restrictions.

Role Nesting

When you grant additional roles to a user, those roles become nested within their existing privileges. This means that a user may inherit the capabilities of both the rds_superuser and any other role they are assigned. As a result, careful management of parameters like session_replication_role is essential.

Upgrade Considerations:

Always verify parameter assignments before performing an upgrade. This check ensures that roles with elevated privileges are not inadvertently interfering with system processes.

For further details, refer to: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Roles.rds_superuser.html