PostgreSQL安全修葺

曾写过一篇文章：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 

如果不先生成key，会出现如下的错误提示：

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)恢复

PITR恢复后是否安全包含最新的业务数据呢？这篇文章《The Rest is History: Investigations of WAL History Files》进行了详细调查取证：

pg_wal(pg_xlog)子目录中有时间线历史文件，时间线历史文件加上WAL文件分析可以告诉我们数据库“家谱”的故事，让我们可以更安全的选择在哪个时间线上恢复包含对业务最有用的数据。

恢复流程步骤参考如下：

• 查看pg_controldata，查看数据库当前处于什么时间线
• 查看$PGDATA/pg_wal/*.history中的WAL历史文件
• 如有必要，从备份存储库/位置恢复丢失的*.history文件
• 看看时间线是什么时候创建的
• 检查WAL的内容

系统元数据

普通的数据库对象从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故障及数据恢复案例

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中存在预备事务、活动的复制槽或订阅，则删除会失败。

删用户

创建用户时我们需要注意用户口令不被明文记录，而删除用户时我们需要注意如下几点：

• 删除表、视图相关owner对象信息
• 删除FDW相关信息
• 删除权限配置信息(schema及表）
• 删除default privileges信息

另外也可以使用reassign owned安全的对象的宿主及权限信息

REASSIGN OWNED BY old_role TO new_role; 

如果确认对象不需要，即便对象上有grant信息，也可使用drop owned批量删除对象。

DROP OWNED BY ... 

修改hba

PG里手工修改pg_hba.conf文件需要注意格式及内容，否则下次重启数据库服务时可能失败。

openGauss系的数据库有提供SQL接口函数来修改pg_hba.conf，期望该功能也可加入到PG社区中。

打开checksum

PG提供了checksum可以保护数据发生损坏，checksum默认是关闭的，可以在initdb时使用–data-checksums打开，打开后会带来大约2%的cpu负载。

除了在initdb时设置，已有实例可使用pg_checksums工具(12引入)进行开关控制：开启(-e,–enable)和关闭(-d,–disable)，使用pg_checksums工时需要关闭PG服务。

如果备份的数据有损坏，还可以使用pg_verifybackup工具进行检测。

主备多节点的环境，可以不停机进行操作，如下步骤：

• 停一个standby节点
• 运行pg_checksum --enable
• 启动standby节点，追数据
• 对所有的standby重复执行以上3个步骤
• switchover切换
• 对原主执行1-3

四、权限策略

超级用户权限下放

具有Superuser属性的用户具有最高权限，高权限易出现一些误操作，PG一直在降低对超级用户权限的依赖，从PG 9.6开始，系统提供预置角色对需要经常使用的功能进行权限访问。

9.6版本下放对后端进程取消或中止的操作：pg_signal_backend

10版本下放了监控相关的操作：pg_monitor，它包含如下三个监控相关的权限。

• pg_read_all_settings：可以读取所有的配置变量。
• pg_read_all_stats：可以读取pg_stat_*视图信息，以及相关的扩展统计信息。
• pg_stat_scan_tables：可以执行一些监控函数。

11版本下放COPY相关的操作：

• pg_read_server_files：控制COPY以及文件访问函数读文件的权限。
• pg_write_server_files：控制COPY以及文件访问函数写文件的权限。
• pg_execute_server_program：控制COPY执行程序的权限。

11版本还支持pg_rewind工具依赖的文件读取函数下放，可通过grant将权限赋给普通用户。

14版本下放全局读写的操作：

• pg_database_owner：提供数据库宿主的通用权限，常见于模板库的定制化工作。
• pg_read_all_data：提供全局读取的访问权限，实例级全局只读用户。
• pg_write_all_data：提供全局写入的访问权限，实例级全局insert、update及delete。

15版本下放checkpoint操作：pg_checkpoint执行checkpoint操作。

16版本下放逻辑复制操作权限：

• 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.

An internal counter keeps track of how many times the statement has been executed and when it reaches the prepareThreshold (default 5) the driver will switch to creating a named statement and using Prepare and Execute.

PG 16在psql工具里支持v3版本扩展查询协议(使用元命令\bind)。

SSL协议版本

使用下面两个参数可控制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属性之后，系统将检查用户对视图相关对象是否有权限，而不是只检查视图的owner权限。

下面的示例由于没有对tab_base表赋予权限给test用户，所以最后的查询语句提示无权限。

postgres=# alter view my_view set (security_invoker = on); 

ALTER VIEW 

postgres=# \c - test 

You are now connected to database "postgres" as user "test". 

postgres=> select * from my_view ; 

ERROR: permission denied for table tab_base 

视图security_invoker的使用场景是当我们不想以视图owner进行权限检测，类似函数的security invoker方式(调用者权限方式）。

另外如果视图引用的对象开启行级安全特性，如果是以owner方式进行检测，则行级安全策略会被跳过。因此security_invoker视图需要与行级安全特性结合使用。

六、自定义参数

PG 15里自定义参数名称不能与数据库中已经安装的扩展插件名称相同。比如我们数据库中使用了外部表插件postgres_fdw，那我们使用postgres_fdw.作为前缀来定义变量时，只要扩展插件加载运行，就会提示一个告警信息，后面将不能使用这个变量。

七、public模式

在PG里面允许用户在public模式下创建表(或者视图等)。PG15对public模式的create 权限从public角色回收，普通用户将不能在public模式下创建表。

八、初始用户

PG 16里初始用户不允许移除超级用户权限，示例如下：

postgres=# alter user postgres nosuperuser; 

ERROR: permission denied to alter role DETAIL: The bootstrap user must have the SUPERUSER attribute. 

初始用户是许多重要系统对象的宿主，即便它没有超级用户权限也可以通过修改pg_catalog模式下的系统表来获得超级用户权限，初始用户允许移除超级用户权限并不符合安全预期。

九、数据动态脱敏

PG里可以使用Anonymizer插件来进行数据脱敏：PostgreSQL数据脱敏插件介绍

十、可信的扩展

从PG 13开始，extension增加了trusted可信标识，创建可信标识的模块不需要超级用户权限。

最后：当前这个最好的时代亦或最坏的时代，PG依然值得我们学习。

保持联系

本人组建了一个技术交流群：PG乐知乐享交流群。欢迎关注文章的小伙伴随缘加入，进群请加本人微信skypkmoon并备注PG乐知乐享。