The problem

Last week I had to give the permission to one user to kill PostgreSQL connections in a development environment, but I found that pg_terminate_backend and pg_cancel_backend functions can only be executed by a superuser and I didn’t want to give such privileges so I started looking for a solution and at the end I implemented two functions:

• pg_kill_connection: kills any user connection.
• pg_kill_user_connection: kills only connections owned by the same user.

The research

There’s a really good post in pgsql-hackers mailing list called pg_terminate_backend and pg_cancel_backend by not administrator user where there’s a discussion if PostgreSQL should add the option to enable non-superuser to kill their own processes. In PostgreSQL 9.2 release draft appears this change “Allow users to cancel queries in user-matching sessions using pg_cancel_backend()“.

In this other forum, there’s a solution to how to allow non-superusers to kill a process. It’s quite straightforward, you only have to wrap the function in another one and execute it with SECURITY DEFINER, so the function is executed with the permissions of who has created the function (must be a superuser to work, of course):

CREATE OR REPLACE FUNCTION pg_kill_connection(integer) 
    RETURNS boolean AS 'select pg_terminate_backend($1);' 
    LANGUAGE SQL SECURITY DEFINER;

The solution

I like to have the administrative tools in a different schema:

CREATE SCHEMA dbo;


I modified the function schema, syntax and add some security options, without modifying the function behaviour and added the other function to kill only connections made by the same user. Remember to execute it as superuser:

BEGIN TRANSACTION;

-- kills any postgresql connection (using its PID)
CREATE OR REPLACE FUNCTION dbo.pg_kill_connection(pid integer)
RETURNS boolean AS $body$
DECLARE
    result boolean;
BEGIN
    result := (select pg_catalog.pg_terminate_backend(pid));
    RETURN result;
END;
$body$
    LANGUAGE plpgsql
    SECURITY DEFINER
    VOLATILE
    RETURNS NULL ON NULL INPUT
    SET search_path = pg_catalog;

-- Only is able to kill PIDs owned by the same user. The user cannot kill process that don't own
CREATE OR REPLACE FUNCTION dbo.pg_kill_user_process(pid integer)
RETURNS boolean AS
$body$
DECLARE
    result boolean;
BEGIN
    IF EXISTS (SELECT * FROM pg_catalog.pg_stat_activity
        WHERE usename IN (SELECT usename FROM pg_catalog.pg_stat_activity WHERE procpid = pg_backend_pid()) AND procpid = pid) THEN
            result := (SELECT pg_catalog.pg_terminate_backend(pid));
    ELSE
        result := false;
    END IF;
    RETURN result;
END;
$body$
    LANGUAGE plpgsql
    SECURITY DEFINER
    VOLATILE
    RETURNS NULL ON NULL INPUT
    SET search_path = pg_catalog;

REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA dbo FROM PUBLIC;

COMMIT TRANSACTION;

The sentences to execute the functions:

SELECT dbo.pg_kill_connection(4352);
SELECT dbo.pg_kill_user_connection(4352);

It’s important to specify a search_path to avoid that someone override the function, specially because we’re using SECURITY DEFINER, as PostgreSQL documentation says:

Because a SECURITY DEFINER function is executed with the privileges of the user that created it, care is needed to ensure that the function cannot be misused. For security, search_path should be set to exclude any schemas writable by untrusted users. This prevents malicious users from creating objects that mask objects used by the function. Particularly important in this regard is the temporary-table schema, which is searched first by default, and is normally writable by anyone. A secure arrangement can be had by forcing the temporary schema to be searched last.

For this reason we make explicit the schema we’re using with SET search_path option. A larger explanation about search_path can be found here . Another change done is to add RETURNS NULL ON NULL INPUT to avoid null inputs.

Giving give execution permissions

The reason to write both function in a transaction with a REVOKE EXECUTE ON ALL FUNCTIONS is because if other users have access to dbo schema (GRANT USAGE), they will be able to execute them, so after creating be sure you REVOKE the permissions to PUBLIC. PostgreSQL documentation is very clear on this:

Another point to keep in mind is that by default, execute privilege is granted to PUBLIC for newly created functions (see GRANT for more information). Frequently you will wish to restrict use of a security definer function to only some users. To do that, you must revoke the default PUBLIC privileges and then grant execute privilege selectively. To avoid having a window where the new function is accessible to all, create it and set the privileges within a single transaction.

In our example we could have created the functions without a transaction because no user had access to the schema by default. If Bob tries to execute it, he will get this error:

ERROR: permission denied for schema dbo
SQL state: 42501

So we give the GRANT USAGE to Bob for the schema and the grants to execute the functions:

GRANT USAGE ON SCHEMA dbo TO bob;
GRANT EXECUTE ON FUNCTION dbo.pg_kill_connection(pid integer) TO bob;
GRANT EXECUTE ON FUNCTION dbo.pg_kill_user_connection(pid integer) TO bob;

Here ends my very first post in this blog! Please forgive me for any syntax and grammatical errors.