Re: Help: Dumb newbie locked himself out!

"Balazs Wellisch" <balazs(at)bwellisch(dot)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