PostgreSQL应该怎样实现安全管控？

Whoami：5年+金融、政府、医疗领域工作经验的DBA
Certificate：OCP、PCP
Skill：Oracle、Mysql、PostgreSQL
Platform：CSDN、墨天伦、公众号（呆呆的私房菜）

安全建议：
1. 规避trust认证方法；
2. 超级用户只允许本地登录，不允许网络连接；
3. 将dbname+username+ip限制到最小；
4. 使用最小范围鉴权，避免使用all、0.0.0.0/0；
5. 密码认证建议使用md5或scram加密方法；
6. 可以配置auth_delay扩展限制用户错误密码登录次数，防止暴力破解或者DDos攻击。

# 如果设置了password_encryption=md5，但创建密码不加encrypted，那么密码将会明文存储
create role <rolename> [[WITH ] option [ ... ] ] | [ ENCRYPTED | UNENCRYPTED ] PASSWORD '<password>';

alter user rep valid until '2024-08-10 00:00:01';

# 数据库安装 passwordcheck 插件
create extension passwordcheck;# 创建用户或修改用户密码时，强制限制密码复杂度，限制密码不能重复使用等

# 避免命令行明文密码的出现
# 使用域认证或第三方认证方法

# 清空记录的sql
select pg_stat_statements_reset();
# 关闭ddl记录功能
pg_stat_statements.track_utility=off

1. 如果网络环境无法保证绝对安全，建议使用加密传输，例如openssl。
2. 修改默认端口
3. 配置防火墙策略
4. 使用ssh隧道技术

1. 敏感数据加密后存储在数据库中，即使加密数据泄露，只要加解密方法没有泄露，也是相对安全的；
2. 建议加解密方法放在应用端实现；
3. pgcrypto模块提供了密码函数。
create extension pgcrypto;

1. 使用超级用户创建所需的数据库、模式、表、索引、函数；
2. 创建应用账户角色，将数据库、模式的使用权限授权给应用账户；
3. 应用账户授予表的对应dml权限，函数的execute权限等，注意严格控制drop、truncate、replace等权限；
4. 通过列权限或视图来限制应用账户的权限；
5. 回收数据库、模式、表、函数等的public权限。
revoke all on database <dbname> from public;
revoke all on schema <schemaname> from public;
revoke all on table <tablename> from public;
revoke all on function <functionname> from public;
6. 回收函数语言的public权限，限制普通用户不能创建函数
revoke all on language plpgsql from public;

限制普通用户只能操作表中指定条件的记录
1. PostgreSQL 9.5新增的特性，不同用户可以看到表中的不同数据
postgres=# \h CREATE POLICY
命令：CREATE POLICY
描述：为一个表定义一条新的行级安全性策略
语法：
CREATE POLICY 名称 ON 表名
    [ AS { PERMISSIVE | RESTRICTIVE } ]
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { 角色名称 | PUBLIC | CURRENT_ROLE | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( 使用表达式 ) ]
    [ WITH CHECK ( 检查表达式 ) ]


URL: https://www.postgresql.org/docs/16/sql-createpolicy.html

1. 通过事件触发器禁止应用账户执行ddl，防止被攻击后，用户drop或truncate删除对象或清空数据；
CREATE OR REPLACE FUNCTION prevent_ddl()
RETURNS event_trigger AS $$
BEGIN
    -- 检查语句是否是 DDL 并且执行者是受限用户
    IF TG_TAG IN ('CREATE', 'ALTER', 'DROP', 'TRUNCATE') AND CURRENT_USER = '受限用户名' THEN
        RAISE EXCEPTION '用户 % 不允许执行 DDL 语句', CURRENT_USER;
    END IF;
    RETURN;
END;
$$ LANGUAGE plpgsql;


-- 创建事件触发器
CREATE EVENT TRIGGER prevent_ddl_trigger
ON ddl_command_start
EXECUTE FUNCTION prevent_ddl();


2. 防止执行不带条件的delte、update，在需要保护的表里边，新增一条dummy记录，创建行触发器，当这条记录被更新或删除时，抛出异常。

1. 跟踪postgresql.conf配置文件变更；
2. 跟踪表的创建时间和表定义的修改时间；
3. 跟踪表的记录被哪个用户插入或修改；

1. 基本的语句记录可以由标准日志记录工具 log_statement=all 提供；
2. 可以使用pg_audit扩展来获得详细的会话或对象审计日志记录；
3. 可以使用hstore扩展和触发器来跟踪表的行记录变更。

社区bug动态跟踪：
http://git.postgresql.org

1. 在事务提交时等待所有的数据都写入到磁盘，确保所有的更改都被写入到磁盘后才会向客户端返回提交成功的响应
synchronous_commit = on：


2. 每次写操作后调用操作系统的 fsync 函数，强制将数据从操作系统的缓存写入到磁盘
fsync = on


3. 将整个 8KB 的页面数据写入到磁盘，即使只有页面的一部分被修改
full_page_writes = on

1. 关闭磁盘的write cache
2. 只允许有断电保护的write cache