0

We are using a remote connection by postgres user to set the PostgreSQL DB in RDS on AWS.

There is an iam_ops user with ops role in the DB. If use it in the same AWS account to set default table privileges to the another rule readwrite, it's possible.

After it, if ops role create new tables in the future, iam_app_user with readwrite role can use it.

But we want to run all these settings by postgres user from a remote server. Since the iam_ops role is using by IAM authentication from RDS, it seems postgres user can't do the grant task as same as the iam_ops can do.

CREATE USER iam_ops WITH LOGIN;
CREATE USER iam_app_user WITH LOGIN;
CREATE ROLE ops;
CREATE ROLE readwrite;
GRANT rds_iam TO iam_ops;
GRANT ops TO iam_ops;
GRANT readwrite TO iam_app_user;
GRANT rds_iam TO iam_app_user;

-- create and use a db

CREATE SCHEMA main;

GRANT SELECT, INSERT, DELETE, UPDATE, TRUNCATE ON ALL TABLES IN SCHEMA main TO ops;
GRANT CREATE, USAGE ON SCHEMA main TO ops;

GRANT SELECT, INSERT, DELETE, UPDATE, TRUNCATE ON ALL TABLES IN SCHEMA main TO readwrite;
GRANT USAGE ON SCHEMA main TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA main GRANT SELECT, INSERT, DELETE, UPDATE, TRUNCATE ON TABLES TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA main FOR ROLE ops GRANT SELECT, INSERT, DELETE, UPDATE, TRUNCATE ON TABLES TO readwrite;

I think the postgres user can do everything. Is it really truth that it has some limitation as this case?

Current roles

postgres=> \du
                                                                          Role List
       Role           |                                   Attribute                              |                        Group
----------------------+--------------------------------------------------------------------------+-------------------------------------------------------------
 iam_app_user         |                                                                          | {rds_iam,readwrite}
 iam_ops              |                                                                          | {rds_iam,ops}
 ops                  | Can't login                                                              | {}
 postgres             | Can create role, database                                               +| {rds_superuser}
 rds_iam              | Can't login                                                              | {}
 rds_superuser        | Can't login                                                              | {pg_monitor,pg_signal_backend,rds_replication,rds_password}
6
  • I am pretty sure that postgres, if you can use that user, is no superuser in a hosted database. Sep 22, 2021 at 15:09
  • I listed some roles. The postgres belongs to rds_superuser group.
    – Miantian
    Sep 23, 2021 at 5:34
  • @LaurenzAlbe So this way can make it be a superuser? ALTER USER postgres WITH SUPERUSER;
    – Miantian
    Sep 23, 2021 at 5:47
  • @LaurenzAlbe Then where? The configuration file?
    – Miantian
    Sep 23, 2021 at 9:19
  • If you want to use a hosted database like Amazon RDS, you cannot get a superuser. To get a superuser, run PostgreSQL on your own machine. Sep 23, 2021 at 9:29

1 Answer 1

1

The name 'postgres' has no inherent special powers. It is common (and often the default for own-hosted environments) to name your most powered user that name, but is not mandatory. If you chose some other name, then that other name is what you have to use.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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