使用 AlloyDB 参数化安全视图管理应用数据安全

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

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

准备工作

本文档假定您已创建 AlloyDB 集群和实例。如需了解详情，请参阅创建数据库。

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

1. 请求对参数化安全视图的访问权限，并等到收到启用确认后再开始操作。

2. 等待 AlloyDB 团队启用 parameterized_views.enabled 数据库标志，以加载所需的扩展程序库。您必须先启用此数据库标志，然后才能开始操作。

   在 AlloyDB 团队启用 parameterized_views.enabled 数据库标志后，您的数据库会重启以使这些更改生效。

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

   -- Requires parameterized_views.enabled set to true
   CREATE EXTENSION parameterized_views;
   

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

创建参数化安全视图

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

1. 运行 CREATE VIEW DDL 命令，如以下示例所示：

   CREATE VIEW secure_checked_items WITH (security_barrier) AS
   SELECT bag_id, timestamp, location
   FROM checked_items t
   WHERE customer_id = $@app_end_userid;
   

   在上述示例中，参数化安全视图允许访问名为 /users/checked_items/ 的表中的三列内容。该视图将结果限制为 /users.id/checked_items.customer_id/ 与必需参数匹配的行。

   使用以下属性：

   • 使用 security_barrier 选项创建视图。
   • 如需限制应用用户，使其只能查看允许其访问的行，请在 WHERE 子句中使用 $@PARAMETER_NAME 语法添加必需的参数。一种常见的使用场景是使用 WHERE COLUMN = $@PARAMETER_NAME 检查列的值。
   • $@PARAMETER_NAME 表示已命名的视图参数。当您使用 execute_parameterized_query API 时，系统会提供相应的值。已命名的视图参数必须满足以下要求：
     • 已命名的视图参数必须以字母 (a-z) 开头。
     • 您可以使用带变音符号的字母和非拉丁字母，也可以使用下划线 (_)。
     • 后续字符可以是字母、下划线或数字 (0-9)。
     • 已命名的视图参数不能包含 $。
     • 已命名的视图参数区分大小写。例如，$@PARAMETER_NAME 的解读方式与 $@parameter_name 不同。

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

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

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

为应用配置安全选项

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

1. 以管理员用户身份创建参数化安全视图。此用户是 AlloyDB 数据库用户，负责为应用执行管理操作，包括数据库设置和安全管理。

2. 创建新的数据库角色，用于针对参数化安全视图执行查询。此用户是 AlloyDB 数据库用户，应用使用它来连接和登录数据库以及针对参数化视图执行查询。

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

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

   如需了解详情，请参阅创建数据库用户。

查询参数化安全视图

如需查询参数化安全视图，请使用以下选项中最适合您应用场景的选项：

• 基于 JSON：使用此 API 一次性运行查询并返回 JSON 行。
• 基于游标：如果您有长时间运行的查询，或者有大型查询，并且您希望分批提取结果，请使用此 API。parameterized_views 扩展程序提供的 execute_parameterized_query 函数接受游标名称。
• PREPARE EXECUTE 语句：对于可使用不同参数值多次执行的预备语句，请使用此选项。

如需查询参数化安全视图，您可以使用 parameterized_views 扩展程序提供的 execute_parameterized_query() 函数。

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

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => 'SELECT * FROM secure_checked_items',
    param_names => ARRAY ['app_end_userid'],
    param_values => ARRAY ['40']
)

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

  -- start a transaction as the that is the default lifetime of a CURSOR
  BEGIN;
  -- create a cursor called 'mycursor'
  SELECT * FROM parameterized_views.execute_parameterized_query(
   query => 'SELECT * FROM secure_checked_items',
   cursor_name => 'mycursor'
   param_names => ARRAY ['app_end_userid'],
   param_values => ARRAY ['40']
  );

  -- then, to actually fetch the results
  FETCH ALL FROM mycursor;
  -- end the transaction, which will clean up the cursor
  END;

PREPARE pquery (/POSITIONAL_PARAM_TYPES/)
        AS RESTRICTED query % a query that may refer to parameterized views
EXECUTE pquery (/POSITIONAL_PARAM_VALUES/)
      WITH VIEW PARAMETERS (VIEW_PARAM_NAME1 = VIEW_PARAM_VALUE1[, ...]);

PREPARE pquery (timestamp) AS RESTRICTED SELECT * FROM secure_checked_items WHERE timestamp > $1;

EXECUTE pquery (current_date - 1) WITH VIEW PARAMETERS (app_end_userid = 40);
EXECUTE pquery (current_date - 30) WITH VIEW PARAMETERS (app_end_userid = 40);

强制实施的查询限制

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

• 禁止以任何方式（包括通过 execute_parameterized_query 或使用 EXECUTE .. WITH VIEW PARAMETERS）对任何 API 进行递归调用，以便仅使用应用指定的值。此限制还可防止使用查询来规避给定的一组参数值的安全范围。
• 某些会启动新的后台会话的扩展程序是不允许的，包括 dblink、pg_cron 和 pg_background 扩展程序。
• 以下列表列出了允许但受限的查询结构集：
  • 允许使用只读 SELECT 语句。
  • 允许使用只读 SHOW 语句、CALL 语句和 DO 语句。
  • 不允许使用 INSERT、UPDATE 和 DELETE 等 DML 语句。
  • 不允许使用 CREATE TABLE 和 ALTER TABLE 等 DDL 语句。
  • 不允许使用其他语句类型，例如 LOAD、SET、CLUSTER、LOCK、CHECKPOINT 和 EXPLAIN。
• 禁止使用 EXPLAIN 语句，以避免使用查询计划进行隐蔽通道攻击。如需了解详情，请参阅隐蔽通道。

列出所有参数化视图

使用 parameterized_views 扩展程序，通过 all_parameterized_views 视图列出数据库中的所有参数化视图。此视图的输出与 pg_views 目录视图相同，但 all_parameterized_views 仅列出具有已命名视图参数的视图。

如需列出参数化视图，请使用以下示例：

postgres=# select * from parameterized_views.all_parameterized_views ;
schemaname |      viewname      | viewowner |                       definition
-----------+--------------------+-----------+---------------------------------------------------------
public     | checked_items_view | postgres  |  SELECT checked_items.bag_id,                          +
           |                    |           |     checked_items."timestamp",                         +
           |                    |           |     checked_items.location                             +
           |                    |           |    FROM checked_items                                  +
           |                    |           |   WHERE (checked_items.customer_id = $@app_end_userid);

如需在 all_parameterized_views 中列出参数化视图，请确保该参数化视图的定义中包含至少一个已命名视图参数。

后续步骤

• 了解参数化安全视图。
• 了解如何使用参数化安全视图来保护应用数据安全并控制对应用数据的访问权限。