4

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
3
  • If you are using RDS, then you don't. Is it necessary? Are there follow-on errors?
    – jjanes
    Feb 2, 2021 at 19:18
  • @jjanes you don't alter roles in RDS (only create them)???? The AWS docs show ALTER ROLE commands being successfully executed.
    – RonJohn
    Feb 2, 2021 at 19:32
  • You don't grant superuser to postgres. You can alter roles, just not in that way.
    – jjanes
    Feb 2, 2021 at 21:21

1 Answer 1

3

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.

3
  • postgres can't alter what it doesn't have privs to alter (which makes perfect sense), even if what it's doing is saying "you can't do what I don't have permission to grant you!" Am I interpreting that correctly?
    – RonJohn
    Feb 2, 2021 at 19:53
  • 1
    Interpert it as "U Can't Touch This". Cue in MC Hammer at will.
    – mustaccio
    Feb 2, 2021 at 19:58
  • @RonJohn That is the case, although it does seem to go against with the most straightforward interpretation of the docs and ERROR message. Perhaps this could be improved.
    – jjanes
    Feb 2, 2021 at 22:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.