Обсуждение: Superuser accout (PostgreSQL 9.0.4)
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.
On Thu, Apr 7, 2011 at 11:15 PM, Grzegorz Szpetkowski <gszpetkowski@gmail.com> wrote: > 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" (?) I think you should be able to start Postgres in single-user mode, see: <http://www.postgresql.org/docs/9.0/static/app-postgres.html> and from there you can fix your superuser privileges. Josh
Where did you get 9.0.4 from?! Cheers, Andrej
Andrej <andrej.groups@gmail.com> writes: > Where did you get 9.0.4 from?! Time warp, apparently. regards, tom lane
Thanks. I didn't know about single-user mode: "The primary use for this mode is during bootstrapping by initdb. Sometimes it is used for debugging or disaster recovery (but note that running a single-user server is not truly suitable for debugging the server, since no realistic interprocess communication and locking will happen). When invoked in single-user mode from the shell, the user can enter queries and the results will be printed to the screen, but in a form that is more useful for developers than end users. In the single-user mode, the session user will be set to the user with ID 1, and implicit superuser powers are granted to this user. This user does not actually have to exist, so the single-user mode can be used to manually recover from certain kinds of accidental damage to the system catalogs." I made: /usr/lib/postgresql/9.0/bin/postgres --single -D /etc/postgresql/9.0/main/ backend> ALTER ROLE postgres SUPERUSER Then Ctrl-D (EOF) and it works fine. BTW (PostgreSQL 9.0.4): It's just my mistake, I have latest 9.0.3 version. Regards, Grzegorz Sz. 2011/4/8 Josh Kupershmidt <schmiddy@gmail.com>: > On Thu, Apr 7, 2011 at 11:15 PM, Grzegorz Szpetkowski > <gszpetkowski@gmail.com> wrote: >> 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" (?) > > I think you should be able to start Postgres in single-user mode, see: > <http://www.postgresql.org/docs/9.0/static/app-postgres.html> > and from there you can fix your superuser privileges. > > Josh >