Re: [GENERAL] giving a user permission to kill their processes only

[Jakub Jindra]

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
http://www.slusnyweb.cz

Re: [GENERAL] giving a user permission to kill their processes only

[Robert Treat]

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

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] giving a user permission to kill their processes only

[George Nychis]

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

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq