rds_superuser role in postgres RDS server

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:

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

did you try CREATE DATABASE x followed by ALTER DATABASE x OWNER TO y? not using amazon rds here, just curious... — filiprem
@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? — Tomislav Mikulin
I think it's redundant, there's not so many database level privileges (only CREATE, TEMPORARY and CONNECT) — filiprem

Vao Tsun Vao Tsun · Accepted Answer · 2018-04-12T08:35:43

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.

rds_superuser role in postgres RDS server

1 Answers