pgAdmin can't cancel query when role field is filled in connection settings

[JulienPoulainBvd]

Hello,

How I reproduce the issue:

• I create a role A (that is allowed to log in the server), which is member of role B (that is not allowed to log in the server)
• I set up a connection to log in to the server, by providing A as username, and B as role
• I connect to "postgres" database using that newly created connection. The database has not been modified since the PostGreSQL instance has been deployed, it has default settings
• I run "select pg_sleep(300)" (launched by A as B), and click on the button to cancel it
• no message saying that the query has been cancelled, but I can see it running (active) when I check the running queries using a super user

This causes problems, for example when a user launches a query that consumes all server resources and is going to run for months. The user then clicks on the cancel button, when they realize their query is wrong, but the button doesn't do its job and the query continues. Later, other users complain about server being too slow and I need to cancel queries by hand or restart the server if there are "too much queries".

I succeeded to reproduce it, using pgAdmin 7.8 on Windows 10, in desktop mode. More details about pgAdmin's version in the below picture.

I did reproduce it on two different versions of PostGreSQL (the second one is an AWS Aurora instance):

• PostgreSQL 16.0, compiled by Visual C++ build 1935, 64-bit
• PostgreSQL 13.12 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (GCC) 9.5.0, 64-bit

When looking at the log file, I see this error which never appears in the interface:
2024-04-30 12:41:32,902: ERROR pgadmin: Failed to execute query (execute_void) for the server #15 - DB:postgres(Query-id: 1579538):
Error Message:must be a member of the role whose query is being canceled or member of pg_signal_backend

I don't know what is wrong here but something is wrong, because starting from the moment I connect to the server with username A, I don't do any role change or whatever, I just launch a query and try to cancel it, all using pgAdmin's interface. This is something that should always work.
Can you help me with this?

Thank you

[pravesh-sharma]

Hi @JulienPoulainBvd,
PostgreSQL raised this error Error Message:must be a member of the role whose query is being canceled or member of pg_signal_backend and not pgAdmin you can also observe the same behaviour with the PSQL tool. If you want to terminate the query like this you need to make role b a member of pg_signal_backend and you will be able to terminate the query.

[JulienPoulainBvd]

Hello,
If I make B member of pg_signal_backend, it will allow A to cancel a query from any other user C, right?

[pravesh-sharma]

Hi @JulienPoulainBvd,

Yes, it will allow A to cancel a query from user C.

[JulienPoulainBvd]

Thanks for the confirmation. Is there anything else that can be done to allow A (as B) to only be able to cancel their own queries? If granting that role is the only way to make people able to cancel their queries, where could I go to ask advises on how to setup my DB?
My goal, explained with two users X and Y but to apply with N users: X and Y are members of B, and are expected to have the same permissions (defined in B). The thing I'm struggling to get: if X creates a table, Y must directly have all accesses as if Y did create the table, without X having to grant any permission or whatever themselve (just executing the table creation should be enough).

[pravesh-sharma]

Hi @JulienPoulainBvd,

You can ask for help related to DB on pgsql-admin@postgresql.org.