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)
   

[jack1902]

Going to drop this here: https://stvdilln.medium.com/using-federated-identities-in-azure-aks-a440feb4a1ce

I found it very helpful for a use case whereby:

• Workload is running inside AKS
• Workload has a WorkloadIdentity along with the Federated Credential
• Workload can then gain access to the database based on the WorkloadIdentity -> FederatedCredential -> AccessToken

I found this very confusing to get working compared to AWS Postgres IAM Authentication which provides plugins to make things easier. Alot of the documentation mentions how to use WorkloadIdentities but not in the sense of coming from AKS

[marcindulak]

In the above post, the important part, the setup of postgres users/roles and their grants is not automated in the way I'm looking for, but relies on executing sql.

You need to run the pgaaadauth_create_principal on the ‘postgres’ database of the server.

Secondly, you need to run the ‘create table’ and ‘Grant Insert’ on the test-db database.