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

本教程介绍了如何在 AlloyDB for PostgreSQL 中使用参数化安全视图，以使用 AlloyDB Studio 或 psql 限制用户对参数化视图的访问权限。

我们提供了一些示例来演示参数化安全视图的功能。这些示例仅用于演示目的。

前提条件

启用结算功能及所需 API

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

    转到“项目选择器”

  2. 确保您的 Google Cloud 项目已启用结算功能。

  3. 启用创建和连接到 AlloyDB for PostgreSQL 所需的 Cloud API。

    启用该 API

    1. 在确认项目步骤中，点击下一步以确认您要更改的项目的名称。

    2. 在启用 API 步骤中，点击启用以启用以下内容：

      • AlloyDB API

创建并连接到数据库

  1. 创建集群及其主实例。
  2. 连接到您的实例并创建数据库。

准备环境

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

启用必需的扩展程序

启用 parameterized_views.enabled 数据库标志，以加载所需的扩展程序库。如需了解详情，请参阅配置实例的数据库标志。

设置数据库

  • 为应用数据和参数化视图创建一个名为 database 的数据库。如需了解详情，请参阅创建数据库。

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

  1. 使用 AlloyDB Studio 或 psql，以 postgres 用户身份或具有 AlloyDB 超级用户权限的用户身份连接到数据库。

    psql database -U postgres

    如需了解详情，请参阅AlloyDB 中的数据库用户管理简介。

  2. 在数据库中创建 parameterized_views 扩展程序。

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

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

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

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

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

  4. 创建新的数据库角色，用于针对参数化安全视图执行查询。这是一个 AlloyDB 角色，应用使用它来连接并登录数据库以执行查询，但对公共函数或对象的访问权限仅限于所需的一组最低权限。

    CREATE ROLE psv_user WITH LOGIN PASSWORD '...';

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

  5. 以管理员用户身份连接。

    SET role TO admin_user;

  6. 创建包含表的架构。

    CREATE SCHEMA schema;

  7. 创建表并插入数据。

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

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

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

  1. 使用 AlloyDB Studio 或 psql 以 admin_user 身份连接到数据库。

    psql database -U admin_user

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

    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;

  3. 授予对视图的访问权限。

    GRANT SELECT ON schema.secure_checked_items TO psv_user;

  4. 如需访问该视图，请授予对相应架构的访问权限。

    GRANT USAGE ON SCHEMA schema TO psv_user;

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

    REVOKE ALL PRIVILEGES ON schema.checked_items FROM psv_user;

验证数据安全

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

  1. 以参数化安全视图用户身份连接。

    psql database -U psv_user

  2. 验证是否无法访问基表。

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

  3. 使用 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']
);

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