I was looking for solution like this. Actually I made a few changes proposed by Robert Treat:
* SESSION_USER insetead of CURRENT_USER
* Added filter for username and procpid from pg_stat_activity
Alternatively you can create similar function for pg_terminate_backend - it pid's terminates backend connection. I hope this could help someone.
BEGIN;
CREATE FUNCTION my_cancel_backend(integer) RETURNS boolean AS 'SELECT pg_terminate_backend((SELECT procpid FROM pg_stat_activity WHERE usename=SESSION_USER AND procpid=$1));'
LANGUAGE SQL SECURITY DEFINER;
REVOKE EXECUTE ON FUNCTION my_cancel_backend(integer) FROM PUBLIC;
COMMIT;
GRANT EXECUTE ON FUNCTION my_cancel_backend(integer) TO myuser;
--
Jakub Jindra
