Обсуждение: 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
>