0

I'm working with Azure flexible PostgreSQL. I am trying to build out a v16 database and have run into some issues with permissions. I'm an Oracle guy historically, and I'm struggling to understand postgres's permissions. We have two elevated roles on the database, in this case test3 is a MS Entra Admin and the login role is the primary administrator that is created when the server is created.

When I am logged in as the login account, this works just fine:

create role test3 with password 'password1' login createdb createrole;
alter role test3 with replication;
grant test3 to login;
drop role test3;

But this gives me an error:

create role test3 with password 'password1' login createdb createrole replication;
grant test3 to login;
ERROR:  Only roles with the ADMIN option on role "test3" may grant this role.permission denied to grant role "test3" 

ERROR:  permission denied to grant role "test3"
SQL state: 42501
Detail: Only roles with the ADMIN option on role "test3" may grant this role.
drop user test3;

I find it odd I can't grant or even drop a role I created. This seems to only happen with the replication attribute. This also seems to be new with v16, so I assume it has something to do with how the privileges were adjusted with the new version. I'm just not sure why?

This is a problem because the terraform provisioning process creates the test3 account and it appears to be creating it with the replication attribute. I need to be able to grant test3 to the login role to perform a data migration. Why can't I grant a role I created? Why can't I drop a role I created?

3
  • Only somebody who knows Microsoft's setup can answer this. Also, you forgot to add the error message. Feb 8 at 19:41
  • Sorry! You are right, forgot the error. Updated with the message. Also clarified my question. I don't think it is a MS thing. Postgres isn't letting me grant/drop a role I created. Why not?
    – Nick S
    Feb 8 at 20:00
  • I tried your statements on PostgreSQL, and they work as expected. Must be some modification/hack/bug added by Microsoft. Feb 8 at 20:22

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.