Cara reset password user postgres di PostgreSQL

masuk sebagai user postgres dengan perintah su – postgres

edit konfig pg_hba.conf

postgres@coderiver:/opt/framework-3.6.0/postgresql/bin$ vim ../data/pg_hba.conf

ganti:

# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

menjadi:

# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
# IPv6 local connections:
host    all         all         ::1/128               trust

terus reload

postgres@coderiver:/opt/framework-3.6.0/postgresql/bin$ ./pg_ctl reload
server signaled
LOG:  received SIGHUP, reloading configuration files

run `psql -d template1`

postgres@coderiver:/opt/framework-3.6.0/postgresql/bin$ ./psql -d template1
psql.bin (8.4.0)
Type "help" for help.
template1=#
 

lalu reset dengan perintah alter:

template1=# alter user postgres with password 'wahjangele';
ALTER ROLE
template1=# \q

kembalikan konfig pg_hba.conf ke semula:

postgres@coderiver:/opt/framework-3.6.0/postgresql/bin$ vim ../data/pg_hba.conf

reload:

postgres@coderiver:/opt/framework-3.6.0/postgresql/bin$ ./pg_ctl reload
server signaled
LOG:  received SIGHUP, reloading configuration files

lalu coba dengan password yang baru

postgres@coderiver:/opt/framework-3.6.0/postgresql/bin$ ./psql
Password:
psql.bin (8.4.0)
Type "help" for help.

postgres=#

referensi:
1. http://www.watters.ws/mediawiki/index.php/Postgres_Reset_Password
2. http://archives.postgresql.org/pgsql-novice/2002-08/msg00072.php