Permission denied for table even with "postgres" user

[jairoperezaquin]

Hi,

I have non-admin users creating tables and I can't query them even though I'm "postgres" user and supposed to be the user with the highest level.

I haven't seen this happening in similar databases like Aurora RDS.

For now, we set a policy that tables should always be created and owned by "postgres", but ideally I could access any tables in the database I administrate.

Thanks!

[ms4446]

Cloud SQL Postgres permissions model adheres to Postgres native behavior, granting table access rights exclusively to the table owner by default. This means that while the postgres user has full superuser privileges, it does not automatically gain access to tables created by other users unless privileges are explicitly granted. Unlike Amazon Aurora RDS, which often provides broader default privileges to admin users through managed configurations, Cloud SQL requires manual privilege management for more granular control.

To address this, the recommended solution is to use ALTER DEFAULT PRIVILEGES. This allows the postgres user to automatically receive specific permissions on all new objects created in the database. For instance, you can configure default privileges at the schema or database level:

-- Grant default privileges for objects created by any user in a specific schema:
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT SELECT ON TABLES TO postgres;

-- Grant full access to postgres for all new objects in a particular schema:
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT ALL ON TABLES TO postgres;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT ALL ON SEQUENCES TO postgres;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT ALL ON FUNCTIONS TO postgres;

-- Grant default privileges for objects created by any user in all schemas:
ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO postgres;
ALTER DEFAULT PRIVILEGES GRANT ALL ON SEQUENCES TO postgres;
ALTER DEFAULT PRIVILEGES GRANT ALL ON FUNCTIONS TO postgres;

Note: ALTER DEFAULT PRIVILEGES applies only to future objects. For existing objects, you must manually grant privileges using GRANT statements, which can be automated through periodic scripts or database event triggers.

Changing ownership policies, such as making postgres the owner of all tables, is another option but often impractical and restrictive. An alternative is to use a shared role (e.g., shared_owner) for managing ownership, but this adds complexity and potential security risks. For temporary access, the postgres user can assume the privileges of the table owner using SET ROLE, and revert with RESET ROLE.

Ultimately, leveraging ALTER DEFAULT PRIVILEGES is the most scalable and secure solution. When combined with clear documentation of privilege policies and periodic maintenance, it ensures that postgres retains the necessary access for effective database administration while maintaining PostgreSQL's granular permissions model.

[DavoLinares]

Hi, 

Thanks to this answer; it helps me a little with the problem I'm facing. We have a Terraform module to provision a Cloud SQL instance and all the resources needed, like service accounts, default DB, firewall rules, etc.

The problem arises when the App team creates a new DB with all their data using a custom pipeline (in AzDO). They use a service account for these deployments, which means the DB and all its objects are owned by this service account. When the App team needs to destroy the Cloud SQL instance, it fails due to DB ownership (the owner is the service account, and the service account executing the destroy is not the same).

Now, the main and important question is: After I run the ALTER PRIVILEGES command for all the DB objects for the Postgres user, will it work to destroy the DB when Terraform destroys everything, even though the owner of the DB is another service account?

Thanks!