Azure Database for PostgreSQL - Flexible Server - administrator role permission

Question

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-security#access-management While you're creating the Azure Database for PostgreSQL - Flexible Server instance, you provide credentials for an administrator role. This administrator role can be used to create more PostgreSQL roles.

I have created a user named liquibase by using the server admin account and a databse，and also grant all privileges of the databse to liquibase，but the service account cannot access the tables created by liquibase. (Flexible Server Postgres 13)

Does anyone know if this is normal?

Answer 1

Hi aya,

Thanks for reaching out to Microsoft Q&A.

In Azure Database for PostgreSQL - Flexible Server, the administrator role indeed has permissions to create users and manage databases, including the ability to grant privileges. However, an important nuance is that privileges granted at the database level (ex: ALL PRIVILEGES ON DATABASE) do not automatically extend to newly created tables within that database. Instead, permissions need to be granted at the schema or table level for each specific object.

In your case:

Database Privileges: While you've granted ALL PRIVILEGES ON DATABASE to the liquibase user, this permission does not extend to schema or table-level permissions, which is likely why the admin account can't access liquibase's tables by default.

1. Solution: To allow the admin or other users to access liquibase’s tables, you need to adjust schema-level privileges:

   GRANT USAGE ON SCHEMA public TO admin_user;

   GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO admin_user;

2. Default Privileges for New Tables: To ensure new tables created by liquibase are accessible to the admin by default, set default privileges:

   ALTER DEFAULT PRIVILEGES FOR USER liquibase IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO admin_user;

This should make the tables accessible to the admin user while ensuring ongoing access to new tables created by liquibase.

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.