How to get back into a Postgres server after … yes. Locking out postgres user.

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;



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;

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

sudo service postgresql-9.6 start
sudo su - postgres
#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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.