Azure-docs: Please provide documentation on how to set a Service Principal as an AD Administrator

[piotrgwiazda]

Please provide documentation on how to set a Service Principal as an AD Administrator. I want to have this managed via Terraform. I was able to set a Service Principal object ID as an AD Administrator for PostgreSQL.
I was, however, unable to authenticate using the service principal.

I've tried

az login .... using service principal credentials
export PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms|jq '.accessToken' -r)
srv=<my database server name>
psql -h $srv.postgres.database.azure.com -U "<service principal client id>@$srv" postgres --set=sslmode=require

The last step failed with password authentication error.

Is it possible to use the Service Principal at all?

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: ab3066d3-1ac8-3367-2b76-7b3d124cc327
• Version Independent ID: cb862d42-6f01-f034-d4ee-e4695ca97409
• Content: Active Directory authentication - Azure Database for PostgreSQL - Single Server
• Content Source: articles/postgresql/concepts-aad-authentication.md
• Service: postgresql
• GitHub Login: @lfittl
• Microsoft Alias: lufittl

[AnuragSharma-MSFT]

@piotrgwiazda Thank you for the feedback. We are actively investigating and will get back to you soon.

[Mike-Ubezzi-MSFT]

@piotrgwiazda In short, please use Bash Shell instead of PowerShell as I think your password is not being passed correctly in the copy/paste step, if you are copy/paste the returned token string. The Bash Shell steps establish the returned token as a variable and there is no need to copy/paste it to the psql connect string or to establish the variable.

Using Bash Shell in two commands:

# Retrieve the access token

export PGPASSWORD=curl -s 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fossrdbms-aad.database.windows.net&client_id=CLIENT_ID' -H Metadata:true | jq -r .access_token

# Connect to the database

psql -h SERVER --user USER@SERVER DBNAME

Going in to greater detail, there are two specific document that address using a Managed Identity to connect with an Azure Database for PostgreSQL instance: Connect with Managed Identity to Azure Database for PostgreSQL (link) and Use Azure Active Directory for authentication with PostgreSQL (link).
The second document link is applicable to the Azure AD Admin configuration and connecting as the Managed Identity where there is a 3 step process outlined for connecting (See: Connecting to Azure Database for PostgreSQL using Azure AD)

What the document does not detail is that you need to add the Azure AD Group to the tenant first and then follow the steps in the document that outline how to add the Database AD Admin, as you need to search on the AD Group you created previously instead of an individual. (See: Setting the Azure AD Admin user)

If you have done all the above and are simply experiencing the password authentication error, please ensure you are using the Bash Shell and not PowerShell for Step 3 (link). The password is generally not handled well in PowerShell when you copy/paste and has been my experience when I was troubleshooting this procedure previously (issue with handling the SSL flag).

The first document I linked above has a good example: Retrieving the access token from Azure Instance Metadata service (link)

Also consider using the Windows Subsystem for Linux where the Bash environment in Windows 10 is essentially a full Linux working environment.

[piotrgwiazda]

Hi
The scenario where a User Group is set as an AD Admin I have done following the documentation. Thank you.

What I am trying to accomplish is to set a Service Principal as an AD Aministrator instead of a user or User Group.

[Mike-Ubezzi-MSFT]

@piotrgwiazda With the Service Principle approach, when you create an Application Object through App Registration blade in Azure Active Directory (See How to: Use the portal to create an Azure AD application and service principal that can access resources), there is also a resulting Service Principle object created. With this object, you will be able to authenticate with Azure AD.

Next, you need to add this object as a user to the database:

SET aad_validate_oids_in_tenant = off;
CREATE ROLE Service Principle Name WITH LOGIN NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION PASSWORD 'CLIENT_ID' IN ROLE azure_ad_user;
GRANT azure_pg_admin TO Service Principle Name;

Where ClIENT_ID can be retrieved from the _Application Registration_ blade for the previously created registration step:

You should now be able to connect:

# Retrieve the access token
export PGPASSWORD=curl -s 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fossrdbms-aad.database.windows.net&client_id=CLIENT_ID' -H Metadata:true | jq -r .access_token

# Connect to the database
psql -h SERVER --user USER@SERVER DBNAME

Additional Consideration:

Azure Database for PostgreSQL matches access tokens to the Azure Database for PostgreSQL role using the user’s unique Azure AD user ID, as opposed to using the username. This means that if an Azure AD user is deleted in Azure AD and a new user created with the same name, Azure Database for PostgreSQL considers that a different user. Therefore, if a user is deleted from Azure AD and then a new user with the same name added, the new user will not be able to connect with the existing role. To allow that, the Azure Database for PostgreSQL Azure AD admin must revoke and then grant the role “azure_ad_user” to the user to refresh the Azure AD user ID.

Although you are not setting the AD Admin in the Portal, at a database level, you are granting pg_admin access directly. The Azure Portal will only accept one value for the Database instance AD Admin, either a group or individual. Managed Identities and Service Principles do not show up in the search dialog when attempting to add these principle types as the AD Admin via the portal.

[AnuragSharma-MSFT]

@Mike-Ubezzi-MSFT Thanks Mike for the depth of details covered wrt to query.
@piotrgwiazda Please let us know if that solves the issue you have been working with.

[AnuragSharma-MSFT]

@piotrgwiazda We will now proceed to close this thread. If there are further questions regarding this matter, please comment and we will gladly continue the discussion.

[evertonmc]

Are you guys ignoring the question here?

Please provide documentation on how to set a Service Principal as an AD Administrator. I want to have this managed via Terraform. I was able to set a Service Principal object ID as an AD Administrator for PostgreSQL.
I was, however, unable to authenticate using the service principal.

When you create a pg_server via terraform (or az-cli) you can set the SP as AD-Admin, however you'll be unable to use its access token as password to login in pSQL.
Service Principals don't have access to the AD objects by default, which is necessary for the AD Admin to validate ad-objects.
BUT, even setting Directory.Read.All permission to the SP, I'm still getting an error

az login .... using service principal credentials
export PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms|jq '.accessToken' -r)

/usr/local/opt/libpq/bin/psql "host=server.postgres.database.azure.com user=test-pg-sp@server dbname=postgres sslmode=require"
psql: error: could not connect to server: FATAL:  Azure AD access token not valid for role test-pg-sp (does not contain group ID ****-****-****-*******)
HINT:  Did you specify the correct user name and access token?

The question is: is there a way to make the SP work as Postgres AD admin WITHOUT needing a user/group in the first place?
In order to run the commands you recommended here,

CREATE ROLE Service Principle Name WITH LOGIN NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION PASSWORD 'CLIENT_ID' IN ROLE azure_ad_user;
GRANT azure_pg_admin TO Service Principle Name;

the AD admin has to be already set previously, and that must be a user/group.

[evertonmc]

Hi @lfittl-msft, do you have more info about this?