For some reason, this is something that even experienced PostgreSQL people don’t know about, which is: What do you do if you’ve forgotten the postgres user’s password, and you have no other superuser available?
The answer is: Bring up PostgreSQL in single-user mode, reset the password, bring it back up in standard mode.
Of course, this requires access to the shell on the machine running PostgreSQL.
Comments
Bruce Momjian · 7 June 2017
Wouldn't it be easier to set 'trust' or 'peer' in pg_hba.conf for localhost, connect, and reset the password?Xof · 7 June 2017
Easier, sure. I'm always a bit disinclined to change config files for things like, lest they not get changed back...Andreas · 8 June 2017
I think the reason many do not think of this solution is that they are running PostgreSQL at Linux, Mac or BSD and have their pg_hba.conf set up so they can connect without password over a Unix socket as the postgres user. So, if you forget your password you can just ssh into the machine, sudo as postgres, and fix it. Personally I recommend not setting a password for the postgres user and instead using personal superuser accounts in PostgreSQL while postgres is only accessible from the postgres Unix user.