PostgreSQL – How to Restore Superuser Account

postgresqlrole

I have a complete curious situation with a postgres dbms. I have installed the database and due to a misconfiguration of my installation scripts I made the superuser role to be a regular user. Now I have a completeley fresh installation of postgres with only the template database and a single user 'postgres' that can do pretty much nothing. How can I make postgres user superuser again?

I tried to reinstall the server under ubuntu 12.04 with aptitude reinstall postgresql-9.1 but the role seems to be unaffected. Most probably because the reinstallation does not affect the files where the roles are recorded.

Best Answer

Superuser rights aren't in any config file, they're part of the pg_catalog.pg_authid database table, which is shared between all databases in a PostgreSQL install.

You need to stop the PostgreSQL server, then restart it in single user mode, where it's always running as superuser.

There, you can ALTER USER myuser SUPERUSER to grant superuser rights. Exit single user mode, and start the server back up.

The exact details are a little distro and version dependent, but something like:

service postgresql stop
sudo -u postgres postgres --single /var/lib/pgsql/9.1/main/data

ALTER USER postgres SUPERUSER;

(control-D to exit)

service postgresql start

On some systems you might need to use an explicit path to the postgres executable, like /usr/pgsql-9.1/bin/postgres. Again, paths may vary based on version and distro.

Related Question