使用参数化安全视图

本文档介绍如何在 Cloud SQL for PostgreSQL 中使用参数化安全视图，以便根据应用特定的命名参数（例如应用用户凭证）限制数据访问权限。参数化安全视图通过扩展 PostgreSQL 视图的功能来加强安全性和访问权限控制。这些视图还会自动对执行的任何查询强制实施多项限制，从而降低运行来自应用的不可信查询的风险。

如需了解详情，请参阅参数化安全视图概览和参数化安全视图教程。

准备工作

本文档假定您已创建 Cloud SQL for PostgreSQL 实例。

如要使用参数化安全视图，您必须先执行以下操作：

  1. 为您的 Cloud SQL 实例启用 cloudsql.enable_parameterized_views 数据库标志。此标志更改需要重启数据库。如需了解详情，请参阅配置数据库标志。

  2. 使用 Cloud SQL Studio 或 psql 在创建了 参数化视图的任何数据库中创建 parameterized_views 扩展程序：

    -- Requires cloudsql.enable_parameterized_views set to on
CREATE EXTENSION parameterized_views;

    创建扩展程序时，系统还会创建一个名为 parameterized_views 的架构，以便将所需 API 包含在该架构的命名空间中，并确保这些 API 不会与现有 API 发生冲突。

创建参数化安全视图

如需创建参数化安全视图，请按以下步骤操作：

  1. 使用 security_barrier 选项运行 CREATE VIEW DDL 命令：

    CREATE VIEW VIEW_NAME WITH (security_barrier) AS
SELECT COLUMN_NAME, COLUMN_NAME_N
FROM TABLE_NAME ALIAS
WHERE CONDITION;

    替换以下内容：

    • VIEW_NAME：参数化安全视图的名称
    • TABLE_NAME：要在参数化安全视图中使用的表的名称
    • ALIAS：要在参数化安全视图中使用的表名称的别名
    • COLUMNNAME 或 COLUMN_NAMEN： 要在参数化安全视图中使用的表列的名称

    • CONDITION：用于限制应用用户的条件语句，以便他们只能查看允许其访问的行。在 WHERE 子句中使用 $@PARAMETER_NAME 语法添加必需的参数。一个常见的用例是使用 WHERE COLUMN = $@PARAMETER_NAME 检查列的值。

      $@PARAMETER_NAME 表示已命名的视图参数。当您使用 execute_parameterized_query API 时，系统会提供相应的值。已命名的视图参数必须满足以下要求：

      • 已命名的视图参数必须以字母 (a-z) 或下划线 (_) 开头。
      • 后续字符可以是字母、下划线或数字 (0-9)。
      • 已命名的视图参数区分大小写。例如，$@PARAMETER_NAME 的解读方式与 $@parameter_name 不同。

      以下示例展示了如何创建使用已命名视图参数的参数化安全视图：

      CREATE VIEW user_specific_items WITH (security_barrier) AS
SELECT item_id, item_name, description, owner_id
FROM items t
WHERE owner_id = $@app_user_id;

  2. 向允许其查询视图的任何数据库用户授予视图的 SELECT 权限。

  3. 向允许其查询视图的任何数据库用户授予对包含视图中所定义表的架构的 USAGE 权限。

如需了解详情，请参阅使用参数化安全视图保护应用数据安全并控制对应用数据的访问权限（教程）。

为应用配置安全选项

如需使用参数化安全视图为应用配置安全选项，请按以下步骤操作：

  1. 以管理员用户身份创建参数化安全视图。这是 Cloud SQL 数据库用户，用于为应用执行管理操作。

  2. 创建新的数据库角色，用于针对参数化安全视图执行查询。这是 Cloud SQL 数据库角色，应用使用它来连接并登录数据库。

    1. 向新角色授予对安全视图的权限，这些权限通常包括对视图的 SELECT 权限以及对架构的 USAGE 权限。
    2. 将此角色可访问的对象限制为应用所需的最低必需公共函数和对象集。避免提供对非公开架构和表的访问权限。

    当您查询视图时，应用会提供与应用用户身份相关联的必需视图参数的值。

    如需了解详情，请参阅创建和管理用户。

查询参数化安全视图

如需查询参数化安全视图，请使用以下选项之一：

  • 基于 JSON：使用此 API 一次性运行查询并返回 JSON 行。
  • 基于游标：如果您有长时间运行的查询，或者有大型查询，并且您希望分批提取结果，请使用此 API。
  • PREPARE .. AS RESTRICTED 语句：使用 PREPARE .. AS RESTRICTED 定义查询计划，然后运行 EXECUTE ... WITH VIEW PARAMETERS (...) 以使用视图的特定参数执行该计划。

