HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

RDS Postgres chicken-and-egg problem altering roles

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
problemeggpostgresrolesandalteringchickenrds

Problem

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

Solution

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.

Context

StackExchange Database Administrators Q#284541, answer score: 3

Revisions (0)

No revisions yet.