It’s 11 pm on a Sunday night.

You sit down to your desk to do some routine maintenance on the Postgres server.

Nothing major: 10 minutes to silence monitoring, 30 seconds to lock out the users (who wants writes during any routine maintenance), 1 minute to run the update scripts, if all goes well you will be in bed by 11:30 pm. Who doesn’t love brief maintenances.

You silence monitoring and move onto locking out users. You copy the output of the below script, and with the swift move of a paste command you lock out everyone. Exit out of postgres and move onto the next step ….

SELECT DISTINCT 'ALTER ROLE ' || usename || ' WITH NOLOGIN' FROM pg_stat_activity;
ALTER ROLE app_user WITH NOLOGIN;
ALTER ROLE
ALTER ROLE postgres WITH NOLOGIN;
ALTER ROLE

exit

WAIT.

You locked out EVERYONE. Yourself, too. Postgres, too.

Panic sets in as you repeatedly try to issue the psql command and get error messages in return.

psql: FATAL:  role "postgres" is not permitted to log in

Yes, you didn’t fuck up the server forever, yes, you can get back in.

Here is how:

1. You will stop postgres service –good thing you silenced those monitors
2. You will start postgres stand-alone backend in single user mode.
   • This is basically a backdoor and below I was hacking my way back into a postgres 9.6 instance but the process is the same for all major versions.
3. You will reset the login
4. Exit using CTRL+D and start postgres service
5. Become postgres user and test it

#as root 
#stop postgres server 

sudo service postgresql-9.6 stop


#the below line might be slightly different depending on your environment. 
#particularly where postgres lives

/usr/pgsql-9.6/bin/postgres --single -D /var/lib/pgsql/9.6/data/ postgres


#here you will quietly restore login rights for postgres user:
backend> ALTER ROLE postgres WITH LOGIN;
backend>

#use CTRL-D to exit out. 
#start up postgres again, and get back in there!

sudo service postgresql-9.6 start
sudo su - postgres
psql
#we are in!

Phew. Depending on how long it took you to find my post, you still make it to bed by 11:30 pm.

Oh god, too much excitement for one night.

Credit where credit is due:

I mostly made this post for my notes in case I lock myself out again. If that scenario at the beginning of the post looked oddly specific, well, that’s because it was a specific scenario.

I used these two (this and this) articles to get back into postgres after my mini mental breakdown, and I want to be clear that all credit shall go to these authors. I simply internalized and regurgitated their words once I recovered from all the excitement.