PostgreSQL教程FG082-PG行级安全策略：RLS配置与实战

本文档风哥主要介绍PostgreSQL的行级安全策略（RLS），包括RLS的概念、工作原理、配置方法以及生产环境中的实战案例。风哥教程参考PostgreSQL官方文档Server Administration内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

Part01-基础概念与理论知识

1.1 PostgreSQL行级安全策略（RLS）的概念

PostgreSQL行级安全策略（RLS）是一种基于行级别的访问控制机制，允许用户根据特定条件限制对表中行的访问。RLS可以确保用户只能访问他们有权限查看或修改的行数据，实现更精细的数据访问控制。更多视频教程www.fgedu.net.cn

PostgreSQL RLS的主要特点：

基于行级别的访问控制
支持复杂的访问控制规则
与常规权限系统集成
可以应用于表和视图
支持多策略组合

1.2 PostgreSQL RLS的工作原理

PostgreSQL RLS的工作原理是通过在查询执行时自动添加WHERE条件来限制用户对行的访问。当启用RLS后，PostgreSQL会根据定义的安全策略，为每个查询添加相应的过滤条件，确保用户只能访问符合条件的行。

# RLS工作流程
1. 用户执行查询
2. PostgreSQL检查表是否启用了RLS
3. 如果启用了RLS，PostgreSQL根据定义的安全策略生成过滤条件
4. PostgreSQL将过滤条件添加到用户查询中
5. PostgreSQL执行修改后的查询，返回符合条件的行

# RLS策略类型
– PERMISSIVE：允许访问符合策略条件的行
– RESTRICTIVE：拒绝访问不符合策略条件的行

# RLS策略适用的操作
– SELECT：查询操作
– INSERT：插入操作
– UPDATE：更新操作
– DELETE：删除操作

1.3 PostgreSQL RLS的优势

PostgreSQL RLS的主要优势包括：

精细的访问控制：可以根据用户、角色、部门等条件限制对行的访问
简化应用代码：将数据访问控制逻辑从应用层移到数据库层，减少应用代码复杂度
统一的权限管理：通过数据库级别的策略管理，确保所有访问路径都遵循相同的权限规则
提高安全性：防止应用代码中的权限检查漏洞，确保数据安全
灵活性：支持复杂的访问控制规则，可以根据业务需求定制

Part02-生产环境规划与建议

2.1 PostgreSQL RLS规划

PostgreSQL RLS规划要点：

# RLS需求分析
– 确定需要行级访问控制的表
– 分析访问控制规则
– 确定策略类型（PERMISSIVE或RESTRICTIVE）
– 考虑策略的优先级

# RLS策略设计
– 基于用户的策略：根据当前用户限制访问
– 基于角色的策略：根据用户角色限制访问
– 基于部门的策略：根据用户部门限制访问
– 基于数据所有权的策略：根据数据所有者限制访问
– 基于时间的策略：根据时间条件限制访问

# RLS策略命名规范
– 清晰描述策略的目的
– 包含表名和策略类型
– 遵循一致的命名格式

# RLS实施计划
– 先在测试环境测试RLS
– 逐步在生产环境实施
– 监控RLS对性能的影响
– 定期审查和更新策略

2.2 PostgreSQL RLS性能考虑

PostgreSQL RLS性能考虑要点：

学习交流加群风哥QQ113257174

# RLS对性能的影响
– RLS会增加查询的复杂度
– 可能影响查询计划的生成
– 可能增加CPU和内存使用

# RLS性能优化
– 为RLS策略中的条件列创建索引
– 避免在RLS策略中使用复杂函数
– 合理设计RLS策略，避免过度复杂的条件
– 考虑使用物化视图缓存常用查询结果
– 监控RLS策略的执行计划

# RLS性能测试
– 测试不同RLS策略的性能
– 测试不同数据量下的RLS性能
– 测试并发访问下的RLS性能
– 比较启用RLS前后的性能差异

2.3 PostgreSQL RLS安全建议

PostgreSQL RLS安全建议：

# RLS安全最佳实践
– 始终为表启用RLS，除非明确不需要
– 使用RESTRICTIVE策略作为默认策略
– 为不同的操作（SELECT/INSERT/UPDATE/DELETE）定义不同的策略
– 定期审查RLS策略，确保其有效性
– 结合其他安全措施，如加密和审计

