Azure PostgreSQL Flexible Server - Admin user permission issue

Question

I have created new Azure database for PostgreSQL - Flexible Server with admin user as "admin_demo".

Now after creating database I have done followoing steps:
(1) create schema abc authorization admin_demo; -- creates new schema
(2) create role role_developer; -- creates new role
(3) grant usage, create on schema abc to role_developer; -- grant access to new role in schema
(4) grant select, insert, update, delete on all tables in schema abc to role_developer; -- grant access to new role in schema
(5) alter default privileges in schema abc grant select, insert, update, delete on tables to role_developer; -- grant access to new role in schema

Now I created one user with name "dev_user" and assigned "role_developer" role created in above step no 2

-- Create the user(s)
create user dev_user with password 'xyz';

-- Assign role(s) to the user(s)
grant role_developer to dev_user;

Now I login into database with user "dev_user" and create table "demo_table".
After that When I login into database with admin user i.e "admin_demo" and try to query the table "demo_table" created by user "dev_user" in above step, it throws me permission denied error.

My requirement here is "admin_demo" being the admin user should be able to anything(DDL/Alter etc) with any table in any schema throughout database irrespective of which user has created that table or function or procedure or sequence etc.

Can anyone help me with this ?

Answer 1

Hi, @sappu solanki Thanks for the ask and for using the Microsoft Q&A platform.

That is by design the admin will need to be part of the role role_developer
Please add your dev_user under membership as shown below and give it a try.

Please let me know if this helps or if you need any additional information.

Regards
Geetha

Answer 2

Hi, @GeethaThatipatri-MSFT Thanks for quick response.
Actually I already have Azure Database for PostgreSQL - Single Server where roles and users are configured in such a way where admin-user can do anything with DB objects irrespective of which user has created that object.
When I checked the membership for admin-user as suggested by you, it has no role or user assigned as member.

Following is properties of admin-user
It only has no members and is member of azure_pg_admin and pg_read_all_stats with admin option unchecked for both.

I am trying to migrate Azure PostgreSQL Single server to Flexible Server, and hence I am facing issue with roles and users configuration of Flexible Server.
I have used pg_dumall -r option to migrate roles and users.

Answer 3

@sappu solanki Is this the sole solution/recommendation by @GeethaThatipatri-MSFT that you've been using up until now, to use DDL triggers of some sort? If so, seems like this was an oversight by Microsoft with flexible server; in my opinion there should be a better work-around for all of this since the server admin technically isn't the admin. I've spent hours looking, but is this fixed in a later version of flexible server? (My current organization is using v11)