PL 权限

在 PostgreSQL 中，PL 是指 Procedural Language，即过程语言，用于扩展数据库功能和编写存储过程、函数等逻辑。常见的过程语言包括：

• PL/pgSQL：PostgreSQL 原生的过程语言。
• PL/Python：支持 Python 编写过程代码。
• PL/Perl：支持 Perl。
• PL/Tcl：支持 Tcl。

PL 权限简介

使用 PL 编写存储过程和函数时，需要注意权限问题：

1. 安装权限：

   • 某些过程语言（如 PL/Python、PL/Perl）需要手动安装到数据库中。
   • 超级用户权限才能安装过程语言。

2. 使用权限：

   • 编写、创建、修改过程函数需要具备对应的权限：
     • CREATE FUNCTION：创建函数的权限。
     • EXECUTE：运行函数的权限。

3. 安全性模式：

   • SECURITY INVOKER（默认）：函数以调用者权限执行。
   • SECURITY DEFINER：函数以创建者权限执行。

如何配置 PL 权限

1. 安装 PL 语言

默认情况下，PostgreSQL 自带 PL/pgSQL，无需安装。如果需要其他过程语言（如 PL/Python），需要以下步骤：

1. 检查支持的 PL 语言

   SELECT * FROM pg_available_extensions WHERE name LIKE 'pl%';
   

   示例输出：

    name      | default_version | installed_version | comment
   ------------+-----------------+-------------------+--------------------------------
    plpgsql    | 1.0             | 1.0               | PL/pgSQL procedural language
    plpython3u | 1.0             |                   | PL/Python3U untrusted procedural language
   

2. 安装所需的 PL 语言

   • 必须以超级用户（superuser）身份安装。
   • 安装 PL/Python 示例：

     CREATE EXTENSION plpython3u;
     

3. 查看已安装的 PL 语言

   SELECT lanname FROM pg_language;
   

   示例输出：

    lanname
   ----------
    plpgsql
    sql
    c
    plpython3u
   

2. 创建和管理函数权限

1. 创建函数

   CREATE FUNCTION test_function(x integer)
   RETURNS integer
   AS $$
   BEGIN
       RETURN x * 2;
   END;
   $$ LANGUAGE plpgsql;
   

2. 分配权限

   • 授予用户执行函数的权限：

     GRANT EXECUTE ON FUNCTION test_function(integer) TO username;
     

   • 撤销用户执行函数的权限：

     REVOKE EXECUTE ON FUNCTION test_function(integer) FROM username;
     

3. 以创建者或调用者权限运行

   • 默认情况下，函数以调用者权限运行（SECURITY INVOKER）。
   • 如果希望函数以创建者权限运行：

     CREATE FUNCTION secure_function()
     RETURNS void
     SECURITY DEFINER
     AS $$
     BEGIN
         -- some privileged operations
     END;
     $$ LANGUAGE plpgsql;
     

3. 安全性和限制

1. 受信任和不受信任的 PL 语言

   • 受信任（Trusted）：
     • 如 PL/pgSQL，普通用户可以使用。
   • 不受信任（Untrusted）：
     • 如 PL/PythonU（带 u 后缀的语言）。
     • 只能由超级用户使用，因为可能会访问数据库外的资源。

2. 限制某些用户创建过程语言的函数

   • 通过角色和权限控制：
     • 禁止用户创建函数：

       REVOKE CREATE ON SCHEMA public FROM username;
       

     • 允许特定用户在特定 schema 下创建：

       GRANT CREATE ON SCHEMA my_schema TO username;
       

示例：配置 PL/Python 权限

1. 安装 PL/Python

   CREATE EXTENSION plpython3u;
   

2. 创建 Python 函数

   CREATE FUNCTION py_test(a integer)
   RETURNS integer
   AS $$
   return a * 2
   $$ LANGUAGE plpython3u;
   

3. 授予其他用户执行权限

   GRANT EXECUTE ON FUNCTION py_test(integer) TO test_user;
   

4. 测试执行权限

   • 以 test_user 登录并运行：

     SELECT py_test(5);
     

总结

• PL 安装：通过 CREATE EXTENSION 安装受信任和不受信任的 PL 语言。
• 权限配置：
  • 使用 GRANT 和 REVOKE 管理函数执行权限。
  • 控制函数的执行模式（SECURITY INVOKER 或 SECURITY DEFINER）。
• 安全性：
  • 对于不受信任的过程语言，确保仅超级用户使用。
  • 配置合理的 schema 和角色权限，避免越权操作。