Thread: giving a user permission to kill their processes only

giving a user permission to kill their processes only

From
George Nychis
Date:
Hey all,

So the pg_cancel_backend() function by default is only available to super users, so I decided
to write a wrapper function around, use a SECURITY DEFINER, and GRANT my user privilege to use
the wrapper.

BEGIN;
CREATE FUNCTION kill_process(integer) RETURNS boolean AS 'select pg_cancel_backend($1);'
LANGUAGE SQL SECURITY DEFINER;
REVOKE EXECUTE ON FUNCTION kill_process(integer) FROM PUBLIC;
COMMIT;
GRANT EXECUTE ON FUNCTION kill_process(integer) TO gnychis;

The problem with this is I can now kill other users postgresql processes.  I was wondering if
anyone knows a way in which i can check that the postgres process being killed is running a
query for that user?  Therefore, they can't kill queries in postgres processes started by other
users.

Thanks!
George

Re: giving a user permission to kill their processes only

From
Robert Treat
Date:
On Wednesday 28 February 2007 15:19, George Nychis wrote:
> Hey all,
>
> So the pg_cancel_backend() function by default is only available to super
> users, so I decided to write a wrapper function around, use a SECURITY
> DEFINER, and GRANT my user privilege to use the wrapper.
>
> BEGIN;
> CREATE FUNCTION kill_process(integer) RETURNS boolean AS 'select
> pg_cancel_backend($1);' LANGUAGE SQL SECURITY DEFINER;
> REVOKE EXECUTE ON FUNCTION kill_process(integer) FROM PUBLIC;
> COMMIT;
> GRANT EXECUTE ON FUNCTION kill_process(integer) TO gnychis;
>
> The problem with this is I can now kill other users postgresql processes.
> I was wondering if anyone knows a way in which i can check that the
> postgres process being killed is running a query for that user?  Therefore,
> they can't kill queries in postgres processes started by other users.
>

you could try to match CURRENT_USER with the information in pg_stat_activity,
but be aware there is a reason why this functionality was made for
superusers...
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

Re: giving a user permission to kill their processes only

From
Jakub Jindra
Date:
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