Recover postgres user in PostgreSQL

问题:

I need to recover the superuser privilege for the postgres user, because it lost the superuser qualification.

I cannot do anything in the psql shell, it gives the message

must be superuser to create superusers

The system is Ubuntu 16.04 and PostgreSQL 9.5.
Can I recover this postgres user?

回答1:

If you managed to remove superuser privileges from all your users, you'll have to start the database in single user mode:

1. Stop the database server as operating system user postgres:

   /path/to/postgresql/bin/pg_ctl stop -D /path/to/data/directory
   

2. Start the server in single user mode:

   /path/to/postgresql/bin/postgres --single -D /path/to/data/directory postgres
   

   Now you are a superuser.

3. Restore the superuser privilege:

   ALTER ROLE postgres SUPERUSER
   

4. Exit from the session with CTRL+D (or CTRL+Z if you are on Windows).

5. Restart PostgreSQL the way you normally do it.