Question:
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:
|
1 2 |
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:
|
1 2 |
CREATE DATABASE foobar WITH OWNER = stan; |
Tnx,
Tom
Answer:
you were supposed to grant stan to rds_superuser in order to do that. You did:
|
1 2 3 4 5 |
rds=> create user stan; CREATE ROLE rds=> CREATE DATABASE foobar WITH OWNER = stan; ERROR: must be member of role "stan" |
you should:
|
1 2 3 4 5 |
rds=> grant stan to su_rdsadm; GRANT ROLE rds=> CREATE DATABASE foobar WITH OWNER = stan; CREATE DATABASE |
I did it as rds superuser:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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.