Accidently removed postgres default superuser priv

Question

From inside Webmin I accidently unmarked the checkbox "Can create databases?" and "Can create users?"

Stupid, I know.

But since it takes a user with superuser privileges to edit/create a user, is there a way to fix this from the linux terminal?

I know it's possible to reset a password, but I haven't been able to find out how to reset the default superuser postgres' roles/privileges. Is this even possible or do I have to do a reinstall?

Answer 1

there's a single-user mode where you always have superuser capabilities:

as root

service postgresql stop

as user postgres: (you may need to change the version number (here 9.1) Non .deb distros will probably have a different path here too. the first path is the postgres binary, the second one is the directory that contains postgresql.conf.

/usr/lib/postgresql/9.1/bin/postgres --single -D /etc/postgresql/9.1/main/

on the new command line

alter user postgres with superuser;

ctrl-d to exit.

as root:

service postgresql start

A similar strategy can use used to set or reset passwords etc.