15:03

Resetting the postgres user’s password

7 June 2017

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.

There are 3 comments.

Bruce Momjian at 15:33, 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 at 16:06, 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 at 04:26, 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.