本教程介绍了如何在 AlloyDB for PostgreSQL 中使用参数化安全视图,以使用 AlloyDB Studio 或 psql 限制用户对参数化视图的访问权限。
我们提供了一些示例来演示参数化安全视图的功能。这些示例仅用于演示目的。
前提条件
启用结算功能及所需 API
在 Google Cloud 控制台中,选择一个项目。
启用创建和连接到 AlloyDB for PostgreSQL 所需的 Cloud API。
- 在确认项目步骤中,点击下一步以确认您要更改的项目的名称。
在启用 API 步骤中,点击启用以启用以下内容:
- AlloyDB API
创建并连接到数据库
准备环境
为了准备对参数化安全视图运行查询,您必须先设置参数化视图、数据库和数据库角色、parameterized_view
扩展程序以及应用架构。
启用必需的扩展程序
启用 parameterized_views.enabled
数据库标志,以加载所需的扩展程序库。如需了解详情,请参阅配置实例的数据库标志。
设置数据库
- 为应用数据和参数化视图创建一个名为
database
的数据库。如需了解详情,请参阅创建数据库。
创建数据库角色、扩展程序和应用架构
使用 AlloyDB Studio 或 psql,以
postgres
用户身份或具有 AlloyDB 超级用户权限的用户身份连接到数据库。psql database -U postgres
如需了解详情,请参阅AlloyDB 中的数据库用户管理简介。
在数据库中创建
parameterized_views
扩展程序。-- Requires parameterized_views.enabled set to true CREATE EXTENSION parameterized_views;
创建扩展程序时,系统还会创建一个名为
parameterized_views
的架构,以便将 API 包含在该架构的命名空间中,并确保这些 API 不会与现有 API 发生冲突。创建 AlloyDB 管理员角色,该角色拥有并管理数据库。
CREATE ROLE admin_user WITH LOGIN PASSWORD '...'; GRANT ALL PRIVILEGES ON DATABASE database TO admin_user;
如需了解详情,请参阅
CREATE USER
。创建新的数据库角色,用于针对参数化安全视图执行查询。这是一个 AlloyDB 角色,应用使用它来连接并登录数据库以执行查询,但对公共函数或对象的访问权限仅限于所需的一组最低权限。
CREATE ROLE psv_user WITH LOGIN PASSWORD '...';
如需了解详情,请参阅
CREATE USER
。以管理员用户身份连接。
SET role TO admin_user;
创建包含表的架构。
CREATE SCHEMA schema;
创建表并插入数据。
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);
创建安全的参数化视图并设置访问权限
如需创建安全的参数化视图并为基表和视图设置适当的访问权限,请按照以下步骤操作:
使用 AlloyDB Studio 或 psql 以
admin_user
身份连接到数据库。psql database -U admin_user
如需提供对视图的有限访问权限,请创建参数化安全视图:
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;
授予对视图的访问权限。
GRANT SELECT ON schema.secure_checked_items TO psv_user;
如需访问该视图,请授予对相应架构的访问权限。
GRANT USAGE ON SCHEMA schema TO psv_user;
撤消对基表的直接访问权限。
REVOKE ALL PRIVILEGES ON schema.checked_items FROM psv_user;
验证数据安全
如需验证参数化安全视图是否限制对指定视图的访问权限,请以 psv_user
身份运行以下命令。这是 AlloyDB 数据库角色,应用使用它来连接并登录数据库以执行查询。
以参数化安全视图用户身份连接。
psql database -U psv_user
验证是否无法访问基表。
SELECT * FROM schema.checked_items; ERROR: permission denied for table checked_items
使用
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'] );
为了提高自然语言生成的查询的安全性,请使用 AlloyDB AI 自然语言集成参数化安全视图。