Relation between roles and pg_terminate_backend and/or pg_cancel_backend admin functions in PostgreSQL

Question

Consider the following example:

A superuser admin executes the following commands in a PostgreSQL database:

CREATE DATABASE admindb;
CREATE ROLE dbo WITH CREATEDB CREATEROLE;
GRANT ALL PRIVILEGES ON DATABASE admindb TO dbo WITH GRANT OPTION;
CREATE ROLE user1 WITH LOGIN PASSWORD 'user1pw';
GRANT dbo TO user1;
ALTER ROLE user1 SET ROLE dbo;

In the next step, user1 does the following:

psql -h 10.11.4.32 -d admindb -U user1
# creates a table in admindb database
CREATE TABLE test1 ( a INT, b INT);
# check pg_stat_activity

 datid | datname |  pid  | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query
 17816 | admindb | 21314 |    17819 | user12  | psql             |             |                 |             |               |            |             |              |                 |            |       |             |          625 |<insufficient privilege>
(1 row)

Please see that query field shows insufficient privilege.

If I don't run ALTER ROLE user1 SET ROLE dbo while creating user1, the query field displays it correctly as given below.

 17816 | admindb | 18386 |    17819 | user12  | psql             | 192.168.0.12 |                 |       58794 | 2018-08-10 06:13:48.762903+00 | 2018-08-10 06:14:57.119916+00 | 2018-08-10 06:14:57.119916+00 | 2018-08-10 06:14:57.119917+00 |                 |            | active |             |          624 | select * from pg_stat_activity;
(1 row)

I would like to understand what causes this behavior.

As a consequence of this, if user1 runs a long query and tries to cancel it by connecting using psql to admindb as user1 and running select pg_cancel_backend(pid), it fails with the error.

ERROR:  must be a member of the role whose query is being canceled or member of pg_signal_backend

If I remove ALTER ROLE user1 SET ROLE dbo, then user1 is able to cancel the query.

Answer 1

The reason is that the SET ROLE changes your active user context to dbo right after you connect, but your “session user” still is user1:

SELECT current_user, session_user;

 current_user | session_user 
--------------+--------------
 dbo          | user1
(1 row)

In pg_stat_activity you can only see the sessions whose session user equals your currently active user, and pg_cancel_backend and pg_terminate_backend allow you only to affect backends whose session user equals your current user.

You can execute RESET ROLE to revert to user1 and perform these operations.