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

选择文档版本：

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

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

目标

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

安装并连接到数据库

  1. 安装 AlloyDB Omni for Kubernetes 或 AlloyDB Omni for Containers。
  2. 创建集群及其主实例。
  3. 连接到您的实例（针对 Kubernetes、针对容器）

准备环境

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

在使用参数化安全视图之前，您必须在每个新的 postgres 容器中执行一次以下操作。您可以使用 ALTER SYSTEM 或通过直接修改 postgresql.conf 来应用每项设置：

  1. 将“parameterized_views”添加到 shared_preload_libraries 中。
  2. 通过设置 parameterized_views.enabled=on 来启用该功能。
  3. 重启 postgres 以使更改生效。

设置数据库

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

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

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

    psql database -U postgres

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

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

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

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

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

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

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

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

    如需了解详情，请参阅 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. 使用 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 Omni 数据库角色，应用使用该角色连接并登录数据库来执行查询。

  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 Omni 实例，也可以保留该实例但删除各个对象。

后续步骤