使用参数化安全视图安全地访问和控制应用数据

本教程介绍了如何在 AlloyDB for PostgreSQL 中使用参数化安全视图，以使用 AlloyDB Studio 或 psql 限制用户对参数化视图的访问权限。我们提供了一些示例来演示参数化安全视图的功能。这些示例仅用于演示目的。

目标

使用指定视图参数创建安全的参数化视图。
创建供应用用于连接到数据库和访问参数化安全视图的数据库角色。
向新角色授予对安全视图的权限，并撤消对基表的访问权限。
使用新角色进行连接，并验证是否无法访问受限表。
使用 execute_parameterized_query 函数对参数化安全视图运行查询。

费用

在本文档中，您将使用 Google Cloud的以下收费组件：

AlloyDB for PostgreSQL

您可使用价格计算器根据您的预计使用情况来估算费用。

新 Google Cloud 用户可能有资格申请免费试用。

完成本文档中描述的任务后，您可以通过删除所创建的资源来避免继续计费。如需了解详情，请参阅清理。

准备工作

请求访问权限

在使用参数化安全视图之前，您必须先请求访问 AlloyDB 参数化安全视图，并等到收到启用确认后再开始操作。

启用结算功能和必需的 API

在 Google Cloud 控制台中，选择一个项目。

转到“项目选择器”
确保您的 Google Cloud 项目已启用结算功能。
启用创建和连接到 AlloyDB for PostgreSQL 所需的 Cloud API。

启用该 API
1. 在确认项目步骤中，点击下一步以确认您要更改的项目的名称。
2. 在启用 API 步骤中，点击启用以启用以下内容：
  - AlloyDB API

创建并连接到数据库

准备环境

为了准备对参数化安全视图运行查询，您必须先设置参数化视图、数据库和数据库角色、parameterized_view 扩展程序以及应用架构。

设置参数化视图和数据库

请求 AlloyDB 团队启用 parameterized_views.enabled database 标志，该标志会加载所需的扩展程序库。
为应用数据和参数化视图创建一个名为 database 的数据库。如需了解详情，请参阅创建数据库。

创建数据库角色、扩展程序和应用架构

使用 AlloyDB Studio 或 psql，以 postgres 用户身份或具有 AlloyDB 超级用户权限的用户身份连接到数据库。
```
psql database -U postgres
```
如需了解详情，请参阅AlloyDB 中的数据库用户管理简介。
在数据库中创建 parameterized_views 扩展程序。
```
-- Requires parameterized_views.enabled set to true
CREATE EXTENSION parameterized_views;
```
创建扩展程序时，系统还会创建一个名为 parameterized_views 的架构，以便将 API 包含在该架构的命名空间中，并确保这些 API 不会与现有 API 发生冲突。

注意：您必须在创建了参数化视图的每个数据库中创建扩展程序。

创建 AlloyDB 管理员角色，该角色拥有并管理数据库。

CREATE ROLE admin_user WITH LOGIN PASSWORD '...';
 GRANT ALL PRIVILEGES ON DATABASE database TO admin_user;

如需了解详情，请参阅 CREATE USER。

创建新的数据库角色，用于针对参数化安全视图执行查询。这是一个 AlloyDB 角色，应用使用它来连接并登录数据库以执行查询，但对公共函数或对象的访问权限仅限于所需的一组最低权限。
```
CREATE ROLE psv_user WITH LOGIN PASSWORD '...';
```
如需了解详情，请参阅 CREATE USER。
以管理员用户身份连接。
```
SET role TO admin_user;
```
创建包含表的架构。
```
CREATE SCHEMA schema;
```

创建表并插入数据。

CREATE TABLE schema.checked_items(bag_id INT,timestamp TIMESTAMP, loc_code CHAR(3), scan_type CHAR(1), location TEXT, customer_id INT);

INSERT INTO schema.checked_items (bag_id, timestamp, loc_code, scan_type, location, customer_id) VALUES
(101, '2023-10-26 10:00:00', 'ABC', 'I', 'Warehouse A', 123),
(102, '2023-10-26 10:15:30', 'DEF', 'O', 'Loading Dock B', 456),
(103, '2023-10-26 10:30:45', 'GHI', 'I', 'Conveyor Belt 1', 789),
(104, '2023-10-26 11:00:00', 'JKL', 'O', 'Shipping Area C', 101),
(105, '2023-10-26 11:45:15', 'MNO', 'I', 'Sorting Station D', 202),
(106, '2023-10-26 12:00:00', 'PQR', 'O', 'Truck Bay E', 303);

创建安全的参数化视图并设置访问权限

如需创建安全的参数化视图并为基表和视图设置适当的访问权限，请按照以下步骤操作：

使用 AlloyDB Studio 或 psql 以 admin_user 身份连接到数据库。
```
psql database -U admin_user
```

如需提供对视图的有限访问权限，请创建参数化安全视图：

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

授予对视图的访问权限。

GRANT SELECT ON schema.secure_checked_items TO psv_user;

如需访问该视图，请授予对相应架构的访问权限。
```
GRANT USAGE ON SCHEMA schema TO psv_user;
```

撤消对基表的直接访问权限。

REVOKE ALL PRIVILEGES ON schema.checked_items FROM psv_user;

验证数据安全

如需验证参数化安全视图是否限制对指定视图的访问权限，请以 psv_user 身份运行以下命令。这是 AlloyDB 数据库角色，应用使用它来连接并登录数据库以执行查询。

以参数化安全视图用户身份连接。
```
psql database -U psv_user
```

验证是否无法访问基表。

SELECT * FROM schema.checked_items;
ERROR:  permission denied for table checked_items

使用 execute_parameterized_query 函数访问参数化安全视图：

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

为了提高自然语言生成的查询的安全性，请使用 AlloyDB AI 自然语言集成参数化安全视图。

清理

为避免因本教程中使用的资源导致您的 Google Cloud 账号产生费用，请删除包含这些资源的项目，或者保留项目但删除各个资源。

在 Google Cloud 控制台中，前往集群页面。

转到集群
点击资源名称列中集群 my-cluster 的名称。
点击 删除集群。
在删除集群 my-cluster 中，输入 my-cluster 以确认您要删除集群。
点击删除。
如果您在创建集群时创建了专用连接，请前往 Google Cloud 控制台的网络页面，然后点击删除 VPC 网络。
使用 AlloyDB Studio 或 psql 以 postgres 身份连接到数据库。
```
psql database -U postgres
```

删除您创建的对象。

DROP VIEW schema.secure_checked_items;
DROP TABLE schema.checked_items;
DROP SCHEMA schema;
DROP ROLE psv_user;
DROP ROLE admin_user;
DROP EXTENSION parameterized_views;

后续步骤

了解参数化安全视图。
了解如何使用参数化安全视图管理应用数据安全。