PostgreSQL Administrator Account WITH NOLOGIN (recover your postgres role)

What if you get locked out your own cluster due to a simple and, to some extent, stupid error? Let’s see it in quick list of steps.
First of all, lock the default postgres account so that the default administrator cannot any more log in the clsuter:
% psql -U postgres -c "ALTER ROLE postgres WITH NOLOGIN" testdb

% psql -U postgres -c "SELECT version();" testdb               
psql: FATAL:  role "postgres" is not permitted to log in
What a mess!

PostgreSQL has a specific recovery mode, called single user mode, that resemble the operating system single user mode and can be used for such situations. Let’s see how.
First of all, shut down the cluster, avoid more damages of what you have already done!
% sudo service postgresql stop

Now, start the postgres process in single user mode. You need to know the data directory of your cluster in order for it to work:
% sudo -u postgres postgres --single -D /mnt/pg_data/pgdata/11.1

PostgreSQL stand-alone backend 11.3
What happened? I used the operating system user postgres to launch the operating system process postgres (ok there’s a little name confusion here!) in single (--single) mode for my own data directory (-D). I got a prompt, I’m connected to the backend process directly, so this is not the same as a local or TCP/IP connection: I’m interacting with the backend process itself. Luckily, the backend process can speak SQL! Therefore, I can reset my administrator role:
backend> ALTER ROLE postgres WITH LOGIN;
Please note that, while the backend process can speak SQL, it does not speak the same way psql does: there is no need for a semicolon and an <enter> will send the statement to the backend. Anyway, I can now release the backend process as I would do with any other operating system process, gently or not, for instance via CTRL-D (End of File).
backend>  CTRL-D
It is now time to restart the cluster and check if the user postgres can connect again:
% sudo service postgresql start
% psql -U postgres -c "SELECT CURRENT_DATE;" testdb
(1 row)
The world is an happy place again!

The article PostgreSQL Administrator Account WITH NOLOGIN (recover your role) has been posted by Luca Ferrari on June 27, 2019