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?

[soldera]

Hi @Mike-Ubezzi-MSFT, I am experiencing exactly the same problem mentioned by @evertonmc. Would you happen to have extra information?

Thanks

[alex-goncharov]

From the looks of it - it is impossible to use SPN as the AD-Admin; at least now. My guess the logic that does the authentication simply does not check that the user can be an application/spn (judging by UI behaviour).

Apparently API accepts any id and does no check it. But during authorization step it is pretty much if not user, then check groups.

Hard to say without looking at the code, but it looks very close to this

@Mike-Ubezzi-MSFT would you be able to ask product team if this is something they consider doing? This really stops automated database provisioning right now.

[michaelelleby]

A workaround until Microsoft fixes this limitation. This code sample is for Linux shell:

1. Create Azure AD group. Let's call it postgres-admins for example.
2. Set group as AD admin on PostgreSQL server.
3. Add SPN to the group.

Now you can connect to the Azure PostgreSQL server using the SPN with token-based authentication. Remember to login as the SPN before running this code:

export PGPASSWORD=$(az account get-access-token --resource https://ossrdbms-aad.database.windows.net --query accessToken -o tsv)

psql "host=mypostgresserver.postgres.database.azure.com user=postgres-admins@mypostgresserver dbname=postgres sslmode=require"

[tcox-git]

Based on what I have read/done so far, yes I can add SPN to AD admin group and use SPN to access PostgreSQL via token-based authentication. From there, SPN can create database schema and tables and such. However SPN can't create role that's tied to an AD account (exact error I got says: "An unexpected error occurred while trying to validate user.") I guess it's due to "Service Principals don't have access to the AD objects by default,... " that somebody mentioned here/above. Is there any work around for this? We use terraform to provision resources and SPN is used by the Azure pipeline to set up PostgreSQL. Thanks!!

[evertonmc]

I haven't tried that out yet, but maybe you have to set directory permission for the application related to the SP first, kinda 'Directory.ReadAll' or something similar.

[pihai]

I tried giving the service principal which is a member of the AD admin group the Directory.Read.All permission. Still the same error:

CREATE ROLE "<some-AD-role>" WITH LOGIN IN ROLE azure_ad_user;
0LP01: An unexpected error occurred while trying to validate user.

I also tried the approach which is used to create roles for managed identities described here. (Use the ClientID as password. In that case I used the group's ObjectID instead). But that did not work either. It lets me create the role but then the login fails:

Azure AD access token not valid for role <-role-name> User (oid or appid claim does not match <some-guid>)

I could not find a way to let the service principal admin account grant access for other AD users/groups. I'm not sure if that's even possible right now or where the bug/feature request should be reported.

[singhim]

I am also facing the same error, for anyone did this worked ?

[pinkfloydx33]

Having the same issue. An unexpected error occurred while trying to validate user.

SPN or MI is in our AAD Admin Group. We can connect as the identity using the group login and can do any administrative task except assign azure_ad_user to another user/role. I've tried every trick/suggestion listed above.

We are automating our processes and we are leveraging ARM DeploymentScripts to finish bootstrapping our server. The identity executing the scripts is part of the Admin AD group and the hope was that it would be able to finish setting up a few other users/groups tied to AAD. Unfortunately it doesn't work so we need to continue doing this manually.

[nnellanspdl]

According to note on this page, this is not possible.

Service Principal or Managed Identity cannot act as fully functional Azure AD Administrator in Single Server and this limitation is fixed in our Flexible Server

[fardarter]

On flexible server, this works perfectly well for a service principal with an admin username:

az login --service-principal -t <tenant-id> -u <client-id> -p <secret>

export PGPASSWORD=$(az account get-access-token --resource https://ossrdbms-aad.database.windows.net --query accessToken -o tsv)

psql "host=mypostgresserver.postgres.database.azure.com user=<adminusername> dbname=postgres sslmode=require"

With <adminusername> the same as the value from:

select * from pgaadauth_list_principals(false);

or <adminusername> the same name you see in the portal under authentication. There doesn't seem to be a need to use <adminusername>@<server>, at least from my testing.

It would be nice if the docs gave more examples.