跟着悠然大佬的博文postgres用户凭空消失了... (qq.com)，本文档中PG版本是PG14.2 做以下实验：
Postgres用户是PG数据库初始化创建的superuser，也是模板库 template0/template1的owner。如果postgres用户突然凭空消失了，如何恢复。
针对目前问题，考虑2个解决方案：
1）数据备份恢复方式：重建主备高可用环境，将数据恢复还原；
缺点：停机维护时间较长，业务中断。
2）在线修复数据：修复数据字典表；
优点：业务连续性，无需停机维护（推荐）。
着重强调：在线修复数据：修复数据字典表的方案只适合库中存在不止一个superuser的用户
场景复现1
模拟删除postgres用户的记录
删除postgres用户
删除postgres用户
drop role postgres，提示不能直接删除postgres role
psql -Udxj -d postgres
drop role postgres;
或
psql
drop user postgres;输出如下：
[postgres@localhost ~]$ psql -Udxj -d postgres
psql (14.2)
Type "help" for help.
postgres=# drop role postgres;
ERROR: cannot drop role postgres because it is required by the database system
或
[postgres@localhost ~]$ psql
psql (14.2)
Type "help" for help.
postgres=# drop user postgres;
ERROR: current user cannot be dropped查看pg_user基表属性
可以看到pg_user是一张视图，数据存放在pg_user引用的基表，那么需要进一步定位该基表。
psql -Udxj -d postgres
\d+ pg_user输出如下：
postgres=# \d+ pg_user
View "pg_catalog.pg_user"
Column | Type | Collation | Nullable | Default | Storage | Description
--------------+--------------------------+-----------+----------+---------+----------+-------------
usename | name | | | | plain |
usesysid | oid | | | | plain |
usecreatedb | boolean | | | | plain |
usesuper | boolean | | | | plain |
userepl | boolean | | | | plain |
usebypassrls | boolean | | | | plain |
passwd | text | | | | extended |
valuntil | timestamp with time zone | | | | plain |
useconfig | text[] | C | | | extended |
View definition:
SELECT pg_shadow.usename,
pg_shadow.usesysid,
pg_shadow.usecreatedb,
pg_shadow.usesuper,
pg_shadow.userepl,
pg_shadow.usebypassrls,
'********'::text AS passwd,
pg_shadow.valuntil,
pg_shadow.useconfig
FROM pg_shadow;pg_user基表定位
针对这个问题，需要了解一下pg initdb初始化数据字典相关过程：
initdb创建基表
pg_database/pg_authid等
执行创建视图的脚本，脚本位于/src/backend/catalog目录
[postgres@localhost ~]$ cd /pgdb/pgsql/src/backend/catalog/
[postgres@localhost catalog]$ ls -l system_views.sql
-rwxrwxr-x. 1 postgres postgres 47570 Feb 8 2022 system_views.sql
[postgres@localhost catalog]$ pwd
/pgdb/pgsql/src/backend/catalog其中system_views.sql创建基于基表的系统视图，其中就包括pg_user的定义：
CREATE VIEW pg_user AS
SELECT
usename,
usesysid,
usecreatedb,
usesuper,
userepl,
usebypassrls,
'********'::text as passwd,
valuntil,
useconfig
FROM pg_shadow;pg_user视图基于pg_shadow，那么继续搜索pg_shadow:
CREATE VIEW pg_shadow AS
SELECT
rolname AS usename,
pg_authid.oid AS usesysid,
rolcreatedb AS usecreatedb,
rolsuper AS usesuper,
rolreplication AS userepl,
rolbypassrls AS usebypassrls,
rolpassword AS passwd,
rolvaliduntil AS valuntil,
setconfig AS useconfig
FROM pg_authid LEFT JOIN pg_db_role_setting s
ON (pg_authid.oid = setrole AND setdatabase = 0)
WHERE rolcanlogin;
REVOKE ALL ON pg_shadow FROM public;pg_shadow视图基于pg_authid基表：
postgres=# \d pg_authid
Table "pg_catalog.pg_authid"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
oid | oid | | not null |
rolname | name | | not null |
rolsuper | boolean | | not null |
rolinherit | boolean | | not null |
rolcreaterole | boolean | | not null |
rolcreatedb | boolean | | not null |
rolcanlogin | boolean | | not null |
rolreplication | boolean | | not null |
rolbypassrls | boolean | | not null |
rolconnlimit | integer | | not null |
rolpassword | text | C | |
rolvaliduntil | timestamp with time zone | | |
Indexes:
"pg_authid_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
"pg_authid_rolname_index" UNIQUE CONSTRAINT, btree (rolname), tablespace "pg_global"
Tablespace: "pg_global"查看基表pg_autid无postgres记录
那么可以推断，由于某些原因删除了pg_authid中的postgres用户，导致pg_database显示known(oid=10)
postgres=# select * from pg_authid;
oid | rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconn
limit | rolpassword |
rolvaliduntil
-------+---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------
------+---------------------------------------------------------------------------------------------------------------------------------------+-
--------------
6171 | pg_database_owner | f | t | f | f | f | f | f |
-1 | |
6181 | pg_read_all_data | f | t | f | f | f | f | f |
-1 | |
6182 | pg_write_all_data | f | t | f | f | f | f | f |
-1 | |
3373 | pg_monitor | f | t | f | f | f | f | f |
-1 | |
3374 | pg_read_all_settings | f | t | f | f | f | f | f |
-1 | |
3375 | pg_read_all_stats | f | t | f | f | f | f | f |
-1 | |
3377 | pg_stat_scan_tables | f | t | f | f | f | f | f |
-1 | |
4569 | pg_read_server_files | f | t | f | f | f | f | f |
-1 | |
4570 | pg_write_server_files | f | t | f | f | f | f | f |
-1 | |
4571 | pg_execute_server_program | f | t | f | f | f | f | f |
-1 | |
4200 | pg_signal_backend | f | t | f | f | f | f | f |
-1 | |
16385 | dxj | t | t | f | f | t | f | f |
-1 | SCRAM-SHA-256$4096:ShgT7wpIvH3N2ZCwrlygsQ==$VHflfQRHrewFEJc/m/AJ5wnaT69HMZvqDtZJVEP2pOk=:z2BYgpt9bpYMdYDcueo4yQidTDNR/sUvcSsE0IfqIN0= |
(12 rows)
删除pg_authid中的postgres记录
delete from pg_authid where role='rolname';
复现删除postgres用户
delete from pg_authid where rolname='postgres';查看数据库情况
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+------------------+----------+-------------+-------------+-------------------
postgres | unknown (OID=10) | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | unknown (OID=10) | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/10 +
| | | | | 10=CTc/10
template1 | unknown (OID=10) | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/10 +
| | | | | 10=CTc/10
(3 rows)
数据库owner显示known(oid=10),问题复现成功。
分析过程
查看用户表和数据库表
查询pg_user无postgres用户记录。
根据经验可以推断，由于数据字典缺少postgres用户，导致数据库owner显示为known(OID=10)，但是数据库基表pg_database中的db记录还存在。
postgres=# select * from pg_catalog.pg_user;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
dxj | 16385 | f | t | f | f | ******** | |
(1 rows)
postgres=# select * from pg_catalog.pg_database;
oid | datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid
| datminmxid | dattablespace | datacl
-------+-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------
+------------+---------------+-------------------------------------
13892 | postgres | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 13891 | 727
| 1 | 1663 |
1 | template1 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | t | -1 | 13891 | 727
| 1 | 1663 | {=c/postgres,postgres=CTc/postgres}
13891 | template0 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | f | -1 | 13891 | 727
| 1 | 1663 | {=c/postgres,postgres=CTc/postgres}
(3 rows)处理过程
从正常的数据库(测试环境)中导出pg_authid表中postgres记录， 写入到故障环境即可（也可以直接写pg_authid表postgres记录,记得密码md加密写入），如果需要重置简单密码，提前在测试环境将postgres用户密码设置简单一些，视情况而定，本文档采用将测试环境的postgres用户密码设置简单。
测试环境重置postgres用户密码
测试环境正常运行的库中操作：
alter user postgres with password 'top@123';导出测试环境中pg_authid表中用户为postgres的sql
备份pg_authid表中用户为postgres的数据
create table pg_authid_r as select * from pg_authid where rolname='postgres';导出postgre用户数据为sql文件
pg_dump -d postgres -t pg_authid_r --insert > /home/postgres/pg_authid_r.sql查看生成postgres用户数据的sql
cat /home/postgres/pg_authid_r.sql输出如下：
......
ALTER TABLE public.pg_authid_r OWNER TO postgres;
--
-- Data for Name: pg_authid_r; Type: TABLE DATA; Schema: public; Owner: postgres
--
INSERT INTO public.pg_authid_r VALUES (10, 'postgres', true, true, true, true, true, true, true, -1, 'SCRAM-SHA-256$4096:WgvfEcyXDOYahck+NgW5HA==$Draqt+5zm4L7ZoT1SVKfxYWBQlnJXsDmczcgImbFg7U=:DkaB2uhox18NxlDv8k1cl1OlIe1s80k5IuSnjqGbr1M=', NULL);
--
-- PostgreSQL database dump complete
将生成sql回写到问题数据库pg_authid表
登录数据库
[postgres@localhost ~]$ psql
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: role "postgres" does not exist这一步骤报错了，和悠然大佬沟通后得知库内必须要有个superuser用户存在，必须用superuser用户登录数据库进行向pg_authid表插入postgres用户的记录才可以，悲剧了，没有备份，只能删除库并重新初始化实例。
向pg_authid表插入postgres用户的记录
登录被删除postgres用户的服务器，同时记得将pg_authid_r表名修改为pg_authid。
INSERT INTO pg_authid VALUES (10, 'postgres', true, true, true, true, true, true, true, -1, 'SCRAM-SHA-256$4096:WgvfEcyXDOYahck+NgW5HA==$Draqt+5zm4L7ZoT1SVKfxYWBQlnJXsDmczcgImbFg7U=:DkaB2uhox18NxlDv8k1cl1OlIe1s80k5IuSnjqGbr1M=', NULL);总结
做实验或者根据大佬博客处理问题的时候务必多思考，有的场景不通用。
场景复现2
模拟删除postgres用户的记录
删除postgres用户
删除postgres用户
drop role postgres，提示不能直接删除postgres role
psql -Udxj -d postgres
drop role postgres;
或
psql
drop user postgres;输出如下：
[postgres@localhost ~]$ psql -Udxj -d postgres
psql (14.2)
Type "help" for help.
postgres=# drop role postgres;
ERROR: cannot drop role postgres because it is required by the database system
或
[postgres@localhost ~]$ psql
psql (14.2)
Type "help" for help.
postgres=# drop user postgres;
ERROR: current user cannot be dropped查看pg_user基表属性
可以看到pg_user是一张视图，数据存放在pg_user引用的基表，那么需要进一步定位该基表。
psql -Udxj -d postgres
\d+ pg_user输出如下：
postgres=# \d+ pg_user
View "pg_catalog.pg_user"
Column | Type | Collation | Nullable | Default | Storage | Description
--------------+--------------------------+-----------+----------+---------+----------+-------------
usename | name | | | | plain |
usesysid | oid | | | | plain |
usecreatedb | boolean | | | | plain |
usesuper | boolean | | | | plain |
userepl | boolean | | | | plain |
usebypassrls | boolean | | | | plain |
passwd | text | | | | extended |
valuntil | timestamp with time zone | | | | plain |
useconfig | text[] | C | | | extended |
View definition:
SELECT pg_shadow.usename,
pg_shadow.usesysid,
pg_shadow.usecreatedb,
pg_shadow.usesuper,
pg_shadow.userepl,
pg_shadow.usebypassrls,
'********'::text AS passwd,
pg_shadow.valuntil,
pg_shadow.useconfig
FROM pg_shadow;pg_user基表定位
针对这个问题，需要了解一下pg initdb初始化数据字典相关过程：
initdb创建基表
pg_database/pg_authid等
执行创建视图的脚本，脚本位于/src/backend/catalog目录
[postgres@localhost ~]$ cd /pgdb/pgsql/src/backend/catalog/
[postgres@localhost catalog]$ ls -l system_views.sql
-rwxrwxr-x. 1 postgres postgres 47570 Feb 8 2022 system_views.sql
[postgres@localhost catalog]$ pwd
/pgdb/pgsql/src/backend/catalog其中system_views.sql创建基于基表的系统视图，其中就包括pg_user的定义：
CREATE VIEW pg_user AS
SELECT
usename,
usesysid,
usecreatedb,
usesuper,
userepl,
usebypassrls,
'********'::text as passwd,
valuntil,
useconfig
FROM pg_shadow;pg_user视图基于pg_shadow，那么继续搜索pg_shadow:
CREATE VIEW pg_shadow AS
SELECT
rolname AS usename,
pg_authid.oid AS usesysid,
rolcreatedb AS usecreatedb,
rolsuper AS usesuper,
rolreplication AS userepl,
rolbypassrls AS usebypassrls,
rolpassword AS passwd,
rolvaliduntil AS valuntil,
setconfig AS useconfig
FROM pg_authid LEFT JOIN pg_db_role_setting s
ON (pg_authid.oid = setrole AND setdatabase = 0)
WHERE rolcanlogin;
REVOKE ALL ON pg_shadow FROM public;pg_shadow视图基于pg_authid基表：
postgres=# \d pg_authid
Table "pg_catalog.pg_authid"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
oid | oid | | not null |
rolname | name | | not null |
rolsuper | boolean | | not null |
rolinherit | boolean | | not null |
rolcreaterole | boolean | | not null |
rolcreatedb | boolean | | not null |
rolcanlogin | boolean | | not null |
rolreplication | boolean | | not null |
rolbypassrls | boolean | | not null |
rolconnlimit | integer | | not null |
rolpassword | text | C | |
rolvaliduntil | timestamp with time zone | | |
Indexes:
"pg_authid_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
"pg_authid_rolname_index" UNIQUE CONSTRAINT, btree (rolname), tablespace "pg_global"
Tablespace: "pg_global"查看基表pg_autid无postgres记录
那么可以推断，由于某些原因删除了pg_authid中的postgres用户，导致pg_database显示known(oid=10)
postgres=# select * from pg_authid;
oid | rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconn
limit | rolpassword |
rolvaliduntil
-------+---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------
------+---------------------------------------------------------------------------------------------------------------------------------------+-
--------------
6171 | pg_database_owner | f | t | f | f | f | f | f |
-1 | |
6181 | pg_read_all_data | f | t | f | f | f | f | f |
-1 | |
6182 | pg_write_all_data | f | t | f | f | f | f | f |
-1 | |
3373 | pg_monitor | f | t | f | f | f | f | f |
-1 | |
3374 | pg_read_all_settings | f | t | f | f | f | f | f |
-1 | |
3375 | pg_read_all_stats | f | t | f | f | f | f | f |
-1 | |
3377 | pg_stat_scan_tables | f | t | f | f | f | f | f |
-1 | |
4569 | pg_read_server_files | f | t | f | f | f | f | f |
-1 | |
4570 | pg_write_server_files | f | t | f | f | f | f | f |
-1 | |
4571 | pg_execute_server_program | f | t | f | f | f | f | f |
-1 | |
4200 | pg_signal_backend | f | t | f | f | f | f | f |
-1 | |
16385 | dxj | t | t | f | f | t | f | f |
-1 | SCRAM-SHA-256$4096:ShgT7wpIvH3N2ZCwrlygsQ==$VHflfQRHrewFEJc/m/AJ5wnaT69HMZvqDtZJVEP2pOk=:z2BYgpt9bpYMdYDcueo4yQidTDNR/sUvcSsE0IfqIN0= |
(12 rows)
创建superuser用户dxj
--创建superuser用户dxj
create user djx superuser password 'dxj';确认库中存在不止一个superuser的用户
库中除了postgres用户具备superuser角色外，dxj用户也具备superuser角色。
该步骤必须验证，如果只有postgres用户具备superuser角色，不能进行模拟复现，不然pg_audit表中删除postgres用户数据后库就不能登录，如果没有备份就凉凉了。
psql -E
\du+输出如下：
[postgres@localhost ~]$ psql -E
psql (14.2)
Type "help" for help.
postgres=# \du
********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit,
r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
r.rolconnlimit, r.rolvaliduntil,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;
**************************
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
dxj | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}删除pg_authid中的postgres记录
--语法
delete from pg_authid where role='rolname';
--复现删除postgres用户
delete from pg_authid where rolname='postgres';查看数据库情况
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+------------------+----------+-------------+-------------+-------------------
postgres | unknown (OID=10) | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | unknown (OID=10) | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/10 +
| | | | | 10=CTc/10
template1 | unknown (OID=10) | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/10 +
| | | | | 10=CTc/10
(3 rows)
数据库owner显示known(oid=10),问题复现成功。
分析过程
查看用户表和数据库表
查询pg_user无postgres用户记录。
根据经验可以推断，由于数据字典缺少postgres用户，导致数据库owner显示为known(OID=10)，但是数据库基表pg_database中的db记录还存在。
postgres=# select * from pg_catalog.pg_user;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
dxj | 16385 | f | t | f | f | ******** | |
(1 rows)
postgres=# select * from pg_catalog.pg_database;
oid | datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid
| datminmxid | dattablespace | datacl
-------+-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------
+------------+---------------+-------------------------------------
13892 | postgres | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 13891 | 727
| 1 | 1663 |
1 | template1 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | t | -1 | 13891 | 727
| 1 | 1663 | {=c/postgres,postgres=CTc/postgres}
13891 | template0 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | f | -1 | 13891 | 727
| 1 | 1663 | {=c/postgres,postgres=CTc/postgres}
(3 rows)处理过程
从正常的数据库(测试环境)中导出pg_authid表中postgres记录， 写入到故障环境即可（也可以直接写pg_authid表postgres记录,记得密码md加密写入），如果需要重置简单密码，提前在测试环境将postgres用户密码设置简单一些，视情况而定，本文档采用将测试环境的postgres用户密码设置简单。
测试环境重置postgres用户密码
测试环境正常运行的库中操作：
alter user postgres with password 'top@123';导出测试环境中pg_authid表中用户为postgres的sql
备份pg_authid表中用户为postgres的数据
create table pg_authid_r as select * from pg_authid where rolname='postgres';导出postgre用户数据为sql文件
pg_dump -d postgres -t pg_authid_r --insert > /home/postgres/pg_authid_r.sql查看生成postgres用户数据的sql
cat /home/postgres/pg_authid_r.sql输出如下：
......
ALTER TABLE public.pg_authid_r OWNER TO postgres;
--
-- Data for Name: pg_authid_r; Type: TABLE DATA; Schema: public; Owner: postgres
--
INSERT INTO public.pg_authid_r VALUES (10, 'postgres', true, true, true, true, true, true, true, -1, 'SCRAM-SHA-256$4096:WgvfEcyXDOYahck+NgW5HA==$Draqt+5zm4L7ZoT1SVKfxYWBQlnJXsDmczcgImbFg7U=:DkaB2uhox18NxlDv8k1cl1OlIe1s80k5IuSnjqGbr1M=', NULL);
--
-- PostgreSQL database dump complete
将生成sql回写到问题数据库pg_authid表
登录数据库
必须使用superuser登录执行回写pg_authid 基表。
psql -Udxj -d postgres向pg_authid表插入postgres用户的记录
登录被删除postgres用户的服务器，同时记得将pg_authid_r表名修改为pg_authid，并且把模式public去掉，如下：
INSERT INTO pg_authid VALUES (10, 'postgres', true, true, true, true, true, true, true, -1, 'SCRAM-SHA-256$4096:K9qbsnImAhbkRkzgFcuUBg==$GJsTHwTVhdXsZ0FsCTFCmyhXXdKgmb4x4Fefkkffl9s=:9/ySYMzmeQxDclWac/v7VM346tbql8GyESNx3gZu/98=', NULL);如果不把模式public去掉，会提示如下错误：
postgres=# INSERT INTO public.pg_authid VALUES (10, 'postgres', true, true, true, true, true, true, true, -1, 'SCRAM-SHA-256$4096:K9qbsnImAhbkRkzgFcuUBg==$GJsTHwTVhdXsZ0FsCTFCmyhXXdKgmb4x4Fefkkffl9s=:9/ySYMzmeQxDclWac/v7VM346tbql8GyESNx3gZu/98=', NULL);
ERROR: relation "public.pg_authid" does not exist
LINE 1: INSERT INTO public.pg_authid VALUES (10, 'postgres', true, t...
^查看问题节点的数据库
owner重新显示成了postgres，恢复正常了。
psql -E
\l+输出如下：
[postgres@localhost ~]$ psql -E
psql (14.2)
Type "help" for help.
postgres=# \l+
********* QUERY **********
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges",
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
ELSE 'No Access'
END as "Size",
t.spcname as "Tablespace",
pg_catalog.shobj_description(d.oid, 'pg_database') as "Description"
FROM pg_catalog.pg_database d
JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
ORDER BY 1;
**************************
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8537 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8385 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8385 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(3 rows)总结
最后沿用下悠然大佬的案例总结：
1）DBA铁律：定期做好备份至关重要，关键时刻可以“回血保命”；
2）严格控制数据库服务器登录权限，避免责任定位不清晰带来的“飞来横锅”；
3）对于生产系统要有敬畏之心：上线前严格审核/运维过程变更可追溯/故障后须复盘（出现过的问题避免犯）；
4）重视数据库安全，尽管客户有专业的安全团队，但是仍然架不住破坏性的变更"骚操作"；如果条件允许，开启数据库审计也是很有必要的；
5）研究一下源码，关键时刻或许有用。
6）业务强烈建议创建业务数据库和业务用户，规避系统用户发生异常影响业务。
如果觉得本文有所帮助或者启发，欢迎关注公众号发私信添加好友交流收藏(篇幅有限，更多资讯请关注董小姐公众号)，2024年我们一路同行！！！。
参数链接：postgres用户凭空消失了...
扫一扫
1177
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