# RLS安全注意事项
– 超级用户和表所有者不受RLS限制
– 确保应用程序使用非超级用户连接数据库
– 避免在RLS策略中使用不可信的函数
– 注意RLS与视图的交互，确保视图也遵循RLS规则
– 定期检查RLS策略的有效性

风哥提示：RLS是PostgreSQL的重要安全特性，可以实现精细的行级访问控制，保护敏感数据。但需要注意RLS对性能的影响，合理设计和优化RLS策略。学习交流加群风哥微信: itpux-com

Part03-生产环境项目实施方案

3.1 PostgreSQL RLS启用与配置

3.1.1 启用RLS

# 以超级用户登录
$ psql -U pgsql

# 创建测试数据库
postgres=# CREATE DATABASE fgedu_rls_test;

CREATE DATABASE

# 切换到测试数据库
postgres=# \c fgedu_rls_test

# 创建测试表
fgedu_rls_test=# CREATE TABLE fgedu_employees (
id serial PRIMARY KEY,
name varchar(50),
department varchar(50),
salary numeric(10,2),
created_by varchar(50)
);

CREATE TABLE fgedu_# 启用RLS
fgedu_rls_test=# ALTER TABLE fgedu_employees ENABLE ROW LEVEL SECURITY;

ALTER TABLE

# 验证RLS状态
fgedu_rls_test=# \d+ fgedu_employees

3.1.2 为表所有者禁用RLS（可选）

# 为表所有者禁用RLS
fgedu_rls_test=# ALTER TABLE fgedu_employees FORCE ROW LEVEL SECURITY;

ALTER TABLE

# 验证RLS状态
fgedu_rls_test=# \d+ fgedu_employees

3.2 PostgreSQL RLS策略创建与管理

3.2.1 创建基于用户的RLS策略

# 创建测试角色
fgedu_rls_test=# CREATE ROLE hr_fgedu WITH LOGIN PASSWORD ‘hr123’;

CREATE ROLE

fgedu_rls_test=# CREATE ROLE finance_fgedu WITH LOGIN PASSWORD ‘finance123’;

CREATE ROLE

fgedu_rls_test=# CREATE ROLE fgfgfgfgsales_fgedu WITH LOGIN PASSWORD ‘fgfgfgfgsales123’;

CREATE ROLE

# 授予表权限
fgedu_rls_test=# GRANT SELECT, INSERT, UPDATE, DELETE ON fgedu_employees TO hr_fgedu, finance_fgedu, fgfgfgfgsales_fgedu;

GRANT

fgedu_rls_test=# GRANT USAGE, SELECT ON SEQUENCE fgedu_employees_id_seq TO hr_fgedu, finance_fgedu, fgfgfgfgsales_fgedu;

GRANT

# 授予数据库连接权限
fgedu_rls_test=# GRANT CONNECT ON DATABASE fgedu_rls_test TO hr_fgedu, finance_fgedu, fgfgfgfgsales_fgedu;

GRANT

# 授予模式使用权限
fgedu_rls_test=# GRANT USAGE ON SCHEMA public TO hr_fgedu, finance_fgedu, fgfgfgfgsales_fgedu;

GRANT

# 创建基于部门的RLS策略
fgedu_rls_test=# CREATE POLICY department_policy ON fgedu_employees
USING (department = current_fgedu);

CREATE POLICY

# 验证RLS策略
fgedu_rls_test=# \d+ fgedu_employees

3.2.2 创建基于操作的RLS策略

# 删除之前的策略
fgedu_rls_test=# DROP POLICY department_policy ON fgedu_employees;

DROP POLICY

# 创建基于SELECT操作的策略
fgedu_rls_test=# CREATE POLICY select_policy ON fgedu_employees
FOR SELECT
USING (department = current_fgedu OR current_fgedu = ‘hr_fgedu’);

CREATE POLICY

# 创建基于INSERT操作的策略
fgedu_rls_test=# CREATE POLICY insert_policy ON fgedu_employees
FOR INSERT
WITH CHECK (department = current_fgedu OR current_fgedu = ‘hr_fgedu’);

