giving a user permission to kill their processes only
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
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