Re: Help: Dumb newbie locked himself out!

Tom,

I tried to run
UPDATE pg_shadow SET usesuper = true WHERE usename = 'postgres';

and I got
ERROR:  pg_shadow: Permission denied.

Any other suggestions? I appreciate your help.

Thanks,
Balazs

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Wednesday, July 17, 2002 10:25 AM
To: Balazs Wellisch
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Help: Dumb newbie locked himself out!


"Balazs Wellisch" <balazs@bwellisch.com> writes:
> sorry for the dumb question but I managed to mess up the privileges in my
> database and now I'm unable to create or modify any users. I can still
> connect to my database using the postgres user but I don't have the
> permissions to do anything. How can I reset the permissions so that the
> postgres user will have full access again?
> This is a RedHat Linux 7.2 machine running PostgreSQL 7.1.3

Hmm.  Since you are still the postgres user, I think you can just do
    UPDATE pg_shadow SET usesuper = true WHERE usename = 'postgres';
Since you are the owner of the table, it should let you do that even
though it doesn't think you are superuser.  Then start a new backend
and you should be super again.

In 7.2 there are safer ways of dealing with this sort of mistake (you
can run a standalone backend that will let you operate as a superuser
no matter how badly you've messed up pg_shadow).  You might want to
update sometime soon.

            regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org