"An unexpected error occurred while trying to validate user" while creating role in PostgreSQL Single Server with a service account

Frédéric Poliquin 0

I'm trying to write a batch that will synchronize Azure Postgresql users with an external source. The batch fails when calling:

create role "${username}" with login in role azure_ad_user

The error is :

An unexpected error occurred while trying to validate user.

Everything works if I use my own personal account. It only fails when I try to use a service account.

The service account is a member of the AAD Admin group and has been granted the "Directory.Read.All" permission.

The service account is able to drop users so it clearly has admin rights. I can confirm that the token contains the object id of the AAD Admin group.

I've tried with :

SET aad_validate_oids_in_tenant = off;

But it doesn't work.

Any help is appreciated !

Thank you

GeethaThatipatri-MSFT 29,597 Reputation points Microsoft Employee Moderator

2023-09-25T15:17:59.5266667+00:00

Hi,@Frédéric Poliquin Thanks for posting your question in the Microsoft Q&A forum.

If I understand correctly you are trying to add AAD User after connecting to Postgres as AAD Admin. The created role succeeds but the created AAD user still cannot login ? can you please clarify?

Can you try creating a new service principal?

Regards

Geetha
Frédéric Poliquin 0 Reputation points

2023-09-28T18:47:30.0633333+00:00

Hello, not exactly. It is the create role statement that fails. I'm just unable to create the user. I've been able to make it work on a flexible server v15 with the same service principal. The only difference is that I use

select * from pgaadauth_create_principal('${username}', false, false);

Instead of the create role statement.

I still get the save error on a single server v11.

Your answer