CREATE POLICY

# 创建基于UPDATE操作的策略
fgedu_rls_test=# CREATE POLICY update_policy ON fgedu_employees
FOR UPDATE
USING (department = current_fgedu OR current_fgedu = ‘hr_fgedu’)
WITH CHECK (department = current_fgedu OR current_fgedu = ‘hr_fgedu’);

CREATE POLICY

# 创建基于DELETE操作的策略
fgedu_rls_test=# CREATE POLICY delete_policy ON fgedu_employees
FOR DELETE
USING (department = current_fgedu OR current_fgedu = ‘hr_fgedu’);

CREATE POLICY

# 验证RLS策略
fgedu_rls_test=# \d+ fgedu_employees

3.3 PostgreSQL RLS策略测试

3.3.1 测试数据准备

# 以超级用户身份插入测试数据
fgedu_rls_test=# INSERT INTO fgedu_employees (name, department, salary, created_by) VALUES
(‘风哥1号’, ‘hr_fgedu’, 8000, ‘postgres’),
(‘风哥2号’, ‘finance_fgedu’, 9000, ‘postgres’),
(‘王五’, ‘fgfgfgfgsales_fgedu’, 10000, ‘postgres’),
(‘赵六’, ‘hr_fgedu’, 8500, ‘postgres’),
(‘钱七’, ‘finance_fgedu’, 9500, ‘postgres’),
(‘孙八’, ‘fgfgfgfgsales_fgedu’, 10500, ‘postgres’);

INSERT 0 6

# 验证测试数据
fgedu_rls_test=# SELECT * FROM fgedu_employees;

id | name | department | salary | created_by
—-+——+—————+——–+————
1 | 风哥1号 | hr_fgedu | 8000 | pgsql 2 | 风哥2号 | finance_fgedu | 9000 | pgsql 3 | 王五 | fgfgfgfgsales_fgedu | 10000 | pgsql 4 | 赵六 | hr_fgedu | 8500 | pgsql 5 | 钱七 | finance_fgedu | 9500 | pgsql 6 | 孙八 | fgfgfgfgsales_fgedu | 10500 | pgsql (6 rows)

3.3.2 测试RLS策略

# 测试hr_fgedu
$ psql -U hr_fgedu -d fgedu_rls_test

# 查询数据（应该能看到所有部门的数据）
fgedu_rls_test=> SELECT * FROM fgedu_employees;

# 插入数据
fgedu_rls_test=> INSERT INTO fgedu_employees (name, department, salary, created_by) VALUES (‘周九’, ‘hr_fgedu’, 7500, ‘hr_fgedu’);

INSERT 0 1

fgedu_rls_test=> INSERT INTO fgedu_employees (name, department, salary, created_by) VALUES (‘吴十’, ‘finance_fgedu’, 8500, ‘hr_fgedu’);

INSERT 0 1

# 测试finance_fgedu
$ psql -U finance_fgedu -d fgedu_rls_test

# 查询数据（应该只能看到finance_fgedu部门的数据）
fgedu_rls_test=> SELECT * FROM fgedu_employees;

id | name | department | salary | created_by
—-+——+—————+——–+————
2 | 风哥2号 | finance_fgedu | 9000 | pgsql 5 | 钱七 | finance_fgedu | 9500 | pgsql 8 | 吴十 | finance_fgedu | 8500 | hr_fgedu
(3 rows)

# 尝试插入其他部门的数据（应该失败）
fgedu_rls_test=> INSERT INTO fgedu_employees (name, department, salary, created_by) VALUES (‘郑十一’, ‘hr_fgedu’, 7000, ‘finance_fgedu’);

ERROR: new row violates row-level security policy for table “fgedu_employees”

# 测试fgfgfgfgsales_fgedu
$ psql -U fgfgfgfgsales_fgedu -d fgedu_rls_test

# 查询数据（应该只能看到fgfgfgfgsales_fgedu部门的数据）
fgedu_rls_test=> SELECT * FROM fgedu_employees;

# 尝试更新其他部门的数据（应该失败）
fgedu_rls_test=> UPDATE fgedu_employees SET salary = 11000 WHERE department = ‘hr_fgedu’;

