Azure PostgreSQL Database: Cannot GRANT role to my admin user

Question

Azure PostgreSQL Database: Cannot GRANT role to my admin user

Oliver Balun 51

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

Kamil Pakur 26 Reputation points

2022-01-28T14:18:51.917+00:00
In my case the steps to reproduce are:

create PostgreSQL single server on Azure, provide admin user credentials

connect to default database as the admin user

run the following script to create a new database called test3 CREATE DATABASE test3;

connect to database test3 as the admin user and run CREATE ROLE role1;
GRANT role1 TO testuser1;
-- alternatively you may also run REVOKE pg_execute_server_program from role1;

you will get the following error ERROR: only superusers can grant role "pg_execute_server_program"
SQL state: 42501

From what I see there are two users:

azure_superuser that is_super user=true I guess this is an azure provided user

myadmin - the user configured when creating PostgresSql, and this one has is_superuser=false

Accepted answer

4 additional answers

Your answer

Kamil Pakur 26 Reputation points

2022-01-28T14:18:51.917+00:00

In my case the steps to reproduce are:

create PostgreSQL single server on Azure, provide admin user credentials

connect to default database as the admin user

run the following script to create a new database called test3 CREATE DATABASE test3;

connect to database test3 as the admin user and run CREATE ROLE role1;
GRANT role1 TO testuser1;
-- alternatively you may also run REVOKE pg_execute_server_program from role1;

you will get the following error ERROR: only superusers can grant role "pg_execute_server_program"
SQL state: 42501

From what I see there are two users:

azure_superuser that is_super user=true I guess this is an azure provided user

myadmin - the user configured when creating PostgresSql, and this one has is_superuser=false

Answer 1

Szymon Kaczorowski 91

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.

Answer 2

Kamil Pakur 26

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

Regards,
Kamil

Answer 3

Oury Ba-MSFT 20,931 Microsoft Employee Moderator

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

Oury Ba-MSFT 20,931 Reputation points Microsoft Employee Moderator

2022-02-01T17:35:36.57+00:00

Hi @Oliver Balun Checking if the answer provided help resolve your issue. If yes, Please do not forget to mark as accepted answer.

Regards,
Oury
Oury Ba-MSFT 20,931 Reputation points Microsoft Employee Moderator

2022-02-07T22:03:40.427+00:00

Hi @Oliver Balun Checking if the answer provided help resolve your issue. If yes, please do not forget to mark as accepted answer.

Regards,
Oury
Oliver Balun 51 Reputation points

2022-02-15T14:29:42.2+00:00

Apologies for delayed response.
I'm using WestEurope region so it might not be that case. But I think that there were problem through all regions with GRANT privileges during some time period (when I tried to perform these actions and run those queries)
Thank you for your help.

Best regards,
Oliver

Answer 4

Christopher Isidora 31

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

Szymon Kaczorowski 91 Reputation points

2022-01-31T11:03:37.517+00:00

You can also try scaling it up/down down/up as suggested by Microsoft support that I put above.
Christopher Isidora 31 Reputation points

2022-01-31T11:46:33.597+00:00

I reacreated it and it worked. Thanks

Answer 5

Oliver Balun 51

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

Share via

Azure PostgreSQL Database: Cannot GRANT role to my admin user

4 additional answers

Your answer