Postgres Azure AD admin privileges fails on GRANT

Question

I've set up my Postgres flexible server in Azure to use Azure AD admins and added a service principal as the admin (nevermind the added user in the screenshot, that was just an experiment):

Now, I want the ServicePrincipal AD admin to be able to do the following via database migrations in our .NET services (using DbUp):

1. Create new roles and
2. GRANT permissions to the created roles

When I ran migrations to create the roles and the GRANTs, I got an error telling me that the ServicePrincipal doesn't have permissions to access public.schemaversions Very well, then I wanted to start by granting the ServicePrincipal itself the privileges to access public.schemaversions, like so:

ALTER SCHEMA public OWNER TO <service principal name>;
GRANT USAGE ON SCHEMA public TO <service principal name>;
GRANT SELECT, INSERT ON public.schemaversions TO <service principal name>;

psql "sslmode=require host=<server> port=<port> dbname=MyDatabase" -U <service principal name> -w

Then I get the following error:

ERROR: permission denied for database MyDatabase

GRANT: ERROR: permission denied for table schemaversions

Is there something I've missed here? Can't the Azure AD admin run GRANT statements? It only seems like it can connect to the server and create roles.

Answer 1

When you add AAD admin user to Azure Database for Postgresql Flexible server all it does is add a user to the AAD Admin group.

You still need to grant this group access to your resources in order for them to have access. In this case you still need to grant the Role access to your Database first and foremost.

If done manually it would go somewhat like this:

CREATE ROLE "AAD-Access-Role-Name" WITH LOGIN IN ROLE azure_ad_user;
GRANT ALL PRIVILEGES ON DATABASE MyDatabaseName TO "AAD-Access-Role-Name";

This has been greatly simplified in the Flexible server with a custom functions. This allows you to create a central Role with access and all the necessary grants, and then once you create a user or role in this fashion, you can easily simply add users to this Role.

GRANT "AAD-Access-Role-Name" TO MyNewUser ;

You can see a list of examples here: https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-manage-azure-ad-users#create-a-role-using-azure-ad-object-identifier

Such as:

select * from pgaadauth_list_principals(true);
select * from pgaadauth_create_principal('ServicePrincipalName', true, false);