rds_superuser role in postgres RDS server
you were supposed to grant stan
to rds_superuser
in order to do that. You did:
rds=> create user stan;
CREATE ROLE
rds=> CREATE DATABASE foobar WITH OWNER = stan;
ERROR: must be member of role "stan"
you should:
rds=> grant stan to su_rdsadm;
GRANT ROLE
rds=> CREATE DATABASE foobar WITH OWNER = stan;
CREATE DATABASE
I did it as rds superuser:
rds=> \du+ su_rdsadm
List of roles
Role name | Attributes | Member of | Description
-------------+-------------------------------+----------------------+-------------
su_rdsadm | Create role, Create DB +| {rds_superuser,stan} |
| Password valid until infinity | |
rds=> select current_user;
current_user
--------------
su_rdsadm
(1 row)
It's good to know this further. This limitation of rds_superuser for ownership/grants and so on will keep hitting you until you grant role whose objects you want to manipulate (or on which behalf you want to grant) to rds superuser.
Related videos on Youtube
![Tomislav Mikulin](https://i.stack.imgur.com/hWgLO.png?s=256&g=1)
Tomislav Mikulin
I'm a Developer/DevOps engineer interested in containers/go/kubernetes and everything in between...
Updated on April 18, 2021Comments
-
Tomislav Mikulin about 3 years
I just created a new postgres RDS instace on aws (through the dashboard), and I gave it a default user, lets call him "jack".
When I logged in to the instance, I saw my created user "jack", and that he had a role "rds_superuser" attached. (so I thought that I can do the same things that I used to do with superuser on a regular postgres server).
I checked the documentation, I saw that wasn't possible.
As logged in as the default user "stan", I created a new database user like "stan", and wanted to create a new databases with the owner being the user "stan", I couldn't?
I entered something like this:
CREATE DATABASE foobar WITH OWNER = stan;
But I got an error, saying something like:
ERROR: must be member of role "stan"
So, what I did was, made the role "stan", logged out as the default user "jack", logged into the RDS instance as "stan", and created that database with him as the owner.
Since I had three different users, I had to repeat that last step three times.
My question, is there a way, that I can make the default user "jack" that I created during RDS postgres creation, capable of creating new databases (like superuser on a regular postgres server installation) and giving the different owners like this:
CREATE DATABASE foobar WITH OWNER = stan;
Tnx, Tom
-
filiprem about 6 yearsdid you try
CREATE DATABASE x
followed byALTER DATABASE x OWNER TO y
? not using amazon rds here, just curious... -
Tomislav Mikulin about 6 yearsjust did, that works! Tnx man!
-
Tomislav Mikulin about 6 years@filiprem, after I do ALTER DATABASE x OWNER TO y, do I still need to do GRANT ALL PRIVILEGES ON DATABASE y TO x, or is it redundant?
-
filiprem about 6 yearsI think it's redundant, there's not so many database level privileges (only CREATE, TEMPORARY and CONNECT)
-