Azure AD Admin unable to run create schema Azure Postgresql - Flexible Server

Question

Azure AD Admin unable to run create schema Azure Postgresql - Flexible Server

Tech1234 10

Hi

I created a new Azure Postgresql - Flexible server and attached Azure Ad admin. then created a new DB on that server. Now when logging through the Azure Ad Admin and running create schema command on the new DB, getting below error

permission denied for database

In the single server, the Azure Ad admin had the admin rights.

Can you please let me know what is missing.

Thanks

Vahid Ghafarpour 23,385 Reputation points Volunteer Moderator

2023-05-23T16:59:59.42+00:00
You need to check ownership and privileges

SELECT datname, datdba, has_database_privilege(datname, 'CREATE') FROM pg_database;
Tech1234 10 Reputation points

2023-05-23T17:12:47.5966667+00:00

Hi @Vahid Ghafarpour

Shouldn't the Ad Admin by default have the permissions. As per MS, Ad Admin has the same permissions as the regular PostgreSQL admin. Same was the case in Single server.

When setting the administrator, a new user is added to Azure Database for PostgreSQL - Flexible Server with full administrator permissions.

2 answers

Your answer

Vahid Ghafarpour 23,385 Reputation points Volunteer Moderator

2023-05-23T16:59:59.42+00:00

You need to check ownership and privileges

SELECT datname, datdba, has_database_privilege(datname, 'CREATE') FROM pg_database;
Tech1234 10 Reputation points

2023-05-23T17:12:47.5966667+00:00

Hi @Vahid Ghafarpour

Shouldn't the Ad Admin by default have the permissions. As per MS, Ad Admin has the same permissions as the regular PostgreSQL admin. Same was the case in Single server.

When setting the administrator, a new user is added to Azure Database for PostgreSQL - Flexible Server with full administrator permissions.

Answer 1

Chris Pollock 21

Is there an update on this?

This should definitely be considered a defect, as the Server admin should have inherit full admin permissions to all newly created databases.

Landry Dubus 0 Reputation points

2024-01-12T15:12:51.9666667+00:00

My understanding is that by default, in postgresql flexible server, only the azuresu role as superuser privileges allowing it to bypass any access control. That role can only be used by Azure as this is a PaaS offer. The default postgres admin user as well as any AAD admin user (if AAD auth is enabled) don't have superuser privileges. They are only part of the azure_pg_admin role that does not have that much privileges and are given additional privileges to create roles and databases. There is absolutly no way from the customer side to add the superuser privilege to any admin role for security reasons.

The consequences of that is that the default postgres admin or AAD admin do not have any permissions to access a database/schema/table/sequence/etc. they do not own because they did not create them directly.

They cannot even read or write data in existing tables of such a database unless you explicitly grant them those permissions by granting the predefined roles like pg_read_all_data or pg_write_all_data or the indivual privileges accordingly.

Additionally, only the database/schema owner can alter or delete objects that it owns.

If you have multiple admin users (postgres or AAD) you should be cautious and connect with the right owner to make schema changes to existing objects.

Answer 2

GeethaThatipatri-MSFT 29,552 Microsoft Employee Moderator

Hi, @Tech1234 Welcome to the Microsoft Q&A forum, Thanks for posting your question.

This is expected behavior, you can take a look below

User's image

If you want to do that, as long as you have the required permissions as Admin, you can assign the role owner of that database to your role, and grant the permission to create a schema to that target user, as in the below example:

User's image

I hope this information helps

Regards

Geetha

GeethaThatipatri-MSFT 29,552 Reputation points Microsoft Employee Moderator

2023-06-01T15:23:58.2766667+00:00

@Tech1234 We haven't heard back from you. Just wanted to check if you had the chance to check my response
Tech1234 10 Reputation points

2024-03-08T15:50:44.41+00:00
@GeethaThatipatri-MSFT As per the MS documentation, there is no difference between the PostgreSQL Administrator and Microsoft Entra Administrator. You when we are creating the new postgresql flexible server and creating new DB why does only PostgreSQL Administrator has the access to complete DB whereas Microsoft Entra Administrator doesnot. This was not the case in Single server. Entra Administrator should also have the same rights as PostgreSQL Administrator as MS documentation also says https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-configure-sign-in-azure-ad-authentication#connect-to-azure-database-for-postgresql-by-using-microsoft-entra-id

Let us know what is missing here

Share via

Azure AD Admin unable to run create schema Azure Postgresql - Flexible Server

2 answers

Your answer