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.