Azure PostgreSQL Database: Cannot GRANT role to my admin user

Oliver Balun 51 Reputation points
2022-01-27T17:27:31.527+00:00

Hello,
I'd like to ask you about resource Azure Database for PostgreSQL server please.
I'm trying to create new user/role on my PostgreSQL database and then grant this role to my admin user. I have DEV environment that is created only when needed and this scenario successfully worked last 14 months. Everytime, there were created new database role (for my application db schema) and this role was granted to my admin user so it could work properly and had correct permissions.
However, this Monday Azure PostgreSQL started to answer with this error message to my db migration:

Migrating schema "<application-schema-name>" to version "<version>" - create users
ERROR: Migration of schema "<application-schema-name>" to version "<version>" - create users failed! Changes successfully rolled back.
ERROR:
Migration V1__create_users.sql failed
--------------------------------------------------
SQL State : 42501
Error Code : 0
Message : ERROR: **only superusers can grant role "pg_execute_server_program"**
Location : /flyway/sql/01_schema/V1__create_users.sql (/flyway/sql/01_schema/V1__create_users.sql)
Line : 5
Statement : grant "<application-schema-name>" to "<my-admin-user>"

When I try to execute this query manually on older Azure PostgreSQL databases that I have created last year, it runs successfully and there in no error at all - user is created, role is granted.
I understand that I'm not able to run queries as superuser but I never tried to. There wasn't any problem with this query last 14 months so I'm quite confused right now because my database cannot be created in desired way. Is there anything that might change in Azure PostgreSQL server resource that might be reason of this error that I'm experiencing?
Thank you for your answers.
Oliver

Azure Database for PostgreSQL
{count} votes

Accepted answer
  1. Szymon Kaczorowski 91 Reputation points
    2022-01-31T10:31:00.033+00:00

    I got following answer from Microsoft support:

    The fix was rolled out and the issue should now be fixed. Could you please try again?

    If the error persists, please scale up the server and then back down again, or down and up, and try again.

    For me recreating Postgress resources worked, haven't tried scaling up and down though.

    2 people found this answer helpful.
    0 comments No comments

4 additional answers

Sort by: Most helpful
  1. Kamil Pakur 26 Reputation points
    2022-01-31T07:20:28.077+00:00

    Hi, for me the region is UK South and there was the 11 minor version upgrade last week.

    Regards,
    Kamil

    1 person found this answer helpful.
    0 comments No comments

  2. Oury Ba-MSFT 20,931 Reputation points Microsoft Employee Moderator
    2022-01-28T20:38:15.483+00:00

    Hi @Oliver Balun Thank you for posting your question on Microsoft Q&A and for using Azure services.

    Have you recently upgraded your Azure PostgreSQL from a different version.?
    Our product group are rolling out a fix to address the GRANT issue and expect to complete in the next week or so. If you are using East US, East US 2, or West US 2 you may want to try creating new server and retry the operation.

    Please let me know if that is helpful.

    Regards,
    Oury


  3. Christopher Isidora 31 Reputation points
    2022-01-31T11:00:33.283+00:00

    I'm also experiencing the issue, the Postgesql was created last Thrusday. Should I recreate it?


  4. Oliver Balun 51 Reputation points
    2022-02-15T14:24:46.37+00:00

    Apologies for delayed response.
    Thank you all for your answers.
    If there were some minor upgrades, changes and fixes, as you said, I think that it might have caused that problem I was experiencing. I waited with the re-creation for a while. If I recreate the PostgreSQL server and database now, it works as i expect. So re-creation helps.
    Thank you all once again

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.