Azure Database for PostgreSQL
An Azure managed PostgreSQL database service for app development and deployment.
1,419 questions
How do members of the Azure_PG_Admin group modify objects owned by others in Azure Database for PostgreSQL?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(150.4, 71%, 24%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBE%3C/text%3E%3C/svg%3E)
Ben Eady
6
Reputation points
Is there any way to ensure that users in the azure_pg_admin group are granted all permissions to all database objects in Azure Database for PostgreSQL?
As we are beginning to use Azure for PostgreSQL we are encountering issues where someone creates a table and I (in the azure_pg_admin group) do not have permissions necessary to drop or modify the table later. It seems that with no super user role it is not possible to have a true admin and therefore objects can be created by others that are unable to be altered. Any workarounds or solutions for this?
Azure Database for PostgreSQL
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(220.8, 4%, 31%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESM%3C/text%3E%3C/svg%3E)
SSingh-MSFT 16,371 Reputation points Moderator2022-11-11T15:24:57.967+00:00 Hi @Ben Eady ,
Welcome to Microsoft Q&A platform and thanks for using Azure services.
The Azure Database for PostgreSQL server is created with the 3 default roles defined. You can see these roles by running the command: SELECT rolname FROM pg_roles;
azure_pg_admin
azure_superuser
your server admin user
Your server admin user is a member of the azure_pg_admin role. However, the server admin account is not part of the azure_superuser role. Since this service is a managed PaaS service, only Microsoft is part of the super user role. In Azure Database for PostgreSQL, the server admin user is granted these privileges: LOGIN, NOSUPERUSER, INHERIT, CREATEDB, CREATEROLE, REPLICATIONRegarding your ask to drop table, I am checking internally and will reply back to you.
Thanks for your patience.
-
SSingh-MSFT 16,371 Reputation points Moderator
2022-11-14T06:09:00.38+00:00 Hi @Ben Eady ,
I have asked about this question internally. Will get back to you when I receive a reply.
-
SSingh-MSFT 16,371 Reputation points Moderator
2022-11-15T04:49:52.467+00:00 Hi @Ben Eady ,
In case of flexible server, The Admin user can drop objects that belong to other users, but he is unable to update, insert, or delete, or select, for example, making the admin user (admin1) access other user objects (user1), we should run the following command under user1:
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO admin1;And he can change the permission based on its needs.
Hope this information helps. Please let us know your inputs.
-
SSingh-MSFT 16,371 Reputation points Moderator
2022-11-17T09:01:46.177+00:00 Hi @Ben Eady ,
Following up to see if the above suggestion was helpful. If this answers your query, do click
Accept AnswerandUp-Votefor the same. And, if you have any further query do let us know.
Sign in to comment
Sign in to answer