曾写过一篇文章：PostgreSQL安全加固，文章简单介绍了一些加固的方式，本文将重新翻修，加强警戒。
一、口令相关
PG 10对password_encryption参数增加了scram-sha-256的加密方式，但为了保持兼容，默认使用md5加密，直到PG 14加密方式才变为scram-sha-256。从PG 10到PG 13建议调整password_encryption参数为scram-sha-256，并重新设置口令。同时注意在PG 14之前scram-sha-256加密口令的长度不能超过1024个字符，从PG 14开始限制取消。
当修改用户名称后，如果用户的口令采用md5加密，则密码会被清空，需要重新设置；而sha256方式加密的用户可以原密码登录，建议用户名修改后，应重设一下口令。
PG里使用外部表时，需要明文存储外部server的用户口令到系统表(pg_user_mappings)。 使用postgres_fdw插件创建用户映射时，可以利用password_required选项(PG 13开始支持)，允许免密认证进行连接，避免口令明文存储到系统表中。 openGauss提供的解决方案是：先通过gs_guc generate命令生成key，再利用key加密存储。$ gs_guc generate -S Admin@1234 -D /$GAUSSHOME/bin -o usermapping
ERROR: No key file usermapping.key.cipher
二、备份及恢复相关
PG目前并未对逻辑备份的数据提供加密存储，openGauss考虑了这个场景，下面的备份命令：$ gs_dump -Fp --with-encryption=AES128 --with-key=Enmote@2024 -U omm -f tmp1.sql postgres
使用--with-encryption=AES128选项对数据进行AES128加密，使用--with-key=Enmote@2024设置key，恢复时使用--with-key解密。$ gsql --with-key=Enmote@2024 -U omm testdb -f tmp1.sql
PITR恢复后是否安全包含最新的业务数据呢？这篇文章《The Rest is History: Investigations of WAL History Files》进行了详细调查取证。 pg_wal(pg_xlog)子目录中有时间线历史文件，时间线历史文件加上WAL文件分析可以告诉我们数据库“家谱”的故事，让我们可以更安全的选择在哪个时间线上恢复包含对业务最有用的数据。
- 查看pg_controldata，查看数据库当前处于什么时间线
- 查看$PGDATA/pg_wal/*.history中的WAL历史文件
- 如有必要，从备份存储库/位置恢复丢失的*.history文件
-
普通的数据库对象从pg_class的oid与relfilenode字段可以看到关联信息，而数据库自身的元数据对象并不能直接看到关联信息，但可以通过如下语句查看：
SELECT oid,relname,pg_relation_filepath(oid) FROM pg_class WHERE relfilenode=0 AND pg_relation_filepath(oid) like 'base_%';
系统元数据对象信息其实存储在pg_filenode.map文件中，这是很重要的一个文件，下面这个恢复案例遇到过该文件缺失处理的过程：[PostgreSQL故障及数据恢复案例](https://www.modb.pro/db/483640) openGauss里对这个文件进行了备份：每个db下对pg_filenode.map文件增加了一个pg_filenode.map.backup文件进行备份，服务启动时会进行检测，发生缺失也会自动创建，发生修改时也会自动同步。三、日常维护相关
如需新建表空间，需要确保表空间目录不应该存放在PGDATA目录下。开发测试环境需要使用自定义表空间，可通过allow_in_place_tablespaces参数开启表空间"in place"功能，将数据直接存储在pg_tblspc目录里面。
当某个db存在活动会话时，删数据库的操作将不被允许。从PG 13开始，对数据库删除操作提供了force删除选项，使用force删除选项时，系统会先自动结束db上的活动会话，然后再删除数据库。
DROP DATABASE testdb WITH (force);
注意如果db中存在预备事务、活动的复制槽或订阅，则删除会失败。
创建用户时我们需要注意用户口令不被明文记录，而删除用户时我们需要注意如下几点： 或者使用reassign owned安全的转移用户的宿主信息。
REASSIGN OWNED BY old_role TO new_role;
如果确认宿主及权限信息不再需要，亦可使用drop owned批量删除。
PG里手工修改pg_hba.conf文件需要注意格式及内容，否则下次重启数据库服务时可能失败。 openGauss系数据库有提供SQL接口函数来修改pg_hba.conf，期望该功能也可加入到PG社区中。四、权限策略
具有Superuser属性的用户具有最高权限，高权限易出现一些误操作，PG一直在降低对超级用户权限的依赖，从PG 9.6开始，系统提供预置角色对需要经常使用的功能进行权限访问。9.6版本下放对后端进程取消或中止的操作：pg_signal_backend10版本下放了监控相关的操作：pg_monitor，它包含如下三个监控相关的权限。
- pg_read_all_settings：可以读取所有的配置变量。
- pg_read_all_stats：可以读取pg_stat_*视图信息，以及相关的扩展统计信息。
- pg_stat_scan_tables：可以执行一些监控函数。
- pg_read_server_files：控制COPY以及文件访问函数读文件的权限。
- pg_write_server_files：控制COPY以及文件访问函数写文件的权限。
- pg_execute_server_program：控制COPY执行程序的权限。
11版本还支持pg_rewind工具依赖的文件读取函数下放，可通过grant将权限赋给普通用户。
- pg_database_owner：提供数据库宿主的通用权限，常见于模板库的定制化工作。
- pg_read_all_data：提供全局读取的访问权限，实例级全局只读用户。
- pg_write_all_data：提供全局写入的访问权限，实例级全局insert、update及delete。
15版本下放了checkpoint操作：pg_checkpoint执行checkpoint操作。
- pg_create_subscription角色允许执行create subscription命令。
- pg_use_reserved_connections角色允许用户使用reserved_connections参数设置的连接数来执行一些后台运维及管理操作而无须担心数据库连接被应用程序占满而无法工作。
当表存在多个策略时，PG 10支持指定策略为宽容性(PERMISSIVE)策略，或限制性(RESTRICTIVE)策略。
- PERMISSIVE：多条策略都适用时通过OR条件组合在一起。
- RESTRICTIVE：多条策略都适用时通过AND条件组合在一起。
五、防攻击
PGJDBC里使用PreparedStatement对象，当参数prepareThreshold = 5，5次后PreparedStatement切换到服务端的PREPARE和EXECUTE。 使用PREPARE和EXECUTE实现server prepared statements比较通用，但会引起查询结果可见性变化：例如更新的count数、 ResultSet元数据。The current driver uses the V3 protocol-level equivalents which avoid these changes in query results. The Extended Query protocol prepares a temporary “unnamed statement”. See Extended Query Section 53.2.3 for details.
PG 16在psql工具里支持v3版本扩展查询协议(使用元命令\bind)。
使用下面两个参数可控制TLS版本，在特定协议版本上进行开发或功能测试比较方便。- ssl_min_protocol_version：控制使用SSL协议的最小版本。- ssl_max_protocol_version：控制使用SSL协议的最大版本。
当我们编写完的函数，PG在函数的执行上给我们提供两种安全环境：第一种是security invoker，以调用者权限执行函数，这是默认的安全环境。另一种环境是security definer，以创建者权限执行函数，创建者权限在函数执行期间生效。 security invoker方式普通用户定义的函数体如果包含高危操作时，虽然自己没有权限执行，但如果被高权限的管理用户执行会触发函数陷阱。而security definer则可以把明确的高权限操作以创建者权限给用户提高权限。
视图默认的安全上下文环境使用security definer，也就是使用视图的owner去检测相关对象的权限。 PG 15对视图增加的security invoker属性，当视图增加security_invoker属性之后，系统将检查用户对视图相关对象（示例中的tab_base表）是否有权限，而不是只检查视图的owner权限。 视图security_invoker属性的使用场景是当我们不想以视图owner进行权限检测，类似函数的security invoker方式(调用者权限方式）。 另外如果视图引用的对象开启行级安全特性，如果是以owner方式进行检测，则行级安全策略会被跳过。因此security_invoker视图需要与行级安全特性结合使用。六、自定义参数
从PG 15开始，自定义参数名称不能与数据库中已经安装的扩展插件名称相同。比如我们数据库中使用了外部表插件postgres_fdw，那我们使用postgres_fdw.作为前缀来定义变量时，只要扩展插件加载运行，就会提示一个告警信息，后面将不能使用这个变量。七、public模式
在PG里面允许用户在public模式下创建表(或者视图等)。PG15对public模式的create 权限从public角色回收，普通用户将不能在public模式下创建表。八、初始用户
PG 16里初始用户不允许移除超级用户权限，因为初始用户是许多重要系统对象的宿主，即便它没有超级用户权限也可以通过修改pg_catalog模式下的系统表来获得超级用户权限，初始用户允许移除超级用户权限并不符合安全预期。九、数据动态脱敏
PG里可以使用Anonymizer插件来进行数据脱敏，参考文章：[PostgreSQL数据脱敏插件介绍](https://www.modb.pro/db/592146)十、可信的扩展
从PG 13开始，extension增加了trusted可信标识，创建可信标识的模块不需要超级用户权限。结语：当前这个最好的时代亦或最坏的时代，PG依然值得我们学习。
原文作者：象楚之行；如有侵权，请联系管理员删除。
