Minor version upgrade of managed postgres does not allow grants

Question

We are having an issue after what seems to be a recent upgrade from postgresql 11.11 to 11.12. We create a new non-superuser role and wish to create a new database owned by this role. We wish to grant this role to the admin account created during deployment. This was working fine up to yesterday, but suddenly this started failing for our new test deployments.
An example of the type of sql we are running:
-- we are logged in using the account created during deploy of the managed postgres environment
-- we create a non-superuser role
CREATE ROLE 'foobar' WITH
LOGIN
NOSUPERUSER
NOCREATEDB
CREATEROLE
INHERIT
NOREPLICATION
PASSWORD 'test123';

-- and wish to grant that role to the admin user created during ARM deploy
-- after that we want to create a new database within our environment owned by this role
-- however this grant fails with: SQL Error [42501]: ERROR: only superusers can grant role 'pg_execute_server_program'
GRANT 'foobar' TO current_user;

CREATE DATABASE foobardb
WITH
OWNER = foobar
ENCODING = 'UTF8'
CONNECTION LIMIT = -1;
ALTER DATABASE foobardb SET search_path = public, extensions;

We have opened a support ticket for this as well.

Answer 1

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

Our product group is 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 us know if that helps

Please do not forget to mark as accepted answer if the reply was helpful

Regards,
Oury

Answer 2

Hi,

We are having the same issue. In fact, on some of our environments deployed months ago this used to work and now we can't do it anymore.

Our case is slightly different, since we are creating a schema with ownership granted to another role (foobar) on a database owned by the admin created during ARM deploy.
Nevertheless the error is the same when we try to grant foobar role to admin as required to be the owner of the new schema: SQL Error [42501]: ERROR: only superusers can grant role 'pg_execute_server_program'
GRANT 'foobar' TO admin;

We have opened a support ticket as well explaining this issue.

Answer 3

Same for me just creating a single server.
And try the following give the error :

CREATE ROLE role3 WITH
NOLOGIN
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
INHERIT
NOREPLICATION
CONNECTION LIMIT -1;

CREATE ROLE user3 WITH
LOGIN
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
INHERIT
NOREPLICATION
CONNECTION LIMIT -1;

GRANT role3 TO user3;

Ticket open wth MS

Answer 4

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 5

Thanks for the updates. We are going to test this.