Postgres Reset Password

From Wiki
Jump to: navigation, search

How to reset the postgres password:

First you need to edit /var/lib/pgsql/data/pg_hba.conf and set the authentication to "trust".

Restart postgresql:

service postgresql restart

The switch users:

su postgres

If the postgres database does not exist you can create it:

createdb postgres

Then you can login and change the password:

alter user postgres with password 'foobar';

Exit and edit /var/lib/pgsql/data/pg_hba.conf again, change "trust" to "md5"

Restart postgres:

/etc/init.d/postgresql restart

Recover superuser privileges

First you must start postgres in single user mode.

sudo -u postgres /usr/pgsql-9.4/bin/postgres --single -D /var/lib/pgsql/9.4/data 
ALTER USER postgres SUPERUSER;
ctl-d to exit

Restart postgresql.

service postgresql-9.4 start