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

本教程介绍了如何在 AlloyDB for PostgreSQL 中使用参数化安全视图来限制用户对数据库表的访问权限。在本教程中，您将设置参数化安全视图，配置数据库角色和权限以限制对基表的访问，并验证数据安全性。参数化安全视图有助于保护使用通过 QueryData API 生成的自然语言查询的应用。

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

什么是参数化安全视图？

作为一般最佳实践，您的应用应使用具有对数据库的最低必需访问权限的服务账号运行。例如，如果您的应用不应写入数据库，则应使用具有只读访问权限的角色。您应在数据库级层配置这些访问权限控制。

不过，有时您的应用需要的安全性控制比标准数据库级访问权限更精细。参数化安全视图使用 SQL 视图提供应用数据安全和行级访问权限控制。它们有助于确保应用用户只能查看他们有权访问的数据。

在处理临时查询或 LLM 生成的自然语言查询时，参数化安全视图通过使用最终用户标识符对视图进行参数化来缓解安全风险，从而确保用户只能访问其授权的数据，无论查询措辞如何。此方法还可简化用户管理，让单个数据库角色能够安全地为所有应用用户提供服务，从而避免了为每个用户创建单独的数据库角色来强制执行行级安全性的需求。

目标

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

费用

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

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

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

为避免继续计费，请在完成本文档中的任务后删除您创建的资源。如需了解详情，请参阅清理。

准备工作

在创建上下文集之前，请完成以下前提条件。

启用结算功能及所需 API

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

    转到“项目选择器”

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

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

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

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

      • AlloyDB for PostgreSQL API
      • Dataplex Universal Catalog API

创建并连接到数据库

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

准备环境

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

启用所需扩展程序

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

设置数据库

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

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

  1. 在 Google Cloud 控制台中，前往 AlloyDB 页面。

    前往 AlloyDB

  2. 从列表中选择一个集群。

  3. 在导航菜单中，点击 AlloyDB Studio。

  4. 使用 postgres 身份验证登录 Studio。

  5. 点击身份验证。 “探索器”窗格会显示数据库中的对象列表。

  6. 点击新的 SQL 编辑器标签页或新标签页以打开新标签页

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

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

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

  8. 以具有超级用户权限的用户身份登录，例如内置的 postgres 用户。如果您想创建新的超级用户（例如 admin_user），请运行以下命令：

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

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

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

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

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

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

    SET role TO admin_user;

  11. 创建包含表的架构。

    CREATE SCHEMA schema;

  12. 创建表并插入数据。

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

INSERT INTO store.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. 在 Google Cloud 控制台中，前往 AlloyDB 页面。

    前往 AlloyDB

  2. 从列表中选择一个集群。

  3. 在导航菜单中，点击 AlloyDB Studio。

  4. 登录 Studio，然后以 admin_user 身份连接到 database。

  5. 点击身份验证。 “探索器”窗格会显示数据库中的对象列表。

  6. 点击新的 SQL 编辑器标签页或新标签页以打开新标签页。

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

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

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

    GRANT SELECT ON store.secure_checked_items TO psv_user;

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

    GRANT USAGE ON SCHEMA store TO psv_user;

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

    REVOKE ALL PRIVILEGES ON store.checked_items FROM psv_user;

  11. 以 admin_user 管理员身份登录，并向通过 IAM 身份验证的用户授予 psv_user 角色

    GRANT psv_user TO "IAM_USER_EMAIL";

    将 IAM_USER_EMAIL 替换为您的 IAM 用户电子邮件地址。

验证数据安全

如需验证参数化安全视图是否限制对指定视图的访问权限，请以经过 IAM 身份验证的用户身份登录数据库。在 AlloyDB 中，IAM 用户会继承分配给他们的数据库角色的权限。

  1. 以经过 IAM 身份验证的用户身份登录数据库。

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

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

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

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

  4. 使用 SQL 语法和包含 PSV 参数的 QueryData 请求查询参数化安全视图。

    curl -X POST \
      "https://geminidataanalytics.googleapis.com/v1beta/projects/PROJECT_ID/locations/REGION:queryData" \
      -H "Authorization: Bearer $(gcloud auth print-access-token)" \
      -H "Content-Type: application/json; charset=utf-8" \
      -d '{
        "prompt": "Show me the checked items.",
        "context": {
          "datasource_references": {
            "alloydb": {
              "database_reference": {
                "project_id": "PROJECT_ID",
                "region": "REGION",
                "cluster_id": "CLUSTER_ID",
                "instance_id": "INSTANCE_ID",
                "database_id": "DATABASE_ID"
              }
            }
          },
          "parameterized_secure_view_parameters": {
            "parameters": {
              "app_end_userid": "303"
            }
          }
        },
        "generation_options": {
          "generate_query_result": true,
          "generate_natural_language_answer": true,
          "generate_explanation": true
        }
      }'

替换以下值：

  • PROJECT_ID：您的 Google Cloud 项目 ID。
  • REGION：AlloyDB for PostgreSQL 实例所在的区域。
  • CLUSTER_ID：AlloyDB for PostgreSQL 集群的 ID。
  • INSTANCE_ID：AlloyDB for PostgreSQL 实例的 ID。
  • DATABASE_ID：AlloyDB for PostgreSQL 数据库的 ID。

清理

删除集群

删除您在准备工作部分中创建的集群时，您创建的所有对象也会一并删除。

  1. 在 Google Cloud 控制台中，前往 AlloyDB 页面。

    前往 AlloyDB

  2. 从列表中选择一个集群。

  3. 点击删除集群。

  4. 在删除集群中，输入集群的名称以确认您要删除相应集群。

后续步骤