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