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
ALTER ROLE
% psql -U postgres -c "SELECT version();" testdb
psql: FATAL: role "postgres" is not permitted to log in
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
backend>
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;
backend>
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
%
postgres can connect again:
% sudo service postgresql start
% psql -U postgres -c "SELECT CURRENT_DATE;" testdb
current_date
--------------
2019-06-27
(1 row)