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

本教程介绍了如何在 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 Studiopsql,以 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 Studiopsqladmin_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 自然语言集成参数化安全视图。