安全地访问和控制应用数据

参数化安全视图可根据应用用户身份过滤查询结果，从而提供行级数据安全性和访问权限控制。

本教程介绍了如何在 Cloud SQL 中设置参数化安全视图，配置数据库角色和权限以限制对基表的访问，以及验证数据安全性。本文档中提供的示例仅用于演示目的。

什么是参数化安全视图？

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

如果您的应用需要的安全性比标准数据库级访问权限更精细，您可以使用参数化安全视图来确保用户只能查看其已获授权的数据。

使用参数化安全视图主要有以下两个优势：

  • 动态行级安全性：使用最终用户标识符过滤查询，以便用户只能访问其授权的数据，而无论查询措辞如何。
  • 简化的角色管理：为所有应用用户使用单个共享数据库角色，而无需为每个用户单独管理数据库角色。

目标

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

费用

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

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

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

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

准备工作

在创建参数化安全视图之前，请完成以下前提条件。

启用结算功能及所需 API

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

    转到“项目选择器”

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

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

    1. 启用该 API

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

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

      • Cloud SQL for PostgreSQL API
      • Knowledge Catalog API

创建并连接到数据库

  1. 创建实例。
  2. 连接到您的实例并创建数据库。

准备环境

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

启用数据库标志

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

设置数据库

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

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

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

    前往 Cloud SQL

  2. 从列表中选择一个实例。

  3. 在导航菜单中，点击 Cloud SQL 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 用户。

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

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

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

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

    SET role TO postgres;

  11. 创建包含表的架构。

    CREATE SCHEMA store;

  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 控制台中，前往 Cloud SQL 页面。

    前往 Cloud SQL

  2. 从列表中选择一个实例。

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

  4. 登录 Studio，然后以 postgres 的身份连接到 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;

    注意：参数化视图的所有者必须对基表拥有 SELECT 权限。此外，参数化视图的用户必须对视图的架构拥有 USAGE 权限，并对视图拥有 SELECT 权限。例如，postgres 用户拥有参数化视图，因此对基表拥有 SELECT 权限，而 psv_user 必须对 store 架构拥有 USAGE 权限，并对视图拥有 SELECT 权限。

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

    GRANT psv_user TO "IAM_USER_EMAIL";

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

验证数据安全

如需验证参数化安全视图是否限制对指定视图的访问权限，请以通过 IAM 身份验证的用户身份登录数据库。在 Cloud SQL 中，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']
);

    1. 使用 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": {
          "cloud_sql_reference": {
            "database_reference": {
              "engine": "POSTGRESQL",
              "project_id": "PROJECT_ID",
              "region": "REGION",
              "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：Cloud SQL for PostgreSQL 实例所在的区域。
      • INSTANCE_ID：Cloud SQL for PostgreSQL 实例的 ID。
      • DATABASE_ID：Cloud SQL for PostgreSQL 数据库的 ID。

    清理

    删除集群

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

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

      前往 Cloud SQL

    2. 从列表中选择一个实例。

    3. 点击删除。

    4. 输入实例名称，然后点击删除，以确认您要删除该实例。