CODEWITHSUNDEEP
postgresqlamazon-aurora
kravb
kravb

Reputation: 558

Superuser cannot create or alter roles

Created a user/role via following method is Aurora Postgres:

CREATE ROLE rds_user_test;
GRANT rds_superuser to rds_user_test;
GRANT rds_iam TO rds_user_test;

When I login using IAM DB Auth as rds_user_test it appears that I can do all operations as needed except creating or altering roles (maybe other functionality is missing but haven't tested all operations yet). When I check role memberships of this new role against other roles that are able to create/alter roles, both are members of superuser.

I also followed the instructions here:
https://aws.amazon.com/premiumsupport/knowledge-center/rds-aurora-postgresql-clone-master-user/

Still get the same permissions error:

[42501] ERROR: permission denied to create role

Any thoughts on why this new role cannot create/alter other roles even though it seems to have the same privileges of superuser as other roles?

Upvotes: 2

Views: 4523

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657022

rds_superuser on Amazon Aurora is typically not a superuser. Check with:

SELECT rolsuper FROM pg_roles WHERE rolname = 'rds_superuser';

But to create a role, you don't need superuser privileges. All you need is the CREATEROLE privilege. Check if your user has that:

SELECT rolcreaterole FROM pg_roles WHERE rolname = 'rds_user_test';

Else you need to grant it (as a role that's allowed to do so):

ALTER ROLE rds_user_test CREATEROLE;

Any role with the CREATEROLE privilege can do that (typically including rds_superuser).
The manual:

Roles having CREATEROLE privilege can change any of these settings except SUPERUSER, REPLICATION, and BYPASSRLS; but only for non-superuser and non-replication roles.

The instructions you followed, explicitly instruct to add CREATEROLE, you seem to have skipped that bit:

CREATE ROLE new_master WITH PASSWORD 'password' CREATEDB CREATEROLE LOGIN;

Upvotes: 2

Related Questions