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

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

目标

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

准备环境

  1. 为 Cloud SQL 实例启用 cloudsql.enable_parameterized_views 数据库标志。此标志更改需要重启数据库。

  2. 以 postgres 用户身份连接到数据库。

    psql -U postgres

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

    CREATE EXTENSION parameterized_views;

  4. 创建新的数据库角色，用于执行查询。

    CREATE ROLE psv_user WITH LOGIN PASSWORD 'PASSWORD';

  5. 为应用数据创建架构和表。

    CREATE SCHEMA app_schema;
CREATE TABLE app_schema.items(item_id INT, item_name TEXT, description TEXT, owner_id INT);

INSERT INTO app_schema.items (item_id, item_name, description, owner_id) VALUES
(1, 'Book', 'A great read', 123),
(2, 'Laptop', 'Work machine', 456),
(3, 'Pencil', 'For writing', 123);

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

  1. 创建参数化安全视图：

    CREATE VIEW app_schema.user_items_view WITH (security_barrier) AS
SELECT item_id, item_name, description
FROM app_schema.items
WHERE owner_id = $@current_user_id;

  2. 向应用角色授予对视图和架构的访问权限。

    GRANT USAGE ON SCHEMA app_schema TO psv_user;
GRANT SELECT ON app_schema.user_items_view TO psv_user;

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

    REVOKE ALL PRIVILEGES ON app_schema.items FROM psv_user;

验证数据安全

  1. 以 psv_user 的身份关联。

    psql -U psv_user -d postgres

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

    SELECT * FROM app_schema.items;
-- ERROR:  permission denied for table items

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

    SELECT * FROM parameterized_views.execute_parameterized_query(
  query => 'SELECT * from app_schema.user_items_view',
  param_names => ARRAY ['current_user_id'],
  param_values => ARRAY ['123']
);

    结果应仅包含 owner_id 为 123 的商品。

后续步骤