0

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).

1 Answer 1

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.

1
  • 1
    Thank you, I did some experiments and your answer was exactly the part I needed to hear! You do have a typo there though, as role_admin is essentially the intermediate role. You might want to update it for other readers. As you said, it's more about defaults than about building a bunker. P.S.: [email protected] is a standard form generated by GCP IAM
    – PeV
    Sep 14, 2022 at 10:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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