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

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

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

准备工作

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

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

请求对参数化安全视图的访问权限，并等到收到启用确认后再开始操作。
等待 AlloyDB 团队启用 parameterized_views.enabled 数据库标志，以加载所需的扩展程序库。您必须先启用此数据库标志，然后才能开始操作。

在 AlloyDB 团队启用 parameterized_views.enabled 数据库标志后，您的数据库会重启以使这些更改生效。
使用 AlloyDB Studio 或 psql 在创建了参数化视图的任何数据库中创建 parameterized_views 扩展程序：
```
-- Requires parameterized_views.enabled set to true
CREATE EXTENSION parameterized_views;
```
创建扩展程序时，系统还会创建一个名为 parameterized_views 的架构，以便将所需 API 包含在该架构的命名空间中，并确保这些 API 不会与现有 API 发生冲突。

创建参数化安全视图

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

运行 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 不同。
向允许其查询视图的任何数据库用户授予视图的 SELECT 权限。
向允许其查询视图的任何数据库用户授予对包含视图中所定义表的架构的 USAGE 权限。

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

为应用配置安全选项

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

以管理员用户身份创建参数化安全视图。此用户是 AlloyDB 数据库用户，负责为应用执行管理操作，包括数据库设置和安全管理。
创建新的数据库角色，用于针对参数化安全视图执行查询。此用户是 AlloyDB 数据库用户，应用使用它来连接和登录数据库以及针对参数化视图执行查询。
1. 向新角色授予对安全视图的权限，这些权限通常包括对视图的 SELECT 权限以及对架构的 USAGE 权限。
2. 将此角色可访问的对象限制为应用所需的最低必需公共函数和对象集。避免提供对非公开架构和表的访问权限。
当您查询视图时，应用会提供与应用用户身份相关联的必需视图参数的值。

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

查询参数化安全视图

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

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

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

JSON API

此 API 存在一定限制，因为它为给定的查询声明了一个游标。因此，查询必须与 PostgreSQL 游标兼容。例如，CURSOR API 不支持 DO 或 SHOW 语句。

此 API 也不会按大小或按返回的行数限制结果。

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

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

替换以下内容：

SQL_QUERY：其 FROM 子句引用了一个或多个参数化安全视图的 SQL 查询。
PARAMETER_NAMES：要以字符串形式传入的参数名称的列表。
PARAMETER_VALUES：要传入的参数值的列表。
- 此列表的大小必须与 param_names 列表的大小相同，并且值的顺序必须与名称的顺序一致。
- 值的确切类型是从查询和参数化视图定义中推断出来的。系统会在需要时尽可能对给定的参数值执行类型转换。如果出现类型不匹配，则会抛出错误。

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

使用以下示例查询参数化安全视图：

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

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

CURSOR API

运行 execute_parameterized_query() 函数，该函数会创建并返回一个事务范围的 CURSOR，您可以使用该 CURSOR 来检索查询结果：

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

替换以下内容：

SQL_QUERY：其 FROM 子句引用了一个或多个参数化安全视图的 SQL 查询。
CURSOR_NAME：要声明的游标的名称。
PARAMETER_NAMES：要以字符串形式传入的参数名称的列表。
PARAMETER_VALUES：要传入的参数值的列表。此列表的大小必须与 param_names 列表的大小相同，并且值的顺序必须与名称的顺序一致。值的确切类型是从查询和参数化视图定义中推断出来的。系统会在需要时尽可能对给定的参数值执行类型转换。如果出现类型不匹配，则会抛出错误。

使用以下示例查询参数化安全视图：

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

返回的游标是 WITHOUT HOLD 的 NO SCROLL 游标。您无法使用游标以非顺序方式（例如向后）检索行。您无法在创建游标的事务之外使用该游标。

PREPARE 语句

使用 PREPARE .. AS RESTRICTED 命令创建引用了参数化视图的预备语句。这些预备语句支持位置参数，并在您执行这些语句时强制实施各种限制。如需了解详情，请参阅安全机制。

此功能扩展了 PREPARE 和 EXECUTE commands，以支持已命名的视图参数。使用预备语句可避免每次执行语句时都进行解析、分析和重写所产生的开销，从而显著提升性能，尤其是在频繁执行查询或执行复杂查询的场景中。预备语句是一种服务器端对象，可通过预编译和存储参数化 SQL 语句以供日后执行来优化性能。

此 API 有一定限制，因为相应语句必须允许在 PREPARE 语句中执行，这意味着仅支持 SELECT 和 VALUES 语句。

此 API 也不会按大小或按返回的行数限制结果。

如需创建引用了参数化视图的预备语句，请运行 PREPARE .. AS RESTRICTED 命令：

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[, ...]);

替换以下内容：

POSITIONAL_PARAM_TYPES：RESTRICTED 查询中使用的一个或多个位置参数。
POSITIONAL_PARAM_VALUES：替换 PREPARE 语句中定义的位置参数的实际值。
VIEW_PARAM_NAME：RESTRICTED 查询中引用的参数化视图所需的参数的名称。
VIEW_PARAM_VALUE：传递给参数化视图的相应 viewParamName 参数的实际值。

如需在预备语句中添加参数，您可以在 PREPARE 语句中提供数据类型列表。在预备语句中，您可以使用 $1 和 $2 等按位置引用参数。

使用 EXECUTE .. WITH VIEW PARAMETERS 命令执行您先前使用 PREPARE .. AS RESTRICTED 命令创建的预备语句。如果创建语句的 PREPARE 语句指定了位置参数，则您必须将一组兼容的参数传递给 EXECUTE 语句。您必须在 WITH VIEW PARAMETERS 子句中传递参数化视图所需的任何已命名视图参数。

使用以下示例查询参数化安全视图：

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 中列出参数化视图，请确保该参数化视图的定义中包含至少一个已命名视图参数。

后续步骤

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

使用 AlloyDB 参数化安全视图管理应用数据安全 使用集合让一切井井有条 根据您的偏好保存内容并对其进行分类。

准备工作

创建参数化安全视图

为应用配置安全选项

查询参数化安全视图

JSON API

CURSOR API

PREPARE 语句

强制实施的查询限制

列出所有参数化视图

后续步骤

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