Pivotal Knowledge Base

Follow

How to cancel other process queries by a user that is not a superuser

Goal

Officially Pivotal Greenplum (GPDB) doesnt allow a different user (other than superuser) to terminate other user/process queries, if you try to kill other process queries you would be treated with the error message

ERROR:  must be superuser to signal other server processes

This limitation might be a concern for some users as they would need to contact their admin to do the task and admin doesnt want to share super user rights. In this guide we would look at a workaround to do the same.

Disclaimer

The function/solution that is provided below is not a offical solution to the problem, so the workaround would need to be tested on your test cluster and verified, also no official support would be provided if there is any issue to the script or anything that arise from it.

Solution

Build the below function on gpadmin user or in any other username that has superuser privilege and grant the permission to the user (non-superuser) that intend to do the admin jobs.

-- The below function allow the user to terminate any query/session in the database.

CREATE SCHEMA admin;

CREATE OR REPLACE FUNCTION admin.kill_process(pid integer)
RETURNS boolean AS $body$
DECLARE
    qry boolean;
BEGIN
    qry := (select pg_catalog.pg_cancel_backend(pid));
    RETURN qry;
END;
$body$
    LANGUAGE plpgsql
    SECURITY DEFINER
    VOLATILE
    RETURNS NULL ON NULL INPUT;


GRANT USAGE ON SCHEMA admin TO <user-name>;
GRANT EXECUTE ON FUNCTION admin.kill_process(pid integer) TO <user-name>;

-- Use the function below , if the non-superuser is only permitted to terminate the query/session that has been started with its username.

CREATE SCHEMA admin;

CREATE OR REPLACE FUNCTION admin.kill_process(pid integer)
RETURNS boolean AS $body$
DECLARE
    qry boolean;
BEGIN
    qry := (SELECT pg_catalog.pg_cancel_backend(procpid) 
FROM pg_stat_activity
WHERE usename=(select session_user)
AND procpid=pid); RETURN qry; END; $body$ LANGUAGE plpgsql SECURITY DEFINER VOLATILE RETURNS NULL ON NULL INPUT; GRANT USAGE ON SCHEMA admin TO <user-name>; GRANT EXECUTE ON FUNCTION admin.kill_process(pid integer) TO <user-name>;

Example

Putting the above mentioned function into practice.

-- Create two users and provide them access to the database.

gpadmin=# create user a1 password 'a1';
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE
Time: 56.142 ms
gpadmin=# create user a2 password 'a2';
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE
Time: 57.652 ms
gpadmin=#
gpadmin:Fullrack@mdw $ cd $MASTER_DATA_DIRECTORY
gpadmin:Fullrack@mdw $ tail -2 pg_hba.conf
local    gpadmin        a1         md5
local    gpadmin        a2         md5
gpadmin:Fullrack@mdw $ gpstop -u
20140926:05:25:09:009006 gpstop:mdw:gpadmin-[INFO]:-Starting gpstop with args: -u
20140926:05:25:09:009006 gpstop:mdw:gpadmin-[INFO]:-Gathering information and validating the environment...
20140926:05:25:09:009006 gpstop:mdw:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20140926:05:25:09:009006 gpstop:mdw:gpadmin-[INFO]:-Obtaining Segment details from master...
20140926:05:25:09:009006 gpstop:mdw:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 4.3.1.0 build 6'
20140926:05:25:09:009006 gpstop:mdw:gpadmin-[INFO]:-Signalling all postmaster processes to reload
.
gpadmin:Fullrack@mdw $

-- As gpadmin (superuser) , create the function and provide the rights on the function to the user that would be doing the admin task.

gpadmin=# Create schema admin;
CREATE SCHEMA
Time: 365.222 ms
gpadmin=# CREATE OR REPLACE FUNCTION admin.kill_process(pid integer)
gpadmin-# RETURNS boolean AS $body$
gpadmin$# DECLARE
gpadmin$#     qry boolean;
gpadmin$# BEGIN
gpadmin$#     qry := (select pg_catalog.pg_cancel_backend(pid));
gpadmin$#     RETURN qry;
gpadmin$# END;
gpadmin$# $body$
gpadmin-#     LANGUAGE plpgsql
gpadmin-#     SECURITY DEFINER
gpadmin-#     VOLATILE
gpadmin-#     RETURNS NULL ON NULL INPUT;
CREATE FUNCTION
Time: 336.945 ms
gpadmin=# GRANT USAGE ON SCHEMA admin TO a2;
GRANT
Time: 378.672 ms
gpadmin=# GRANT EXECUTE ON FUNCTION admin.kill_process(pid integer) TO a2;
GRANT
Time: 323.731 ms
gpadmin=# \q

