CODEWITHSUNDEEP
postgresqlazurerolespostgresql-14azure-postgresql
Tomer Stein
Tomer Stein

Reputation: 11

Azure PostgreSQL - How to create admin user

We use Azure Database for PostgreSQL flexible servers. My requirement is creating admin user that should be able to anything (DDL/Alter etc.) with any table in any schema throughout the database, irrespective of which user has created that table, function, procedure or sequence etc.

I tried creating admin user according to Azure Docs:

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.

CREATE USER <new_user> CREATEDB CREATEROLE PASSWORD '<StrongPassword!>';

GRANT azure_pg_admin TO <new_user>;

I created a user called test_admin and granted it the azure_pg_admin role as the docs said. With another regular user, I logged in and created a table and the test_admin couldn't query it, and got this error:

permission denied for table

even though the test_admin have the azure_pg_admin.

Can anyone help me with this ?

Upvotes: 1

Views: 3756

Answers (1)

Pratik Lad
Pratik Lad

Reputation: 8402

As per MS Document

The admin user is the highest privilege user you have on the server. It belongs to the role azure_pg_admin. This role does not have full superuser permissions.

AFAIK, it doesn't have all the permissions required to conduct all operations on all tables in all schemas are not available to the azure_pg_admin role. To query an any table in every schema or aver database you need to provide it explicitly. It is a built-in role in Azure Database for PostgreSQL that provides administrative privileges to manage the server.

psql commands to grant permissions:

--grant permission on particular database level permissions.
GRANT ALL PRIVILEGES ON DATABASE DB_name TO User_name;

--grant permission on all tables of schema.
GRANT SELECT ON ALL TABLES IN SCHEMA Schema_name TO User_name;

Upvotes: 1

Related Questions