How to automate Entra user management in flexible server?
Opened this issue · 0 comments
marcindulak commented
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.
- In Azure Portal,
Azure Database for PostgreSQL flexible server | Databases
createtest
database - In Azure Portal,
Azure Database for PostgreSQL flexible server | Authentication | Add Microsoft Entra Admins
createMyAdAdmin
user, using an existingMyAdAdmin
AAD group - Connect to the
postgres
database usingMyAdAdmin
(due to MicrosoftDocs/azure-docs#102693 (comment))and create aPOSTGRES_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
MyAdReader
user, using an existingMyAdReader
AAD groupSELECT * 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)
- As the regular database
Admin
(notMyAdAdmin
) connect totest
databaseand create a schemaPOSTGRES_USER=Admin PGSSLMODE=require psql -h $POSTGRES_HOST --port=$POSTGRES_PORT --username=$POSTGRES_USER test
and add grants toCREATE SCHEMA "myschema"; CREATE TABLE "myschema"."mytable" (mycolumn text); INSERT INTO "myschema"."mytable" (mycolumn) VALUES ('Hello, world!');
MyAdReader
GRANT USAGE ON SCHEMA "myschema" TO "MyAdReader"; GRANT SELECT ON ALL TABLES IN SCHEMA "myschema" TO "MyAdReader";
- As
MyAdReader
connect totest
databaseand verify the grantPOSTGRES_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
SELECT * FROM "myschema"."mytable"; mycolumn --------------- Hello, world! (1 row)