当我们在使用PostgreSQL数据库的时候，就算我们不新赋予Superuser权限给用户，默认，也会存在一个跟系统初始化用户同名的Superuser，这个Superuser的存在其实对于权限的管控是很有用的，但是如果我们误操作，把数据库所有的superuser都变成普通账号后，可能会引起很多问题。

如下，为找回Superuser权限的步骤，供参考。

一、误操作模拟

[xmaster@mogdb-kernel-0005 ~]$ psql
psql (14.1)
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 chpt      |                                                            | {}
 puser     | Superuser                                                  | {}
 xmaster   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# alter role xmaster nosuperuser ; 
ALTER ROLE
postgres=# alter role puser nosuperuser ; 
ERROR:  must be superuser to alter superuser roles or change superuser attribute
postgres=# alter user xmaster superuser;
ERROR:  must be superuser to alter superuser roles or change superuser attribute
复制

可以看到，当初始化的xmaster用户没有了Superuser的权限后，他不能回收其他用户的Superuser的权限了。

[xmaster@mogdb-kernel-0005 ~]$ psql -U puser
psql (14.1)
Type "help" for help.

postgres=# \du
                              List of roles
 Role name |                   Attributes                    | Member of 
-----------+-------------------------------------------------+-----------
 chpt      |                                                 | {}
 puser     | Superuser                                       | {}
 xmaster   | Create role, Create DB, Replication, Bypass RLS | {}

postgres=# alter role puser nosuperuser ; 
ALTER ROLE
postgres=# alter user puser superuser;
ERROR:  must be superuser to alter superuser roles or change superuser attribute
postgres=# \du
                              List of roles
 Role name |                   Attributes                    | Member of 
-----------+-------------------------------------------------+-----------
 chpt      |                                                 | {}
 puser     |                                                 | {}
 xmaster   | Create role, Create DB, Replication, Bypass RLS | {}
复制

此时我的PostgreSQL数据库里已经没有了所有的具备Superuser权限的用户。

二、如何恢复

恢复的方式就是停掉数据库后，使用单用户模式修改元数据,更改pg_authid表的对应字段rolsuper=true。
单用户模式可见我之前发的这篇文章。https://www.modb.pro/db/142632

[xmaster@mogdb-kernel-0005 ~]$ postgres --single postgres

PostgreSQL stand-alone backend 14.1
backend> update pg_authid set rolsuper=true where rolname='xmaster';
backend> update pg_authid set rolsuper=true where rolname='puser';

backend> [xmaster@mogdb-kernel-0005 ~]$ pg_ctl start
waiting for server to start....2022-10-21 17:58:38.383 CST [320101] LOG:  redirecting log output to logging collector process
2022-10-21 17:58:38.383 CST [320101] HINT:  Future log output will appear in directory "log".
 done
server started
[xmaster@mogdb-kernel-0005 ~]$ psql
psql (14.1)
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 chpt      |                                                            | {}
 puser     | Superuser                                                  | {}
 xmaster   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
复制

可以看到，最后Superuser的用户权限找回来了。