Non-Superuser kill own session in PostgreSQL

How to kill long running queries or IDLE queries of a user or another session of user itself.

you can create one function and assign it to public so that they can kill their queries without disturbing to DBA team.

This function can kill their own queries only. They can find pid of other users, but they can’t kill other queries.

create schema testadmin;

create schema testuser;
create user testuser  with password 'StrongPassword';

grant all privileges on schema testuser to testuser;
alter schema testuser owner to testuser;

vi pg_hba.conf
local   postgres        testuser                              scram-sha-256

select pg_reload_conf();

now let’s create walkaround function:

CREATE OR REPLACE FUNCTION testadmin.kill_process(testuserpid integer)
RETURNS boolean AS $body$
DECLARE
    qry boolean;
	procpid int;
BEGIN
    qry := (SELECT pg_catalog.pg_cancel_backend(pid) 
            FROM pg_stat_activity
            WHERE usename=(select session_user)            		
            and pid=testuserpid);
    RETURN qry;
END;
$body$
    LANGUAGE plpgsql
    SECURITY DEFINER
    VOLATILE
    RETURNS NULL ON NULL INPUT;
	
	

GRANT USAGE ON SCHEMA testadmin TO testuser;
GRANT EXECUTE ON FUNCTION testadmin.kill_process(testuserpid integer) TO testuser;

open two different sessions with testuserpid and in one session:

psql -U testuserpid postgres -W

CREATE TABLE users(
  id    SERIAL PRIMARY KEY,
  email VARCHAR(40) NOT NULL UNIQUE
);


INSERT INTO users(email)
SELECT
  'user_' || seq || '@' || (
    CASE (RANDOM() * 2)::INT
      WHEN 0 THEN 'gmail'
      WHEN 1 THEN 'hotmail'
      WHEN 2 THEN 'yahoo'
    END
  ) || '.com' AS email
FROM GENERATE_SERIES(1, 100000000) seq;

on other session with testuser logged in.

SELECT * FROM pg_stat_activity WHERE usename = 'testuser';

select admin.kill_process(3487);