JSON API

运行 execute_parameterized_query() 函数，该函数具有以下语法：

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
);

该函数会返回一个 JSON 对象表。表中的每一行都相当于原始查询结果行的 row_to_json() 值。

使用此 API 会按结果的大小（以 KB 为单位）和行数限制结果集的大小。您可以使用 parameterized_views.json_results_max_size 和 parameterized_views.json_results_max_rows 配置这些限制。

CURSOR API

使用游标名称运行 execute_parameterized_query() 函数，该函数会创建并返回一个事务范围的 CURSOR：

-- Must be in a transaction block
BEGIN;

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    cursor_name => CURSOR_NAME,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
);

FETCH ALL FROM CURSOR_NAME;

END;

预处理语句

借助预处理语句方法，您可以准备一次查询计划，然后使用查询的位置参数和视图的命名参数的不同值多次执行该计划。

如需使用预处理语句，请执行以下操作：

  1. 创建预处理语句。

    -- Prepare the statement
PREPARE PREPARED_STATEMENT_NAME (QUERY_PARAM_TYPE_1, QUERY_PARAM_TYPE_N)
AS RESTRICTED SQL_QUERY;

    替换以下内容：

    • PREPARED_STATEMENT_NAME：预处理语句的名称
    • QUERY_PARAM_TYPE_N：查询参数的数据类型，例如 TEXT
    • SQL_QUERY：要作为预处理语句的一部分执行的 SQL 查询，其中包含给定的值

  2. 执行预处理语句。

    -- Execute the statement with query parameters and view parameters
EXECUTE PREPARED_STATEMENT_NAME (QUERY_VALUE_1, QUERY_VALUE_N)
WITH VIEW PARAMETERS (PARAMETER_NAME_1 := 'PARAMETER_VALUE_1', PARAMETER_NAME_N := 'PARAMETER_VALUE_N');

    替换以下内容：

    • PREPARED_STATEMENT_NAME：预处理语句的名称。
    • QUERY_VALUE_N：要作为 SQL 查询的参数提供的值
    • PARAMETER_NAME_N：您在创建参数化 安全视图时定义的已命名视图 参数的名称。您可以从表的列创建已命名视图参数。
    • PARAMETER_VALUE_N：已命名视图参数的值，该参数会将参数化安全视图限制为与该值关联的行。

  3. 清理预处理语句。

    -- Cleanup
DEALLOCATE PREPARED_STATEMENT_NAME>;

以下示例使用名为 user_specific_items 的参数化安全视图，该视图需要已命名视图参数 $@app_user_id。

-- Prepare a query with a positional parameter $1 for the item_name pattern
PREPARE get_items_by_name (TEXT) AS RESTRICTED
SELECT item_id, item_name FROM user_specific_items
WHERE item_name LIKE $1;

创建预处理语句后，您可以多次执行该语句，并为查询和已命名视图参数分配不同的值。

例如，第一次查询执行：

-- Execute for user 123, looking for items like '%Laptop%'
EXECUTE get_items_by_name ('%Laptop%')
WITH VIEW PARAMETERS (app_user_id := '123');

第二次查询执行：

-- Execute for user 456, looking for items like '%Book%'
EXECUTE get_items_by_name ('%Book%')
WITH VIEW PARAMETERS (app_user_id := '456');

WITH VIEW PARAMETERS 子句用于提供参数化安全视图的已命名视图参数（123、456），与预处理查询的位置参数（%Laptop%、%Book%）分开。

最后，清理预处理语句。

-- Clean up the get_item_by_name prepared statement
DEALLOCATE get_items_by_name;

强制实施的查询限制

下面列出了使用 查询参数化安全视图部分所述选项运行查询时的一组受限操作：

  • 只读：仅允许使用只读 SELECT 语句。禁止使用 DML（INSERT、UPDATE、DELETE）和 DDL（CREATE、ALTER）。
  • 禁止嵌套：禁止对 execute_parameterized_query 进行递归调用。
  • 扩展程序限制：禁止使用某些会启动新的后台会话的扩展程序（例如 dblink、pg_cron）。
  • 禁止使用 EXPLAIN 语句，以防止使用查询计划泄露潜在信息。

列出所有参数化视图

使用 parameterized_views.all_parameterized_views 视图列出所有参数化视图（包含至少一个命名参数 $@... 的视图）。

SELECT * FROM parameterized_views.all_parameterized_views;

后续步骤