Thread: Questions of the privileges to use the pg_cancel_backend and pg_terminate_backend function. Thanks.
Questions of the privileges to use the pg_cancel_backend and pg_terminate_backend function. Thanks.
From
leaf_yxj
Date:
Hi Guys. I got one problem. I need to give some of the non-super users( kind of dba) to get the privileges to can cancel other users's query, DML. After I granted the execute on pg_cancel_backend and pg_terminate_backend function to them, they still get the error message as follows when they call these two function : ERROR : must be superuser to signal other server processes. QUestion : is it possible to make the non superuser to have these two privileges?? Thanks. Regards. Grace -- View this message in context: http://postgresql.1045698.n5.nabble.com/Questions-of-the-privileges-to-use-the-pg-cancel-backend-and-pg-terminate-backend-function-Thanks-tp5618129p5618129.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: Questions of the privileges to use the pg_cancel_backend and pg_terminate_backend function. Thanks.
From
Aaron
Date:
When I need to give other users access to a function that someone must be superuser to execute I write a security definer function. See: http://www.postgresql.org/docs/9.1/static/sql-createfunction.html Also: http://www.ibm.com/developerworks/opensource/library/os-postgresecurity/index.html Using the security definer Think if is like sudo for a db. Aaron Thul http://www.chasingnuts.com On Wed, Apr 4, 2012 at 8:39 AM, leaf_yxj <leaf_yxj@163.com> wrote: > Hi Guys. I got one problem. I need to give some of the non-super users( kind > of dba) to get the privileges > to can cancel other users's query, DML. After I granted the execute on > pg_cancel_backend and pg_terminate_backend function to them, they still get > the error message as follows when they call these two function : > > ERROR : must be superuser to signal other server processes. > > QUestion : is it possible to make the non superuser to have these two > privileges?? > > Thanks. > > Regards. > > Grace > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/Questions-of-the-privileges-to-use-the-pg-cancel-backend-and-pg-terminate-backend-function-Thanks-tp5618129p5618129.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Re: Questions of the privileges to use the pg_cancel_backend and pg_terminate_backend function. Thanks.
From
leaf_yxj
Date:
Hi Aaron: thanks. I tried the security definer. it works well as follows : CREATE FUNCTION kill_process(integer) RETURNS boolean AS 'select pg_cancel_backend($1);' LANGUAGE SQL SECURITY DEFINER; ---- One more question about this function : if non-super user get the execute this function, he/her will have privilege to kill all the processes which belong to the postgresql process. How can we avoid that happing. Thanks. Grace -- View this message in context: http://postgresql.1045698.n5.nabble.com/Questions-of-the-privileges-to-use-the-pg-cancel-backend-and-pg-terminate-backend-function-Thanks-tp5618129p5618473.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: Re: Questions of the privileges to use the pg_cancel_backend and pg_terminate_backend function. Thanks.
From
Merlin Moncure
Date:
On Wed, Apr 4, 2012 at 12:24 PM, leaf_yxj <leaf_yxj@163.com> wrote: > Hi Aaron: thanks. I tried the security definer. it works well as follows : > > CREATE FUNCTION kill_process(integer) RETURNS boolean AS 'select > pg_cancel_backend($1);' LANGUAGE SQL SECURITY DEFINER; > > ---- One more question about this function : if non-super user get the > execute this function, he/her will have privilege to kill all the processes > which belong to the postgresql process. How can we avoid that happing. maybe, inside kill_process, do a quick check against pg_stat_activity and bail if the process doesn't belong to a known usename? merlin
Re: Questions of the privileges to use the pg_cancel_backend and pg_terminate_backend function. Thanks.
From
Jasen Betts
Date:
On 2012-04-04, leaf_yxj <leaf_yxj@163.com> wrote: > Hi Aaron: thanks. I tried the security definer. it works well as follows : > > CREATE FUNCTION kill_process(integer) RETURNS boolean AS 'select > pg_cancel_backend($1);' LANGUAGE SQL SECURITY DEFINER; > > ---- One more question about this function : if non-super user get the > execute this function, he/her will have privilege to kill all the processes > which belong to the postgresql process. How can we avoid that happing. revoke execute on function kill_process(integer) from public; grant execute on function kill_process(integer) to db_admin; assuming db_admin is a role granted to all those who you want to have access, you can instead grans execute to each individual (or some combination of those two) -- ⚂⚃ 100% natural
Re: Questions of the privileges to use the pg_cancel_backend and pg_terminate_backend function. Thanks.
From
leaf_yxj
Date:
Thanks Guys. I wrote this function in a specific schema and granted to the dba users only. Thanks. Problem solved. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Questions-of-the-privileges-to-use-the-pg-cancel-backend-and-pg-terminate-backend-function-Thanks-tp5618129p5627387.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: Questions of the privileges to use the pg_cancel_backend and pg_terminate_backend function. Thanks.
From
josepdba
Date:
I also needed to give the privilege to execute pg_terminate_backend to non-superusers and I made it in a separate schema, too. But, to avoid users killing other user connections I made another function that only gives the option to kill connections made by the same user that's executing the function. I made a post in my blog: http://dbadailystuff.com/2012/05/12/pg_terminate_backend-for-non-superusers/ -- View this message in context: http://postgresql.1045698.n5.nabble.com/Questions-of-the-privileges-to-use-the-pg-cancel-backend-and-pg-terminate-backend-function-Thanks-tp5618129p5709232.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.