UPDATE 0

# 更新自己部门的数据（应该成功）
fgedu_rls_test=> UPDATE fgedu_employees SET salary = 11000 WHERE department = ‘fgfgfgfgsales_fgedu’;

UPDATE 2

fgedu_rls_test=> SELECT * FROM fgedu_employees;

3.4 PostgreSQL RLS策略维护

3.4.1 管理RLS策略

# 列出表的RLS策略
fgedu_rls_test=# SELECT * FROM pg_policies WHERE tablename = ‘fgedu_employees’;

# 修改RLS策略
fgedu_rls_test=# ALTER POLICY select_policy ON fgedu_employees
USING (department = current_fgedu OR current_fgedu = ‘hr_fgedu’ OR current_fgedu = ‘finance_fgedu’);

ALTER POLICY

# 验证修改后的策略
fgedu_rls_test=# SELECT * FROM pg_policies WHERE tablename = ‘fgedu_employees’ AND policyname = ‘select_policy’;

# 删除RLS策略
fgedu_rls_test=# DROP POLICY delete_policy ON fgedu_employees;

DROP POLICY

# 验证策略删除
fgedu_rls_test=# SELECT * FROM pg_policies WHERE tablename = ‘fgedu_employees’;

# 禁用RLS
fgedu_rls_test=# ALTER TABLE fgedu_employees DISABLE ROW LEVEL SECURITY;

ALTER TABLE

# 验证RLS状态
fgedu_rls_test=# \d+ fgedu_employees

Part04-生产案例与实战讲解

4.1 PostgreSQL RLS实战案例：多租户数据隔离

4.1.1 案例描述

场景：一个SaaS应用，需要为不同的租户隔离数据，确保每个租户只能访问自己的数据。

4.1.2 实施方案

# 1. 创建租户表
fgedu_rls_test=# CREATE TABLE fgedu_tenants (
id serial PRIMARY KEY,
name varchar(100) UNIQUE,
description text
);

CREATE TABLE fgedu_# 2. 创建用户表，包含租户ID
fgedu_rls_test=# CREATE TABLE fgedu_fgedus (
id serial PRIMARY KEY,
tenant_id integer REFERENCES fgedu_tenants(id),
fgeduname varchar(50) UNIQUE,
password varchar(100),
email varchar(100)
);

CREATE TABLE fgedu_# 3. 创建业务表，包含租户ID
fgedu_rls_test=# CREATE TABLE fgedu_orders (
id serial PRIMARY KEY,
tenant_id integer REFERENCES fgedu_tenants(id),
fgedu_id integer REFERENCES fgedu_fgedus(id),
order_date date,
amount numeric(10,2),
status varchar(20)
);

CREATE TABLE fgedu_# 4. 启用RLS
fgedu_rls_test=# ALTER TABLE fgedu_fgedus ENABLE ROW LEVEL SECURITY;

ALTER TABLE

fgedu_rls_test=# ALTER TABLE fgedu_orders ENABLE ROW LEVEL SECURITY;

ALTER TABLE

# 5. 创建租户角色
fgedu_rls_test=# CREATE ROLE tenant1_fgedu WITH LOGIN PASSWORD ‘tenant1’;

CREATE ROLE

fgedu_rls_test=# CREATE ROLE tenant2_fgedu WITH LOGIN PASSWORD ‘tenant2’;

CREATE ROLE

# 6. 授予权限
fgedu_rls_test=# GRANT SELECT, INSERT, UPDATE, DELETE ON fgedu_fgedus TO tenant1_fgedu, tenant2_fgedu;

GRANT

fgedu_rls_test=# GRANT SELECT, INSERT, UPDATE, DELETE ON fgedu_orders TO tenant1_fgedu, tenant2_fgedu;

GRANT

fgedu_rls_test=# GRANT USAGE, SELECT ON SEQUENCE fgedu_fgedus_id_seq TO tenant1_fgedu, tenant2_fgedu;

GRANT

fgedu_rls_test=# GRANT USAGE, SELECT ON SEQUENCE fgedu_orders_id_seq TO tenant1_fgedu, tenant2_fgedu;

GRANT

