本节简单介绍了PostgreSQL中在忘记密码或者无权限登录数据库时如何recover administrator.

创建数据库,把管理员设置为NOLOGIN

[pg12@localhost ~]$ initdb -D /data/pg12/tmpdb
The files belonging to this database system will be owned by user "pg12".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating directory /data/pg12/tmpdb ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... PRC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
    pg_ctl -D /data/pg12/tmpdb -l logfile start
[pg12@localhost ~]$ pg_ctl -D /data/pg12/tmpdb -l logfile start
waiting for server to start.... done
server started
[pg12@localhost ~]$ psql -U pg12 -c "ALTER ROLE pg12 WITH NOLOGIN" -d postgres
ALTER ROLE

尝试登录数据库,提示无法登录

[pg12@localhost ~]$ psql -d postgres
psql: error: could not connect to server: FATAL:  role "pg12" is not permitted to log in

可通过single user mode处理此类”事故”
关闭数据库

[pg12@localhost ~]$  pg_ctl -D /data/pg12/tmpdb -l logfile stop
waiting for server to shut down.... done
server stopped

以single user mode模式启动数据库

[pg12@localhost ~]$ postgres --single -D /data/pg12/tmpdb postgres
PostgreSQL stand-alone backend 12beta1
backend>

执行维护任务

backend> alter role pg12 with login
backend>

启动数据库,登录

[pg12@localhost ~]$ pg_ctl -D /data/pg12/tmpdb -l logfile start
waiting for server to start.... done
server started
[pg12@localhost ~]$ psql -U pg12 -d postgres
psql (12beta1)
Type "help" for help.
postgres=#

参考资料
PostgreSQL Administrator Account WITH NOLOGIN (recover your postgres role)