Thread: Not able to grant access on pg_signal_backend on azure flexible server
Hi,
We have created an azure postgresql flexible server and we have added an ad admin as a user and Created our database using this admin user.However,When are running this command: 'Grant pg_signal_backend To adminUser' we are getting an error that says 'permission denied to grant role "pg_signal_backend".While this is strange the admin user is infact the owner of the said database and we don't have any other user that have the said privileges.
Saksham Joshi <sakshamjoshi64@gmail.com> writes:
> We have created an azure postgresql flexible server and we have added an ad
> admin as a user and Created our database using this admin user.However,When
> are running this command: 'Grant pg_signal_backend To adminUser' we are
> getting an error that says 'permission denied to grant role
> "pg_signal_backend".While this is strange the admin user is infact the
> owner of the said database and we don't have any other user that have the
> said privileges.
Being the owner of a database isn't an especially privileged thing
in Postgres. In particular, it does not grant you any powers over
installation-wide objects such as roles.
regards, tom lane
On 4/18/24 22:48, Saksham Joshi wrote: > Hi, > We have created an azure postgresql flexible server and we have added an > ad admin as a user and Created our database using this admin > user.However,When are running this command: 'Grant pg_signal_backend To > adminUser' we are getting an error that says 'permission denied to grant > role "pg_signal_backend".While this is strange the admin user is infact > the owner of the said database and we don't have any other user that > have the said privileges. https://www.postgresql.org/docs/current/predefined-roles.html "PostgreSQL provides a set of predefined roles that provide access to certain, commonly needed, privileged capabilities and information. Administrators (including roles that have the CREATEROLE privilege) can GRANT these roles to users and/or other roles in their environment, providing those users with access to the specified capabilities and information." -- Adrian Klaver adrian.klaver@aklaver.com
Hi,
I am afraid that's not the case with postgresql 16 since we also tested with postgresql 15 we are able to run this command( "GRANT pg_signal_backend To "our_admin_user") successfully with our admin user but that's not the case with postgresql 16 we keep getting the error message 'permission denied to grant role "pg_signal_backend".Also,In postgre 16 only the current users of the database are able to close the connections.Can you help on this please?
On Fri, 19 Apr 2024, 19:50 Adrian Klaver, <adrian.klaver@aklaver.com> wrote:
On 4/18/24 22:48, Saksham Joshi wrote:
> Hi,
> We have created an azure postgresql flexible server and we have added an
> ad admin as a user and Created our database using this admin
> user.However,When are running this command: 'Grant pg_signal_backend To
> adminUser' we are getting an error that says 'permission denied to grant
> role "pg_signal_backend".While this is strange the admin user is infact
> the owner of the said database and we don't have any other user that
> have the said privileges.
https://www.postgresql.org/docs/current/predefined-roles.html
"PostgreSQL provides a set of predefined roles that provide access to
certain, commonly needed, privileged capabilities and information.
Administrators (including roles that have the CREATEROLE privilege) can
GRANT these roles to users and/or other roles in their environment,
providing those users with access to the specified capabilities and
information."
--
Adrian Klaver
adrian.klaver@aklaver.com
Re: Not able to grant access on pg_signal_backend on azure flexible server
From
"David G. Johnston"
Date:
On Sunday, April 21, 2024, Saksham Joshi <sakshamjoshi64@gmail.com> wrote:
Hi,I am afraid that's not the case with postgresql 16 since we also tested with postgresql 15 we are able to run this command( "GRANT pg_signal_backend To "our_admin_user") successfully with our admin user but that's not the case with postgresql 16 we keep getting the error message 'permission denied to grant role "pg_signal_backend".
Probably you haven’t granted pg_signal_backend to whichever role you are executing the above grant command with. One cannot grant what one does not have themselves, with the admin option.
David J.
Hi David,
We have tried granting it with our admin user also but no avail.
On Mon, 22 Apr 2024, 10:50 David G. Johnston, <david.g.johnston@gmail.com> wrote:
On Sunday, April 21, 2024, Saksham Joshi <sakshamjoshi64@gmail.com> wrote:Hi,I am afraid that's not the case with postgresql 16 since we also tested with postgresql 15 we are able to run this command( "GRANT pg_signal_backend To "our_admin_user") successfully with our admin user but that's not the case with postgresql 16 we keep getting the error message 'permission denied to grant role "pg_signal_backend".Probably you haven’t granted pg_signal_backend to whichever role you are executing the above grant command with. One cannot grant what one does not have themselves, with the admin option.David J.
Re: Not able to grant access on pg_signal_backend on azure flexible server
From
"David G. Johnston"
Date:
On Sun, Apr 21, 2024 at 10:49 PM Saksham Joshi <sakshamjoshi64@gmail.com> wrote:
We have tried granting it with our admin user also but no avail.
This is how v16+ works in a community installation:
postgres=1 # create role cr createrole;
CREATE ROLE
CREATE ROLE
postgres 2=# set role cr;
SET
postgres 2=> create role otherrole;
CREATE ROLE
postgres 2=> grant pg_signal_backend to otherrole;
ERROR: permission denied to grant role "pg_signal_backend"
DETAIL: Only roles with the ADMIN option on role "pg_signal_backend" may grant this role.
SET
postgres 2=> create role otherrole;
CREATE ROLE
postgres 2=> grant pg_signal_backend to otherrole;
ERROR: permission denied to grant role "pg_signal_backend"
DETAIL: Only roles with the ADMIN option on role "pg_signal_backend" may grant this role.
postgres 1=# grant pg_signal_backend to cr with admin option;
GRANT ROLE
GRANT ROLE
postgres 2=> grant pg_signal_backend to otherrole;
GRANT ROLE
GRANT ROLE
David J.
Re: Not able to grant access on pg_signal_backend on azure flexible server
From
Bagesh Kumar Singh
Date:
No luck.
On Mon, 22 Apr, 2024, 11:42 am David G. Johnston, <david.g.johnston@gmail.com> wrote:
On Sun, Apr 21, 2024 at 10:49 PM Saksham Joshi <sakshamjoshi64@gmail.com> wrote:We have tried granting it with our admin user also but no avail.This is how v16+ works in a community installation:postgres=1 # create role cr createrole;
CREATE ROLEpostgres 2=# set role cr;
SET
postgres 2=> create role otherrole;
CREATE ROLE
postgres 2=> grant pg_signal_backend to otherrole;
ERROR: permission denied to grant role "pg_signal_backend"
DETAIL: Only roles with the ADMIN option on role "pg_signal_backend" may grant this role.postgres 1=# grant pg_signal_backend to cr with admin option;
GRANT ROLEpostgres 2=> grant pg_signal_backend to otherrole;
GRANT ROLEDavid J.