五月 30, 2024
摘要：在本教程中，您将学习如何使用 PostgreSQL 行级安全性，来控制对表中各个行的访问。
目录
PostgreSQL 行级安全性简介
行级安全性（RLS）是一个特性，允许您根据执行查询的用户来限制查询返回的行。
RLS 允许您，根据当前用户和由策略定义的特定条件，来控制对表中各个行的访问。
实现行级安全性的基本步骤如下：
首先，使用ALTER TABLE语句，在表上启用行级安全性：
ALTER TABLE table_name
ENABLE ROW LEVEL SECURITY;
第二步，使用CREATE POLICY语句，为一个表创建新的行级安全策略：
CREATE POLICY name ON table_name
USING (condition);
在策略中，您可以定义一个条件，用于确定哪些行是可见的。
请注意，带有BYPASSRLS属性的超级用户和角色，在访问表时可以绕过行级安全系统。
此外，表所有者也会绕过行级安全性。要对表所有者应用行级安全性，可以使用FORCE ROW LEVEL SECURITY选项修改表：
ALTER TABLE table_name
FORCE ROW LEVEL SECURITY;
PostgreSQL 行级安全性示例
我们将演示一个创建表和角色的例子，其中角色可以从表中检索manager列与当前角色匹配的数据。
1. 新建一个数据库，名为hr：
create database hr;
2. 将当前数据库更改为hr数据库：
\c hr
3. 创建一个新表，名为departments，用来存储部门数据：
create table departments(
id serial primary key,
name VARCHAR(255) NOT NULL UNIQUE,
manager VARCHAR(255) NOT NULL
);
4. 在departments表中插入一些行：
INSERT INTO departments(name, manager)
VALUES('Sales', 'alice'),
('Marketing', 'bob'),
('IT', 'jack');
5. 创建一个组角色，名为managers：
CREATE ROLE managers;
6. 将public模式中所有表的SELECT权限授予给组角色managers：
GRANT SELECT ON ALL TABLES
IN SCHEMA public
TO managers;
7. 创建三个新角色alice、bob、peter，并将其分配为managers组角色的成员：
CREATE ROLE alice WITH LOGIN PASSWORD 'SecurePass1'
IN ROLE managers;
CREATE ROLE bob WITH LOGIN PASSWORD 'SecurePass2'
IN ROLE managers;
CREATE ROLE jack WITH LOGIN PASSWORD 'SecurePass3'
IN ROLE managers;
角色alice、bob和jack将会隐式继承组角色managers的权限。换句话说，它们可以从public模式中的所有表中检索数据。
8. 在departments表上启用行级安全性：
ALTER TABLE departments
ENABLE ROW LEVEL SECURITY;
9. 创建一个策略，让当前用户可以访问departments表中manager列的值与当前角色名称匹配的行：
CREATE POLICY department_managers
ON departments
TO managers
USING (manager = current_user);
10. 在单独的会话中，使用alice角色连接到hr数据库：
psql -U alice -d hr
11. 从departments表中检索数据：
SELECT * FROM departments;
输出：
id | name | manager
----+-------+---------
1 | Sales | alice
(1 row)
该查询会返回manager列值为alice的行。
12. 在单独的会话中，使用bob角色连接到hr数据库：
psql -U bob -d hr
13. 从departments表中查询数据：
SELECT * FROM departments;
输出：
id | name | manager
----+-----------+---------
2 | Marketing | bob
(1 row)
与alice一样，bob只能检索到manager列值为bob的数据。
总结
- 使用
ALTER TABLE table_name ENABLE ROW LEVEL SECURITY语句，启用表的行级安全性。 - 使用
CREATE POLICY语句，为一个表定义新的行级安全策略。