-- As user "a1" , run a bad query.

gpadmin:Fullrack@mdw $ psql -U a1
Password for user a1:
Timing is on.
Pager usage is off.
psql (8.2.15)
Type "help" for help.
gpadmin=> create table test1 ( a int , b int ); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. CREATE TABLE Time: 173.353 ms gpadmin=> insert into test1 values (generate_series(1,10000000000),generate_series(1,10000000000));

-- As user "a2" , kill the process "a1" query

gpadmin:Fullrack@mdw $ psql -U a2
Password for user a2:
Timing is on.
Pager usage is off.
psql (8.2.15)
Type "help" for help.

gpadmin=> select * from pg_stat_activity ;
 datid | datname | procpid | sess_id | usesysid | usename |                                       current_query                                       | waiting |          query_start          |         backend_start         | client_addr | client_port | application_name |          xact_start           | waiting_reason
-------+---------+---------+---------+----------+---------+-------------------------------------------------------------------------------------------+---------+-------------------------------+-------------------------------+-------------+-------------+------------------+-------------------------------+----------------
 19175 | gpadmin |   13473 |      12 |    20262 | a2      | insert into test1 values (generate_series(1,10000000000),generate_series(1,10000000000)); | f       | 2014-09-26 05:32:26.928082-04 | 2014-09-26 05:31:02.122222-04 |             |          -1 | psql             | 2014-09-26 05:32:26.928082-04 |
 19175 | gpadmin |    7623 |      15 |    20262 | a2      | select * from pg_stat_activity ;                                                          | f       | 2014-09-26 05:34:01.032379-04 | 2014-09-26 05:33:39.315819-04 |             |          -1 | psql             | 2014-09-26 05:34:01.032379-04 |
(2 rows)

Time: 4.632 ms

gpadmin=> select pg_cancel_backend(13473);
ERROR:  must be superuser to signal other server processes
gpadmin=>

gpadmin=> select admin.kill_process(13473);
 kill_process
--------------
 t
(1 row)

Time: 3.256 ms

-- On user "a1" login screen.

gpadmin=> insert into test1 values (generate_series(1,10000000000),generate_series(1,10000000000));
ERROR:  canceling statement due to user request

Comments

  • Avatar
    Ayub M

    Faisal, with this approach the user having execute grant will be able to cancel any session which is not good. Wouldn't it be better for the the function to check the current user and the user name of the session he is trying to terminate. If they match then only should the session be terminated. This way one would have access to terminate only one's sessions.

  • Avatar
    Faisal Ali

    Thanks Ayub M for the feedback, Very much appreciated.

    Officially Greenplum do not allow non-superuser to terminate any other users query ( this is for security reasons ) , and we have multiple request where in a development environment this means you will need to ping up the DBA often to terminate the bad set of queries and this was a workaround to overcome the problem i.e to grants right to the trusted users. I would normally wont recommend this workaround being used on a production database, but for Test / development / QA environment this should be fine.

    That being said , i understand your concern and i have slightly modified the query (below) which might help in achieving what you are looking for , hope that helps.

    CREATE OR REPLACE FUNCTION admin.kill_process(pid integer)
    RETURNS boolean AS $body$
    DECLARE
        qry boolean;
    BEGIN
        qry := (select pg_catalog.pg_cancel_backend(procpid) from pg_stat_activity where usename=(select session_user) and procpid=pid);
        RETURN qry;
    END;
    $body$
        LANGUAGE plpgsql
        SECURITY DEFINER
        VOLATILE
        RETURNS NULL ON NULL INPUT;
    

    I have updated the article with the same info, so that it might be helpful for others as well.

    Thanks Again.

Powered by Zendesk