Not able to grant access on pg_signal_backend on azure flexible server

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 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 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

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.

postgres 1=# grant pg_signal_backend to cr with admin option;
GRANT ROLE

postgres 2=> grant pg_signal_backend to otherrole;
GRANT ROLE

David J.

No luck.

On Mon, 22 Apr, 2024, 11:42 am David G. Johnston, <
david.g.johnston@gmail.com> wrote: