Postgresql – RDS Postgres chicken-and-egg problem altering roles

amazon-rdsawspostgresql

I just created an RDS Postgres instance, the user postgres (and it's master password), and connected via psql as user postgres.

Then I ran the script generated from the current system using pg_dumpall --schema-only, and it immediately failed on the ALTER ROLE commands:

ERROR:  must be superuser to alter superusers

Ok, but how do I grant superuser to postgres? All of the documentation I've found "conveniently" skips that part…

EDIT: Here are the exact commands and error message:

postgres=> CREATE ROLE "READONLY";
CREATE ROLE
postgres=> ALTER ROLE "READONLY" WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'mdXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
ERROR:  must be superuser to alter superusers

Best Answer

Your RDS master user is not a superuser.

ALTER ROLE changes the attributes of a PostgreSQL role. [...]

Roles having CREATEROLE privilege can change any of these settings except SUPERUSER, REPLICATION, and BYPASSRLS...

Omit incompatible settings from your ALTER ROLE command.

Related Question