azure-docs
azure-docs copied to clipboard
MicrosoftDocs
pgaadauth_create_principal_with_oid service parameter bug
NOTE: This is actually a bug in the pgaadauth extension. I wasn't able to find where to report it, so hopefully you can help get it to the right team.
Desired behavior:
I should be able to create a new AAD-authenticated role using the function described in Create a role using Azure AD object identifier.
Actual behavior
The query fails with the following error:
SQL Error [XX000]: ERROR: incorrect object type provided for principal, given object type: 'cba96244-8555-4d9f-910a-549824af0b0f', actual object type: 'service'
Where: SQL statement "SECURITY LABEL for "pgaadauth" on role "example-identity" is 'aadauth,oid=example-identity-object-id'"
PL/pgSQL function pgaadauth_create_principal_with_oid(text,text,text,boolean,boolean) line 32 at EXECUTE
More information:
The function source contains the following commented section
I was able to successfully create my role by writing out and running the subqueries manually, given that I appended the type explicitly as the commented section would.
create role "example-identity" login;
security label for "pgaadauth"
on role "example-identity-name"
is 'aadauth,oid=example-identity-object-id,type=service';
Therefore I believe that section just needs to be un-commented in the published extension in order to fix this issue.
Document Details
⚠ Do not edit this section. It is required for learn.microsoft.com ➟ GitHub issue linking.
- ID: c454ed0e-944b-2933-ff30-5969ff0de1c9
- Version Independent ID: 6f69997e-b534-e573-154d-7116e189d3c9
- Content: Manage Azure Active Directory Users - Azure Database for PostgreSQL - Flexible Server
- Content Source: articles/postgresql/flexible-server/how-to-manage-azure-ad-users.md
- Service: postgresql
- Sub-service: flexible-server
- GitHub Login: @achudnovskij
- Microsoft Alias: anchudno
@alecglen Thanks for your findings, I'm assigning this to the author for the feedback review.
Hello Alec.
Thank you for reporting the problem. The root cause of this error is SQL function in "pgaadauth" extension definition was out of sync with the underlying C library powering the feature. The problem should be resolved at this time. Please retry. If you still see this problem - Disable/Enable Azure AD authentication should resolve it.
Thanks! Andrey.
On Thu, Dec 15, 2022 at 2:58 PM GeethaThatipatri-MSFT < @.***> wrote:
@alecglen https://github.com/alecglen Thanks for your findings, I'm assigning this to the author for the feedback review.
— Reply to this email directly, view it on GitHub https://github.com/MicrosoftDocs/azure-docs/issues/102814#issuecomment-1353819388, or unsubscribe https://github.com/notifications/unsubscribe-auth/ALCNQG4HHB6O6Y6BWD6QSQTWNOPBXANCNFSM6AAAAAATAH4ATI . You are receiving this because you were assigned.Message ID: @.***>
Hope this is fixed now. Alec- PLease feel free to reopen this PR if you need any other info
