Thread: Questions of the privileges to use the pg_cancel_backend and pg_terminate_backend function. Thanks.

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.

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

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.

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

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

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.

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.