PostgreSQL教程

安全模型核心概念

• 角色（Role）：PostgreSQL中的基本安全实体，可以是用户或用户组
• 权限（Privilege）：对数据库对象执行特定操作的权利
• 对象（Object）：数据库中的实体，如表、视图、函数等
• 模式（Schema）：数据库对象的逻辑容器
• 继承（Inheritance）：角色可以继承其他角色的权限

PostgreSQL中的主要权限类型：

创建和管理角色

-- 创建普通用户角色
CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password';

-- 创建管理员角色
CREATE ROLE app_admin WITH LOGIN PASSWORD 'admin_password' CREATEDB CREATEROLE;

-- 创建应用角色（无登录权限）
CREATE ROLE app_role;

-- 创建具有特定属性的角色
CREATE ROLE backup_user WITH LOGIN PASSWORD 'backup_password' REPLICATION;

-- 修改角色属性
ALTER ROLE app_user WITH NOSUPERUSER NOCREATEDB;
ALTER ROLE app_user VALID UNTIL '2025-12-31';

-- 设置角色的连接限制
ALTER ROLE app_user CONNECTION LIMIT 10;

-- 删除角色
DROP ROLE IF EXISTS old_user;

角色属性详解

-- 创建具有不同属性的角色
-- 超级用户（具有所有权限）
CREATE ROLE super_user WITH LOGIN PASSWORD 'password' SUPERUSER;

-- 可以创建数据库的角色
CREATE ROLE db_creator WITH LOGIN PASSWORD 'password' CREATEDB;

-- 可以创建角色的角色
CREATE ROLE role_creator WITH LOGIN PASSWORD 'password' CREATEROLE;

-- 可以创建复制的角色
CREATE ROLE replica_user WITH LOGIN PASSWORD 'password' REPLICATION;

-- 可以绕过行级安全策略的角色
CREATE ROLE bypass_rls_user WITH LOGIN PASSWORD 'password' BYPASSRLS;

-- 设置角色的默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO app_user;

角色继承

-- 创建基础角色
CREATE ROLE base_role;

-- 授予基础角色权限
GRANT SELECT, INSERT ON TABLE users TO base_role;

-- 创建继承角色
CREATE ROLE junior_user WITH LOGIN PASSWORD 'password';
CREATE ROLE senior_user WITH LOGIN PASSWORD 'password';

-- 设置角色继承关系
GRANT base_role TO junior_user;
GRANT base_role TO senior_user;

-- 为高级用户授予额外权限
GRANT UPDATE, DELETE ON TABLE users TO senior_user;

-- 查看角色成员关系
SELECT 
    r.rolname AS role_name,
    m.rolname AS member_name
FROM pg_auth_members am
JOIN pg_roles r ON am.roleid = r.oid
JOIN pg_roles m ON am.member = m.oid
WHERE r.rolname = 'base_role';

-- 在会话中设置当前角色
SET ROLE senior_user;
-- 执行操作
RESET ROLE;  -- 重置角色

表权限管理

-- 授予表权限
GRANT SELECT ON TABLE users TO app_user;
GRANT INSERT, UPDATE, DELETE ON TABLE users TO app_admin;
GRANT ALL PRIVILEGES ON TABLE users TO super_user;

-- 授予多个表的权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_user;
GRANT INSERT, UPDATE ON ALL TABLES IN SCHEMA sales TO sales_team;

-- 授予序列权限
GRANT USAGE, SELECT, UPDATE ON SEQUENCE users_id_seq TO app_user;

-- 授予多个序列的权限
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- 回收权限
REVOKE INSERT, UPDATE, DELETE ON TABLE users FROM app_user;
REVOKE ALL PRIVILEGES ON TABLE users FROM PUBLIC;  -- 回收公共权限

-- 回收多个表的权限
REVOKE INSERT, UPDATE ON ALL TABLES IN SCHEMA sales FROM sales_team;

模式权限管理

-- 创建模式
CREATE SCHEMA sales;
CREATE SCHEMA hr;

-- 授予模式使用权限
GRANT USAGE ON SCHEMA sales TO sales_team;
GRANT USAGE ON SCHEMA hr TO hr_team;

-- 授予模式对象创建权限
GRANT CREATE ON SCHEMA sales TO sales_admin;
GRANT CREATE ON SCHEMA hr TO hr_admin;

-- 授予默认权限（对新创建的对象自动生效）
ALTER DEFAULT PRIVILEGES IN SCHEMA sales GRANT SELECT ON TABLES TO sales_team;
ALTER DEFAULT PRIVILEGES IN SCHEMA hr GRANT SELECT, INSERT, UPDATE ON TABLES TO hr_team;

-- 回收模式权限
REVOKE CREATE ON SCHEMA sales FROM sales_admin;
REVOKE USAGE ON SCHEMA sales FROM PUBLIC;

函数和过程权限

