Superuser accout (PostgreSQL 9.0.4)

From: Grzegorz Szpetkowski <gszpetkowski(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Superuser accout (PostgreSQL 9.0.4)
Date: 2011-04-08 03:15:14
Message-ID: BANLkTimo=aKVfZWjsZvE9ZJE6KJB--ofjQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Grzegorz Szpetkowski 2011-04-08 03:21:00 Valid role name (PostgreSQL 9.0.4)
Previous Message Josh Kupershmidt 2011-04-08 03:04:42 Re: What does \timing measure?