fgedu_rls_test=# GRANT CONNECT ON DATABASE fgedu_rls_test TO tenant1_fgedu, tenant2_fgedu;

GRANT

fgedu_rls_test=# GRANT USAGE ON SCHEMA public TO tenant1_fgedu, tenant2_fgedu;

GRANT

# 7. 创建函数获取当前用户的租户ID
fgedu_rls_test=# CREATE OR REPLACE FUNCTION get_current_tenant_id() RETURNS integer AS $$
DECLARE
tenant_id integer;
BEGIN
SELECT tenant_id INTO tenant_id FROM fgedu_fgedus WHERE fgeduname = current_fgedu;
RETURN tenant_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE FUNCTION

# 8. 创建RLS策略
fgedu_rls_test=# CREATE POLICY tenant_isolation_policy ON fgedu_fgedus
USING (tenant_id = get_current_tenant_id());

CREATE POLICY

fgedu_rls_test=# CREATE POLICY tenant_isolation_policy ON fgedu_orders
USING (tenant_id = get_current_tenant_id());

CREATE POLICY

# 9. 插入测试数据
fgedu_rls_test=# INSERT INTO fgedu_tenants (name, description) VALUES
(‘tenant1’, ‘租户1’),
(‘tenant2’, ‘租户2’);

INSERT 0 2

fgedu_rls_test=# INSERT INTO fgedu_fgedus (tenant_id, fgeduname, password, email) VALUES
(1, ‘tenant1_fgedu’, ‘password1’, ‘tenant1@fgedu.net.cn’),
(2, ‘tenant2_fgedu’, ‘password2’, ‘tenant2@fgedu.net.cn’);

INSERT 0 2

fgedu_rls_test=# INSERT INTO fgedu_orders (tenant_id, fgedu_id, order_date, amount, status) VALUES
(1, 1, ‘2026-04-01’, 1000, ‘已完成’),
(1, 1, ‘2026-04-02’, 2000, ‘处理中’),
(2, 2, ‘2026-04-01’, 1500, ‘已完成’),
(2, 2, ‘2026-04-02’, 2500, ‘处理中’);

INSERT 0 4

# 10. 测试RLS策略
# 测试tenant1_fgedu
$ psql -U tenant1_fgedu -d fgedu_rls_test

fgedu_rls_test=> SELECT * FROM fgedu_fgedus;

fgedu_rls_test=> SELECT * FROM fgedu_orders;

id | tenant_id | fgedu_id | order_date | amount | status
—-+———–+———+————+——–+———-
1 | 1 | 1 | 2026-04-01 | 1000 | 已完成
2 | 1 | 1 | 2026-04-02 | 2000 | 处理中
(2 rows)

# 测试tenant2_fgedu
$ psql -U tenant2_fgedu -d fgedu_rls_test

fgedu_rls_test=> SELECT * FROM fgedu_fgedus;

fgedu_rls_test=> SELECT * FROM fgedu_orders;

id | tenant_id | fgedu_id | order_date | amount | status
—-+———–+———+————+——–+———-
3 | 2 | 2 | 2026-04-01 | 1500 | 已完成
4 | 2 | 2 | 2026-04-02 | 2500 | 处理中
(2 rows)

4.2 PostgreSQL RLS实战案例：部门数据隔离

4.2.1 案例描述

场景：一个企业内部系统，需要为不同部门隔离数据，确保每个部门只能访问自己部门的数据。

4.2.2 实施方案

# 1. 创建部门表
fgedu_rls_test=# CREATE TABLE fgedu_departments (
id serial PRIMARY KEY,
name varchar(50) UNIQUE,
description text
);

CREATE TABLE fgedu_# 2. 创建员工表，包含部门ID
fgedu_rls_test=# CREATE TABLE fgedu_staff (
id serial PRIMARY KEY,
department_id integer REFERENCES fgedu_departments(id),
name varchar(50),
position varchar(50),
salary numeric(10,2)
);

CREATE TABLE fgedu_# 3. 创建项目表，包含部门ID
fgedu_rls_test=# CREATE TABLE fgedu_projects (
id serial PRIMARY KEY,
department_id integer REFERENCES fgedu_departments(id),
name varchar(100),
description text,
start_date date,
end_date date
);

