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 ?

3
  • First suggestion in google search. stackoverflow.com/questions/10757431/… Commented May 18, 2022 at 9: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. Commented May 18, 2022 at 10:00
  • 1
    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. Commented Jul 21, 2022 at 17:22

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.