Hi,
I have standard main 5432 cluster with postgres owner/superuser. I made:
su -c "su - postgres"
psql -c "\du"
List of roles
Role name | Attributes | Member of
-----------+-----------------------------------+-----------
postgres | Superuser, Create role, Create DB | {}
psql -c "ALTER ROLE postgres NOSUPERUSER"
After this there is no superuser in cluster 9.0/main, and I can't
restore it to previous point:
psql -c "ALTER ROLE postgres SUPERUSER"
ERROR: must be superuser to alter superusers
createuser -srd admin
createuser: creation of new role failed: ERROR: must be superuser to
create superusers
psql -c "UPDATE pg_authid SET rolsuper = true WHERE rolname = 'postgres'"
(rolcatupdate change to false, so obviously above doesn't work)
Is there any way to restore superuser without recreating cluster from
scratch (initdb, pg_createcluster etc.) ? Or better should PostgreSQL
prevent for such situation (mistake command) ? I don't know is there
any "at least one superuser per cluster rule" (?)
Thanks,
Grzegorz Sz.