CREATE TABLE fgedu_# 4. 启用RLS
fgedu_rls_test=# ALTER TABLE fgedu_staff ENABLE ROW LEVEL SECURITY;

ALTER TABLE

fgedu_rls_test=# ALTER TABLE fgedu_projects ENABLE ROW LEVEL SECURITY;

ALTER TABLE

# 5. 创建部门角色
fgedu_rls_test=# CREATE ROLE dept_hr WITH LOGIN PASSWORD ‘hr123’;

CREATE ROLE

fgedu_rls_test=# CREATE ROLE dept_IT WITH LOGIN PASSWORD ‘it123’;

CREATE ROLE

# 6. 授予权限
fgedu_rls_test=# GRANT SELECT, INSERT, UPDATE, DELETE ON fgedu_staff TO dept_hr, dept_IT;

GRANT

fgedu_rls_test=# GRANT SELECT, INSERT, UPDATE, DELETE ON fgedu_projects TO dept_hr, dept_IT;

GRANT

fgedu_rls_test=# GRANT USAGE, SELECT ON SEQUENCE fgedu_staff_id_seq TO dept_hr, dept_IT;

GRANT

fgedu_rls_test=# GRANT USAGE, SELECT ON SEQUENCE fgedu_projects_id_seq TO dept_hr, dept_IT;

GRANT

fgedu_rls_test=# GRANT CONNECT ON DATABASE fgedu_rls_test TO dept_hr, dept_IT;

GRANT

fgedu_rls_test=# GRANT USAGE ON SCHEMA public TO dept_hr, dept_IT;

GRANT

# 7. 创建函数获取当前用户对应的部门ID
fgedu_rls_test=# CREATE OR REPLACE FUNCTION get_current_department_id() RETURNS integer AS $$
DECLARE
dept_id integer;
BEGIN
CASE current_fgedu
WHEN ‘dept_hr’ THEN
dept_id := 1;
WHEN ‘dept_IT’ THEN
dept_id := 2;
ELSE
dept_id := NULL;
END CASE;
RETURN dept_id;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION

# 8. 创建RLS策略
fgedu_rls_test=# CREATE POLICY department_isolation_policy ON fgedu_staff
USING (department_id = get_current_department_id());

CREATE POLICY

fgedu_rls_test=# CREATE POLICY department_isolation_policy ON fgedu_projects
USING (department_id = get_current_department_id());

CREATE POLICY

# 9. 插入测试数据
fgedu_rls_test=# INSERT INTO fgedu_departments (name, description) VALUES
(‘HR’, ‘人力资源部’),
(‘IT’, ‘信息技术部’);

INSERT 0 2

fgedu_rls_test=# INSERT INTO fgedu_staff (department_id, name, position, salary) VALUES
(1, ‘风哥1号’, ‘HR经理’, 10000),
(1, ‘风哥2号’, ‘HR专员’, 6000),
(2, ‘王五’, ‘IT经理’, 12000),
(2, ‘赵六’, ‘程序员’, 8000);

INSERT 0 4

fgedu_rls_test=# INSERT INTO fgedu_projects (department_id, name, description, start_date, end_date) VALUES
(1, ‘招聘计划’, ‘2026年招聘计划’, ‘2026-01-01’, ‘2026-12-31’),
(1, ‘培训计划’, ‘2026年培训计划’, ‘2026-01-01’, ‘2026-12-31’),
(2, ‘系统升级’, ‘ERP系统升级’, ‘2026-01-01’, ‘2026-06-30’),
(2, ‘网络改造’, ‘公司网络改造’, ‘2026-07-01’, ‘2026-12-31’);

INSERT 0 4

# 10. 测试RLS策略
# 测试dept_hr
$ psql -U dept_hr -d fgedu_rls_test

fgedu_rls_test=> SELECT * FROM fgedu_staff;

id | department_id | name | position | salary
—-+—————+——+———-+——–
1 | 1 | 风哥1号 | HR经理 | 10000
2 | 1 | 风哥2号 | HR专员 | 6000
(2 rows)

fgedu_rls_test=> SELECT * FROM fgedu_projects;

# 测试dept_IT
$ psql -U dept_IT -d fgedu_rls_test

