Search code examples
windowspostgresqlpsql

Forgot Admin Password on Postgres (Windows Installation), can't reset

I have a Windows PostgreSQL installation.

According to some posts, there is no default password set for the 'postgres' user yet I can't connect using an empty password string.

I'm receiving this exception when I try to connect:

Caused by: org.postgresql.util.PSQLException: FATAL: password authentication failed for user "postgres"

The most relevant tip was this: https://stackoverflow.com/a/25943227/1005607

Open  pg_hba.conf  
Change md5 -> TRUST  
then restart PgAdmin.

I tried that and restarted PGAdmin but it still asks me for the password when I try to connect:

enter image description here

The task manager in Windows shows some PostgreSQL processes are running. I can't switch them off.

I have tried this and it failed:

pg_ctl restart

ERROR: 
pg_ctl: no database directory specified and environment variable PGDATA unset

psql.exe postgres
Password: (none)
ERROR:
psql: fe_sendauth: no password supplied

How can I reset the default password for user 'postgres'?

Solution

  • Based on AK47's answer and some additional info I fixed it by doing the following,

    1) Stop Postgres if currently running, command line below. Need to give it the 'data' dir. In my case C:\PostgreSQL\data

    pg_ctl -D C:\PostgreSQL\data stop

    2) Edit the file pg_hba.conf (it's also in the \data dir) as follows:

    As AK40 wrote, change all MD5 references to trust , e.g.

    # TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

    3) Now run

    psql -U postgres

    4) In the PG Command Prompt that appears type,

    ALTER USER Postgres WITH PASSWORD '<newpassword>';

    5) Save this by typing wq enter to exit the PG Prompt

    6) Now start Postgres

    pg_ctl -D C:\PostgreSQL\data start

    7) Might want to revert the MD5 -> Trust change later in the pg_hba.conf.