How to automate Entra user management in flexible server?

[marcindulak]

Here is an example list of steps in order to add an Entra group with a database schema select grant. While some are achievable using tools like terraform or bicep, it feels like Azure could make this process more accessible and automated. Additionally, the existence of functions like pgaadauth_create_principal only in the cloud, makes the automation testing more difficult.

1. In Azure Portal, Azure Database for PostgreSQL flexible server | Databases create test database
2. In Azure Portal, Azure Database for PostgreSQL flexible server | Authentication | Add Microsoft Entra Admins create MyAdAdmin user, using an existing MyAdAdmin AAD group
3. Connect to the postgres database using MyAdAdmin (due to https://github.com/MicrosoftDocs/azure-docs/issues/102693#issuecomment-1798118261)

   POSTGRES_USER=MyAdAdmin
   export PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms --query "[accessToken]" -o tsv)
   PGSSLMODE=require psql -h $POSTGRES_HOST --port=$POSTGRES_PORT --username=$POSTGRES_USER postgres

   and create a MyAdReader user, using an existing MyAdReader AAD group

   SELECT * FROM pgaadauth_create_principal('MyAdReader', false, false);
              pgaadauth_create_principal             
   ---------------------------------------------------
   Created role for "MyAdReader"
   (1 row)
   SELECT * FROM pgaadauth_list_principals(false);
     rolname  | principaltype |               objectid               |               tenantid               | ismfa | isadmin 
   ------------------------------------+---------------+--------------------------------------+--------------------------------------+-------+---------
   MyAdAdmin  | group         | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx1 | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx |     0 |       1
   MyAdReader | group         | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx2 | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx |     0 |       0
   (2 rows)  

4. As the regular database Admin (not MyAdAdmin) connect to test database

   POSTGRES_USER=Admin
   PGSSLMODE=require psql -h $POSTGRES_HOST --port=$POSTGRES_PORT --username=$POSTGRES_USER test

   and create a schema

   CREATE SCHEMA "myschema";
   CREATE TABLE "myschema"."mytable" (mycolumn text);
   INSERT INTO "myschema"."mytable" (mycolumn) VALUES ('Hello, world!');

   and add grants to MyAdReader

   GRANT USAGE ON SCHEMA "myschema" TO "MyAdReader";
   GRANT SELECT ON ALL TABLES IN SCHEMA "myschema" TO "MyAdReader";

5. As MyAdReader connect to test database

   POSTGRES_USER=MyAdReader
   export PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms --query "[accessToken]" -o tsv)
   PGSSLMODE=require psql -h $POSTGRES_HOST --port=$POSTGRES_PORT --username=$POSTGRES_USER test

   and verify the grant

   SELECT * FROM "myschema"."mytable";
     mycolumn    
   ---------------
   Hello, world!
   (1 row)