Postgres Pro
Downloads
Home   >   mailing lists

Thread: 7.3 : how add user when last user have lost 'createuser' option

7.3 : how add user when last user have lost 'createuser' option

From
alban
Date:
i have execute
ALTER USER postgres WITH NOCREATEUSER;
it's work very well but now, i cant add, drop, alter user, alter group...

ALTER USER postgres WITH CREATEUSER;
--> ERROR : ALTER USER : permission denied

there is a solution ?

Thank
ALban

Re: 7.3 : how add user when last user have lost 'createuser' option

From
Bruce Momjian
Date:
alban wrote:
> i have execute
> ALTER USER postgres WITH NOCREATEUSER;
> it's work very well but now, i cant add, drop, alter user, alter group...
> 
> ALTER USER postgres WITH CREATEUSER;
> --> ERROR : ALTER USER : permission denied
> 
> there is a solution ?

Uh, you removed createuser permission from the postgres super-user.  I
think that's why you are getting the failure.  I think you have to
manually update pg_shadow to fix this, if it will allow that.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073

Re: 7.3 : how add user when last user have lost 'createuser' option

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> alban wrote:
>> i have execute
>> ALTER USER postgres WITH NOCREATEUSER;

> Uh, you removed createuser permission from the postgres super-user.

That was a really bad move :-(

If you have no superusers left, I think your only option is to shut down
the postmaster, start a standalone backend, and ALTER USER postgres back
to superuser status in the standalone backend.  Then you can restart the
postmaster.

IIRC this will work in 7.3 and probably 7.2.  If you're running
something older, you are well and truly hosed, because you can't
reclaim superuser status even with a standalone backend.
        regards, tom lane