PostgreSQL Administrator Account WITH NOLOGIN (recover your
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
postgresaccount so that the default administrator cannot any more log in the clsuter:
What a mess!
% 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
postgresprocess in single user mode. You need to know the data directory of your cluster in order for it to work:
What happened? I used the operating system user
% sudo -u postgres postgres --single -D /mnt/pg_data/pgdata/11.1 PostgreSQL stand-alone backend 11.3 backend>
postgresto 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:
Please note that, while the backend process can speak SQL, it does not speak the same way
backend> ALTER ROLE postgres WITH LOGIN; backend>
psqldoes: 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).
It is now time to restart the cluster and check if the user
backend> CTRL-D %
postgrescan connect again:
The world is an happy place again!
% sudo service postgresql start % psql -U postgres -c "SELECT CURRENT_DATE;" testdb current_date -------------- 2019-06-27 (1 row)