PostgreSQL实战指南：多领域数据库应用与实践 - 第26章：安全性管理与权限控制

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

# /etc/postgresql/14/main/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# 本地 Unix socket 连接使用 peer 认证
local   all             all                                     peer

# 来自本地网络的 IPv4 连接，要求提供安全的密码
host    all             all             192.168.1.0/24          scram-sha-256

# 来自特定应用服务器的连接
host    my_app_db       app_user        192.168.1.100/32        scram-sha-256

# 拒绝所有其他连接
host    all             all             0.0.0.0/0               reject

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

-- 1. 创建一个只读角色
CREATE ROLE readonly_role;
GRANT CONNECT ON DATABASE my_db TO readonly_role;
GRANT USAGE ON SCHEMA public TO readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
-- 对于未来新建的表，也自动授予 SELECT 权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_role;

-- 2. 创建一个只写角色
CREATE ROLE writeonly_role;
GRANT CONNECT ON DATABASE my_db TO writeonly_role;
GRANT USAGE ON SCHEMA public TO writeonly_role;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO writeonly_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE, DELETE ON TABLES TO writeonly_role;

-- 3. 创建具体用户，并授予角色
CREATE USER analyst_user WITH PASSWORD 'secure_password';
CREATE USER app_user WITH PASSWORD 'another_secure_password';

GRANT readonly_role TO analyst_user;
GRANT writeonly_role TO app_user;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

-- 1. 准备表和数据
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    tenant_id TEXT NOT NULL,
    content TEXT
);

INSERT INTO documents (tenant_id, content) VALUES
('tenant_a', 'Document 1 for A'),
('tenant_a', 'Document 2 for A'),
('tenant_b', 'Document 1 for B');

-- 2. 创建用户，并使用 set_config 设置其租户ID
CREATE USER user_a WITH PASSWORD 'pass_a';
CREATE USER user_b WITH PASSWORD 'pass_b';

-- 3. 在表上启用 RLS
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
-- 注意：默认情况下，表的所有者(通常是超级用户)不受 RLS 限制。
-- 如果想让所有者也受限，使用 ALTER TABLE ... FORCE ROW LEVEL SECURITY;

-- 4. 创建安全策略
-- 这个策略会检查当前会话的 'app.tenant_id' 变量是否与行中的 tenant_id 列匹配
CREATE POLICY tenant_isolation_policy ON documents
    FOR ALL -- 对 SELECT, INSERT, UPDATE, DELETE 都生效
    USING (tenant_id = current_setting('app.tenant_id', true))
    WITH CHECK (tenant_id = current_setting('app.tenant_id', true));

-- 'WITH CHECK' 子句对 INSERT 和 UPDATE 生效，防止用户插入或更新不属于自己租户的数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 以 user_a 的身份连接并设置会话变量
SET app.tenant_id = 'tenant_a';

-- user_a 执行查询
SELECT * FROM documents;
-- 结果:
-- id | tenant_id | content
-- ---|-----------|------------------
--  1 | tenant_a  | Document 1 for A
--  2 | tenant_a  | Document 2 for A
-- (user_a 看不到 tenant_b 的数据)

-- user_a 尝试插入错误的数据 (将会失败)
-- INSERT INTO documents (tenant_id, content) VALUES ('tenant_b', 'Malicious data');
-- ERROR:  new row violates WITH CHECK OPTION for policy "tenant_isolation_policy"