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
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:
- You will stop postgres service –good thing you silenced those monitors
- 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.
- You will reset the login
- Exit using CTRL+D and start postgres service
- 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.