Restoring the superuser account on the postgres server

Question

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.

Answer 1

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.

Answer 2

For Windows it is necessary to:

• create a local user called 'postgres'
• assign write rights for this user on the data folder
• stop the postgres service from services
• open a command prompt as postgres user (e.g. runas /user:postgres cmd)

• run postgres in single user mode

  "<PATHTOPOSTGRES>\postgres.exe" --single -D "<PATHTODATA>/data"
  

  where the relevant database is inferred from the username (postgres)

• Execute the necessary commands to get your user back to abnormal (super):

  backend> ALTER USER postgres SUPERUSER;
  backend> ALTER USER postgres LOGIN;
  backend> ALTER USER postgres REPLICATION;
  backend> ALTER USER postgres CREATEDB;
  backend> ALTER USER postgres CREATEROLE;
  backend> ALTER USER postgres INHERIT;
  backend> ALTER USER postgres CONNECTION LIMIT -1;
  backend> ALTER USER postgres VALID UNTIL 'infinity';
  backend> ALTER USER postgres UNENCRYPTED PASSWORD 'MYPASSWORD';
  

• Ctrl-C to exit

• start the postgres service from services

Note the 'bug' in earlier PGAdmin 16.1 related to opening a user's properties might have been the cause.