作者
digoal
日期
2021-05-28
标签
PostgreSQL , 超级账号 , 单用户
背景
把数据库所有的superuser都变成普通账号后, 怎么找回超级账号?
postgres=# \du+
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------+---------------------+-------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
ro | | {pg_read_all_data} |
rw | | {pg_write_all_data} |
postgres=# alter role postgres nosuperuser ;
ALTER ROLE
重新连接后:
```
postgres=> \du+
List of roles
Role name | Attributes | Member of | Description
-----------+-------------------------------------------------+---------------------+-------------
postgres | Create role, Create DB, Replication, Bypass RLS | {} |
ro | | {pg_read_all_data} |
rw | | {pg_write_all_data} |
postgres=> create role r1 superuser;
ERROR: must be superuser to create superusers
postgres=> alter role postgres superuser;
ERROR: must be superuser to alter superuser roles or change superuser attribute
postgres=> alter role ro superuser ;
ERROR: must be superuser to alter superuser roles or change superuser attribute
```
是不是崩溃了, 所有超级账号都消失了.
怎么找回来?
单用户, 修改元数据.
```
IT-C02YW2EFLVDL:data14 digoal$ pg_ctl stop -m fast
waiting for server to shut down.... done
server stopped
IT-C02YW2EFLVDL:data14 digoal$ postgres --single postgres
PostgreSQL stand-alone backend 14beta1
backend> update pg_authid set rolsuper=true where rolname='postgres';
```
```
backend> IT-C02YW2EFLVDL:data14 digoal$ pg_ctl start
waiting for server to start....2021-05-28 15:54:44.627 CST [69943] LOG: redirecting log output to logging collector process
2021-05-28 15:54:44.627 CST [69943] HINT: Future log output will appear in directory "log".
done
server started
IT-C02YW2EFLVDL:data14 digoal$ psql
psql (14beta1)
Type "help" for help.
postgres=# \du+
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------+---------------------+-------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
ro | | {pg_read_all_data} |
rw | | {pg_write_all_data} |
```
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