fgedu_rls_test=> SELECT * FROM fgedu_staff;

id | department_id | name | position | salary
—-+—————+——+———-+——–
3 | 2 | 王五 | IT经理 | 12000
4 | 2 | 赵六 | 程序员 | 8000
(2 rows)

fgedu_rls_test=> SELECT * FROM fgedu_projects;

4.3 PostgreSQL RLS实战案例：用户数据隔离

4.3.1 案例描述

场景：一个个人数据管理系统，需要确保用户只能访问自己的数据，不能访问其他用户的数据。

4.3.2 实施方案

# 1. 创建用户表
fgedu_rls_test=# CREATE TABLE fgedu_personal_fgedus (
id serial PRIMARY KEY,
fgeduname varchar(50) UNIQUE,
password varchar(100),
email varchar(100)
);

CREATE TABLE fgedu_# 2. 创建个人数据表格
fgedu_rls_test=# CREATE TABLE fgedu_personal_data (
id serial PRIMARY KEY,
fgedu_id integer REFERENCES fgedu_personal_fgedus(id),
data_type varchar(50),
data_value text,
created_at timestamp DEFAULT now()
);

CREATE TABLE fgedu_# 3. 启用RLS
fgedu_rls_test=# ALTER TABLE fgedu_personal_data ENABLE ROW LEVEL SECURITY;

ALTER TABLE

# 4. 创建测试用户
fgedu_rls_test=# CREATE ROLE fgedu1 WITH LOGIN PASSWORD ‘fgedu1’;

CREATE ROLE

fgedu_rls_test=# CREATE ROLE fgedu2 WITH LOGIN PASSWORD ‘fgedu2’;

CREATE ROLE

# 5. 授予权限
fgedu_rls_test=# GRANT SELECT, INSERT, UPDATE, DELETE ON fgedu_personal_data TO fgedu1, fgedu2;

GRANT

fgedu_rls_test=# GRANT USAGE, SELECT ON SEQUENCE fgedu_personal_data_id_seq TO fgedu1, fgedu2;

GRANT

fgedu_rls_test=# GRANT CONNECT ON DATABASE fgedu_rls_test TO fgedu1, fgedu2;

GRANT

fgedu_rls_test=# GRANT USAGE ON SCHEMA public TO fgedu1, fgedu2;

GRANT

# 6. 创建函数获取当前用户的ID
fgedu_rls_test=# CREATE OR REPLACE FUNCTION get_current_fgedu_id() RETURNS integer AS $$
DECLARE
fgedu_id integer;
BEGIN
SELECT id INTO fgedu_id FROM fgedu_personal_fgedus WHERE fgeduname = current_fgedu;
RETURN fgedu_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE FUNCTION

# 7. 创建RLS策略
fgedu_rls_test=# CREATE POLICY fgedu_isolation_policy ON fgedu_personal_data
USING (fgedu_id = get_current_fgedu_id());

CREATE POLICY

# 8. 插入测试数据
fgedu_rls_test=# INSERT INTO fgedu_personal_fgedus (fgeduname, password, email) VALUES
(‘fgedu1’, ‘password1’, ‘fgedu1@fgedu.net.cn’),
(‘fgedu2’, ‘password2’, ‘fgedu2@fgedu.net.cn’);

INSERT 0 2

fgedu_rls_test=# INSERT INTO fgedu_personal_data (fgedu_id, data_type, data_value) VALUES
(1, ‘address’, ‘北京市朝阳区’),
(1, ‘phone’, ‘13800138000’),
(2, ‘address’, ‘上海市浦东新区’),
(2, ‘phone’, ‘13900139000’);

INSERT 0 4

# 9. 测试RLS策略
# 测试fgedu1
$ psql -U fgedu1 -d fgedu_rls_test

fgedu_rls_test=> SELECT * FROM fgedu_personal_data;

# 测试fgedu2
$ psql -U fgedu2 -d fgedu_rls_test

fgedu_rls_test=> SELECT * FROM fgedu_personal_data;

风哥教程针对风哥教程针对风哥教程针对生产环境建议：在生产环境中，建议结合使用RLS和其他安全措施，如加密、审计和访问控制，以确保数据的安全性。同时，需要定期审查RLS策略，确保其有效性和性能。更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 PostgreSQL RLS最佳实践

