pgaadauth_create_principal does not exist in my flexible postgresql instance

Question

I am trying to create users that I will later use to log in from my kubernetes service. I have set up 2 admins for this database 1. being a user group on which I am added and the other being a service principal.

I tried to use the create_principal functions/procedures (with oid or without oid) but they don't seem to exist.

I logged in with my user by opening ssh with azure cli and using a token as a password. I will now try from the service itself but I don't think I will find it connecting that way either.

https://learn.microsoft.com/en-us/azure/developer/java/spring-framework/migrate-postgresql-to-passwordless-connection?tabs=sign-in-azure-cli%2Cjava%2Caks%2Cassign-role-service-connector

Answer 1

Hi, @Armando Cordova Welcome to Microsoft Q&A thanks for posting your question.

I understand that you are trying to create users to log in from your Kubernetes service but are unable to find the create_principal function/procedure in your flexible PostgreSQL instance.

For service principal you can try this or please share the steps you have followed to better assist you.

az ad sp create-for-rbac --name yourAppName --role Contributor --scopes /subscriptions/your_subscriptions.

Regards

Geetha

Answer 2

Ok my problem was that I didn't connect to the right database.

Those functions only exist in the special db "postgres" that exists alongside my database. I wasn't expecting that creatign a user in a different database would affect mine.

The docs do have a bash script with one of the params being postgres and not being a variable but I thought that was simply the name of their sample db. It was easy to miss, better warnings and text regarding this special database to create users would help a lot.

Answer 3

@Armando Cordova I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer.

Issue: You are trying to create users to log in from your Kubernetes service, but you are unable to find the create_principal function/procedure in your flexible PostgreSQL instance. you have also tried logging in with your user and token via SSH with Azure CLI, but still unable to find the function/procedure.

Solution: As you mentioned you were able to resolve the issue by connecting to the correct database.as those functions only exist in the special db "postgres" .

I understand that the documentation could have been clearer about the special postgres database and its role in creating users. Your feedback is valuable, and I will pass it along to the Azure documentation team to improve the documentation.

Please remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution.

Regards

Geetha