Relatively new to AWS RDS PostgreSQL environment. We are creating a database with multiple schemas. Users are grouped by "schema_role" that have privileges to create objects in assigned schemas.
My challenge is that objects are owned by different users; this is by design. Now, the sudo “SUPERUSER” privileges in AWS RDS are limited. When an object's created, PostgreSQL automatically assigns ownership. Object ownership confers complete control over the object – its access, integrity, and administration. By default, only the object owner may select from a table until access is granted to others. If the user does not have the necessary permission, access will be denied.
The AWS RDS "SUPERUSER" a.k.a “master user system account” does not have authority to grant the user permission to others or to select on an object created by other users, but does have ability to have object dropped. Strange!!!
We deployed an “event_trigger” (below) which is at the time of an object creation, in addition to the object ownership, it also grants all privileges to the “admin_role”.
CREATE OR REPLACE FUNCTION auto_grant_func() RETURNS event_trigger LANGUAGE plpgsql AS $$ DECLARE obj record; BEGIN FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag='CREATE TABLE' LOOP EXECUTE format('GRANT ALL PRIVILEGES ON TABLE %s TO " admin_role" WITH GRANT OPTION',obj.object_identity); EXECUTE format('GRANT SELECT ON TABLE %s TO "all_ro_role"',obj.object_identity); END LOOP; END; $$;
Having done this, “admin_role” has more or less capability to manage all object permissions in all schemas in database.
My question is: How can I limit permissions while using this methodology?
Let’s say a schema owner A_role wants to manage his own objects within the schema? If I make him part of “admin_role”, he inherits access to all objects in all schemas. So, how can I reduce the scope of his access to schema level only?