postgres用户凭空消失了...

Postgres用户是PG数据库初始化创建的superuser，也是模板库 template0/template1的owner。如果postgres用户突然凭空消失了，该如何应对？

最近收到一线求助，说是1个月左右前客户pg数据库由于上线了一套安全卫士系统，然后数据库postgres用户属主显示有问题，如下：

从截图可以看到，数据库只影响系统模板库(postgres)，业务数据库不受影响。

1）由于一线每天都有完整的备份，因此不用担心数据丢失问题，但是这个问题一直在这始终是个地雷，万一什么时候爆发了就成事故；

2）同时，高可用备机也同样出现了类似问题，这意味着主库的故障同步“污染”了备机；

3）一线最近一段时间只有这个安全卫士变更上线，极有可能是问题的“罪魁祸首”。

针对目前问题，考虑2个解决方案：

1）数据备份恢复方式：重建主备高可用环境，将数据恢复还原；

缺点：停机维护时间较长，业务中断。

2）在线修复数据：修复数据字典表；

优点：业务连续性，无需停机维护（推荐）。

1.查询pg_user无postgres用户记录：

根据经验可以推断，由于数据字典缺少postgres用户，导致数据库owner显示为known(OID=10)，但是数据库基表pg_database中的db记录还存在。

2.尝试复现问题：

1）drop user postgres，提示不能直接删除postgres role：

2)  查看pg_user"基表"属性

可以看到pg_user是一张视图，数据存放在pg_user引用的基表，那么需要进一步定位该基表。

3.pg_user基表定位：

针对这个问题，需要了解一下pg initdb初始化数据字典相关过程：

1）initdb创建基表

pg_database/pg_authid等

2) 执行创建视图脚本，脚本位于/src/backend/catalog目录

其中system_views.sql创建基于基表的系统视图，其中就包括pg_user的定义：

pg_user视图基于pg_shadow，那么继续搜索pg_shadow:

pg_shadow视图基于pg_authid基表：

3）查看基表pg_autid无postgres记录

那么可以推断，由于某些原因删除了pg_authid中的postgres用户，导致pg_database显示known(oid=10)

4.问题复现：

1)删除pg_authid中的postgres记录

delte from pg_autid where role='rolname';

2)查看数据库情况：

数据库owner显示known(oid=10),问题复现。

通过前面分析定位，那么恢复就非常简单了，从正常的数据库(测试环境)中导出pg_authid表中postgres记录， 写入到故障环境即可（也可以直接写pg_authid表postgres记录,记得密码md加密写入），如果需要重置简单密码，提前在测试环境将postgres用户密码设置简单一些：

1）测试环境重置postgres用户简单密码：

2）导出正常环境中postgres记录为sql:

--备份记录表

create table pg_authid_r as select * from pg_authid where role='postgres';

--导出postgre记录表

--查看生成postgres记录sql

3)将生成sql回写到问题数据库pg_authid表

注意：需要使用superuser登录执行回写pg_authid 基表

4）恢复完成确认

至此，数据库恢复成功。

案例带来的启发：

1）DBA铁律：定期做好备份至关重要，关键时刻可以“回血保命”；

2）严格控制数据库服务器登录权限，避免责任定位不清晰带来的“飞来横锅”；

3）对于生产系统要有敬畏之心：上线前严格审核/运维过程变更可追溯/故障后须复盘（出现过的问题避免重犯）；

4）重视数据库安全，尽管客户有专业的安全团队，但是仍然架不住破坏性的变更"骚操作"；如果条件允许，开启数据库审计也是很有必要的；

5）研究一下源码，关键时刻或许有用。

如果觉得本文有所帮助或者启发，欢迎添加好友交流收藏(篇幅有限，更多资讯请关注附录知识星球二维码)，2024年我们一路同行！！！。