A hedge against future forgetation of my pg password

Posted: 11 August, 2021 Category: recipes Tagged: postgresql

The forgetations mount, of late. 'course, forgoing a password manager, based on the (il)logic that it would simply strengthen forgetations, is partly how we got here in the first place. So I opened pgAdmin locally after a long (loooong) while. It prompted me for the main password. Found it. Yay! I'm in! Then I clicked the pg instance of interest. Instead of patching me through, pgAdmin prompted me for THAT db's password.

OH, CRAP.

So this is what I ended up having to do to get back in:

  1. stop postgres
  2. Find the config file (pg_hba.conf). In windows-world, that's at C:\Program Files\PostgreSQL<version>\data\pg_hba.conf unless you monkeyed with install paths and on linux... well, it could be anywhere really.
  3. Back it up!!!
  4. Replace the entries with the following:

    # IPv4 local connections:
    host    all             all             127.0.0.1/32            trust
    # IPv6 local connections:
    host    all             all             ::1/128                 trust

    In short, pg is now primed to trust everyone, from anywhere all the time.

  5. Restart pg (and in my case, I went back into pgAdmin: note, it will still prompt you for the database password, just ignore it, leave the box empty and click OK... it lets you in all the same!)
  6. Now that you can access the db with no questions asked, change the poxy password:

    ALTER USER postgres with password 'never_forget'; -- or whatever ur shiny new password is gonna be
  7. close everything down again and stop postgres.
  8. Nuke that permissive conf file and restore the backup (you BACKED UP, RIGHT?)
  9. restart pg and you can get it in again with the new pwd when prompted.

Go dance a jig, you are a forgetotron that has survived one more day.