非超级用户执行pg_terminate_backend

1.缘起

最近我的用户请求在开发环境中kill session的权限，但是pg_terminate_backend与pg_cancel_backend只能在超级用户权限下执行，但我又不想将超级用户权限授予他们，在网络上找到一些文章实现以下两个功能，总结一下：

• pg_kill_connection：终止任何用户连接。
• pg_kill_user_connection：仅kill同一用户拥有的连接。

2.解决方案

方法非常简单，只需将该函数包装在另一个函数中并使用 SECURITY DEFINER 执行它，因此该函数将以创建该函数的用户的权限执行（当然必须是超级用户）：

2.1 创建管理schema

将一些管理工具放在专门的schema是一个好习惯：

CREATE SCHEMA dbo;

2.2 pg_kill_connection

此函数将pg_terminate_backend封装在内部，让被授予执行权限的普通用户拥有kill任何pid的能力。
请记住以超级用户身份执行它

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;
    commit transaction;

2.3 pg_kill_user_connection

此函数将pg_terminate_backend封装在内部，仅能终止同一用户自身建立的连接
请记住以超级用户身份执行它

BEGIN TRANSACTION;
-- 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;

3.示例：执行函数

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

4.安全选项说明

• SECURITY DEFINER:
  此安全选项将以创建该函数的用户的权限执行（所以必须是超级用户去创建pg_kill_connection或pg_kill_user_connection）,如此，当其他普通用户执行此函数时，实际将以创建它的超级用户的权限执行
• search_path:
  限定执行函数时的搜索路径，这里是限定到pg_catalog
  指定 search_path 以避免有人覆盖该函数很重要，特别是因为我们正在使用 SECURITY DEFINER

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.
简单说就是利用sear_path限定搜索路径，排除一切不受信任用户有写入权限的schema,同时因为temporary-table schema通常被首先搜索，而且因为这个schema,任何人都有写入的权限，故建议指定search_path时放在最后
这样做的目的，因为可能有人会在其他schema创建与pg_terminate_backend同名的函数(可能用途不同），导致真正执行时pg_kill_connection函数时，调用到错误的函数

• RETURNS NULL ON NULL INPUT：
  输入null参数时返回null,避免null inputs

5.赋予执行权限

REVOKE EXECUTE ON ALL FUNCTIONS:
在事务中使用 REVOKE EXECUTE ON ALL FUNCTIONS 编写这两个函数的原因是，如果其他用户有权访问 dbo 模式（GRANT USAGE），他们将能够执行它们，因此在创建之后确保 REVOKE 对 PUBLIC 的权限 。 PostgreSQL 文档对此非常清楚：

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.

在我们的示例中，我们可以在没有事务(begin…commit)的情况下创建函数，因为默认情况下没有用户有权访问该schema。 如果 Bob 尝试执行它，他会收到以下错误：

 ERROR: permission denied for schema dbo
SQL state: 42501

因此，我们将模式的 GRANT USAGE 授予 Bob 并授予执行函数的权限：

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;