-- 创建示例函数
CREATE OR REPLACE FUNCTION get_user_info(user_id INTEGER)
RETURNS TABLE(id INTEGER, username VARCHAR(50), email VARCHAR(100))
AS $$
BEGIN
    RETURN QUERY SELECT u.id, u.username, u.email FROM users u WHERE u.id = user_id;
END;
$$ LANGUAGE plpgsql;

-- 授予函数执行权限
GRANT EXECUTE ON FUNCTION get_user_info(INTEGER) TO app_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO app_admin;

-- 回收函数执行权限
REVOKE EXECUTE ON FUNCTION get_user_info(INTEGER) FROM app_user;

-- 对存储过程的权限管理
CREATE OR REPLACE PROCEDURE update_user_email(user_id INTEGER, new_email VARCHAR(100))
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE users SET email = new_email WHERE id = user_id;
END;
$$;

GRANT EXECUTE ON PROCEDURE update_user_email(INTEGER, VARCHAR) TO app_admin;
REVOKE EXECUTE ON PROCEDURE update_user_email(INTEGER, VARCHAR) FROM PUBLIC;

启用和配置行级安全

-- 创建示例表
CREATE TABLE employee_data (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    personal_info TEXT
);

-- 启用表的行级安全
ALTER TABLE employee_data ENABLE ROW LEVEL SECURITY;

-- 创建策略：员工只能查看自己部门的数据
CREATE POLICY emp_dept_policy ON employee_data
FOR SELECT
TO app_user
USING (department = current_user);

-- 创建策略：管理员可以查看所有数据
CREATE POLICY admin_policy ON employee_data
FOR ALL
TO app_admin
USING (true);

-- 创建策略：限制插入操作
CREATE POLICY insert_policy ON employee_data
FOR INSERT
TO app_user
WITH CHECK (department = current_user);

-- 创建策略：限制更新操作
CREATE POLICY update_policy ON employee_data
FOR UPDATE
TO app_user
USING (department = current_user)
WITH CHECK (department = current_user);

-- 查看表的策略
SELECT 
    polname AS policy_name,
    rolname AS role_name,
    polcmd AS command,
    polqual AS using_clause,
    polwithcheck AS check_clause
FROM pg_policy p
JOIN pg_roles r ON p.polroles[1] = r.oid
WHERE p.polrelid = 'employee_data'::regclass;

-- 禁用行级安全
ALTER TABLE employee_data DISABLE ROW LEVEL SECURITY;

-- 删除策略
DROP POLICY IF EXISTS emp_dept_policy ON employee_data;

pg_hba.conf配置示例

# pg_hba.conf 文件示例
# TYPE  DATABASE        USER            ADDRESS                 METHOD

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

# 本地连接使用md5密码认证
local   all             all                                     md5

# IPv4本地连接
host    all             all             127.0.0.1/32            md5

# IPv6本地连接
host    all             all             ::1/128                 md5

# 允许特定用户从特定IP连接
host    myapp           app_user        192.168.1.0/24          md5

# 允许管理员从任何地方连接（使用SSL）
hostssl all             admin_user      0.0.0.0/0               cert

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

# 复制连接
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

修改pg_hba.conf后需要重新加载配置：

-- 在数据库中重新加载配置
SELECT pg_reload_conf();

-- 或者在命令行中
pg_ctl reload -D /path/to/data/directory

SSL连接配置

-- 在postgresql.conf中启用SSL
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'ca.crt'

-- 强制使用SSL连接
-- 在pg_hba.conf中配置
hostssl all             all             0.0.0.0/0               md5

-- 客户端连接时指定SSL模式
-- psql连接示例
psql "host=localhost dbname=mydb user=myuser sslmode=require"

-- 在应用程序中使用SSL连接
-- JDBC连接字符串示例
jdbc:postgresql://localhost:5432/mydb?ssl=true&sslmode=require

配置审计日志

-- 在postgresql.conf中配置日志参数
log_statement = 'mod'  -- 记录DDL和DML语句
log_duration = on      -- 记录语句执行时间
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_min_duration_statement = 1000  -- 记录执行时间超过1秒的语句

-- 记录连接和断开连接
log_connections = on
log_disconnections = on

-- 记录锁等待
log_lock_waits = on

-- 创建审计表
CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    username TEXT,
    operation TEXT,
    table_name TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    client_ip INET
);

-- 创建审计触发器函数
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO audit_log (username, operation, table_name, client_ip)
        VALUES (USER, TG_OP, TG_TABLE_NAME, inet_client_addr());
        RETURN OLD;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO audit_log (username, operation, table_name, client_ip)
        VALUES (USER, TG_OP, TG_TABLE_NAME, inet_client_addr());
        RETURN NEW;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO audit_log (username, operation, table_name, client_ip)
        VALUES (USER, TG_OP, TG_TABLE_NAME, inet_client_addr());
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 为敏感表创建审计触发器
CREATE TRIGGER user_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();