2

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 ?

swapnil solanki
  • 76
  • 3
  • First suggestion in google search. https://stackoverflow.com/questions/10757431/postgres-upgrade-a-user-to-be-a-superuser – Oleh Tarasenko May 18 '22 at 09:05
  • In Azure PostgreSQL on Flexible Server, We cant create any user as super user as it is Platform as service and managed by Azure. Only we can create admin user with elevated privileges as compared to other user and roles. – swapnil solanki May 18 '22 at 10:00
  • This is a major administration hole in the Azure offering. As it stands, the creator of an object (if other than the "admin" user) has to grant access to the admin role in order for them to manage it. Not a good design. – Dave Bennett Jul 21 '22 at 17:22

0 Answers0