PostgreSQL RLS最佳实践：

from oracle:www.itpux.com

策略设计：

根据业务需求设计RLS策略
使用简单明了的策略条件
为不同的操作定义不同的策略
考虑策略的优先级和组合

性能优化：

为RLS策略中的条件列创建索引
避免在RLS策略中使用复杂函数
合理设计数据模型，减少RLS策略的复杂度
监控RLS策略的执行计划

安全考虑：

始终为表启用RLS，除非明确不需要
使用RESTRICTIVE策略作为默认策略
确保应用程序使用非超级用户连接数据库
定期审查RLS策略，确保其有效性

管理与维护：

使用清晰的策略命名规范
记录RLS策略的设计和变更
定期测试RLS策略的有效性
备份RLS策略配置

5.2 PostgreSQL RLS常见问题

PostgreSQL RLS常见问题及解决方案：

性能问题：为RLS策略中的条件列创建索引，优化策略条件
策略冲突：合理设计策略优先级，避免策略冲突
权限绕过：确保应用程序使用非超级用户连接数据库，启用FORCE ROW LEVEL SECURITY
策略复杂度：简化策略条件，避免使用复杂函数
迁移问题：在现有表上启用RLS时，需要考虑现有数据的访问控制
测试不足：充分测试RLS策略，确保其在各种场景下的有效性

5.3 PostgreSQL RLS性能调优

PostgreSQL RLS性能调优建议：

索引优化：

为RLS策略中的条件列创建索引
使用部分索引优化特定条件的查询
定期更新统计信息，确保查询优化器选择正确的执行计划

策略优化：

使用简单的策略条件，避免复杂函数
合理使用PERMISSIVE和RESTRICTIVE策略
避免过度使用RLS策略，只在必要的表上启用

查询优化：

优化查询语句，减少不必要的列和行访问
使用合适的JOIN策略，避免复杂的关联查询
考虑使用物化视图缓存常用查询结果

系统配置：

调整PostgreSQL的内存配置，提高查询性能
使用合适的存储设备，提高I/O性能
考虑使用连接池，减少连接开销

风哥提示：RLS是PostgreSQL的强大安全特性，可以实现精细的行级访问控制。合理设计和优化RLS策略，可以在保证数据安全的同时，不会显著影响性能。from PostgreSQL:www.itpux.com

持续改进：RLS策略的设计和优化是一个持续的过程，需要根据业务需求和性能表现不断调整。建议建立定期审查机制，持续改进RLS策略的设计和性能。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

PostgreSQL教程FG082-PG行级安全策略：RLS配置与实战

Part01-基础概念与理论知识

1.1 PostgreSQL行级安全策略（RLS）的概念

1.2 PostgreSQL RLS的工作原理

1.3 PostgreSQL RLS的优势

Part02-生产环境规划与建议

2.1 PostgreSQL RLS规划

2.2 PostgreSQL RLS性能考虑

2.3 PostgreSQL RLS安全建议

Part03-生产环境项目实施方案

3.1 PostgreSQL RLS启用与配置

3.1.1 启用RLS

3.1.2 为表所有者禁用RLS（可选）

3.2 PostgreSQL RLS策略创建与管理

3.2.1 创建基于用户的RLS策略

3.2.2 创建基于操作的RLS策略

3.3 PostgreSQL RLS策略测试

3.3.1 测试数据准备

3.3.2 测试RLS策略

3.4 PostgreSQL RLS策略维护

3.4.1 管理RLS策略

Part04-生产案例与实战讲解

4.1 PostgreSQL RLS实战案例：多租户数据隔离

4.1.1 案例描述

4.1.2 实施方案

4.2 PostgreSQL RLS实战案例：部门数据隔离

4.2.1 案例描述

4.2.2 实施方案

4.3 PostgreSQL RLS实战案例：用户数据隔离

4.3.1 案例描述

4.3.2 实施方案

Part05-风哥经验总结与分享

5.1 PostgreSQL RLS最佳实践

5.2 PostgreSQL RLS常见问题

5.3 PostgreSQL RLS性能调优

相关推荐

联系我们