Enforce table ownership

Question

Here's the deal,: I have postgresql databases (GCP Cloud SQL deployment, I don't care about version) and I want to enforce ownership of tables.

Specifically, an application logs in to the database mydb via GCP IAM account, which has assigned role_admin. The application needs to create a table mytable owned by role_mydb_admin. So it needs to first call SET ROLE role_mydb_admin; and then proceed to create the table. Here's how the ownership looks like:

Role                  | Member of
----------------------+-----------------
[email protected] | role_admin
role_admin            | role_mydb_admin

Name    | Owner
--------+-----------------
mytable | role_mydb_admin

If, for some reason, the user does not set the role to role_mydb_admin, the table will be created under the role_admin. That is a problem I need to avoid.

One way to get around this is to create a function that will intercept CREATE TABLE via event trigger and reassign incorrectly created tables. That works but is not a good enough solution, because some applications are performing a lot of DDL manipulations as part of their work and it calls triggers this function excessively. Secondly, I don't like it.

What I would like would be some kind of policy, which would ensure that the table can be created only with role_mydb_admin user. Is something like this possible? Something like deny create table if owner not equal to role_mydb_admin.

Or if I could have a role_admin which would simply not have the CREATE and ALTER permissions, but have the permission to SET ROLE to role_mydb_admin (which has these permissions).

Answer 1

Easy; use an intermediate NOINHERIT role:

CREATE ROLE admin_noinherit NOLOGIN NOINHERIT;
REVOKE role_mydb_admin FROM "[email protected]";
GRANT role_mydb_admin TO admin_noinherit;
GRANT admin_noinherit TO "[email protected]";

Now "[email protected]" (weird name) is a member of role_mydb_admin, but does not inherit permissions from that role.

Then change the schema so that only role_mydb_admin has the CREATE privilege. After that, "[email protected]" must use

SET ROLE role_mydb_admin;

to be able to create a table, and the table ownership is as it should be.

Note, however, that there is nothing that keeps "[email protected]" FROM running

ALTER TABLE myschema.tab SET OWNER TO "[email protected]";

at some later point. But I guess you are only trying to establish reasonable default permissions.