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

Question

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:

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'?

score 16 · Accepted Answer · answered Nov 15 '17 at 17:36

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.

I'm going to update my answer based on some of the details in this which I left out — Alan Kavanagh, Nov 15 '17 at 17:38
Would be nice to precise that all "md5" which address a local address have to be changed. It could be dangerous to trust every connexions even those coming from outside the server. — Hybris95, Feb 04 '20 at 14:08
How do you expect `psql -U postgres` to work if the server is stopped? — ProfK, Nov 14 '20 at 08:17
how can I use the first command if my postgres installed in "Program Files" directory? — Aitsuken, Mar 31 '22 at 03:59

Alan Kavanagh · Answer 2 · 2017-11-15T17:48:50.163

4

Update your pg_hba.conf file to allow for trusted local connections

[root@server] vim pg_hba.conf
>> local all all         trust

then restart your PostgreSQL server

[user@machine] pg_ctl -D C:\PostgreSQL\data restart    (Windows)
[root@server] service postgresql restart            (Linux)

at this point you can connect to your server as postgres user using a local connection without the need to enter a password (omitting the -h parameter when calling the psql command will use a local connection - if you pass -h then this will match the line host all all 0.0.0.0/0 <method> in your pg_hba.conf file)

[root@server] psql -U postgres

You can then alter the postgres user role and set the password to whatever you like using the following command in the psql terminal

[psql] alter role postgres password <new_password>;

Once this is done you can restart your PostgreSQL server again

[user@machine] pg_ctl -D C:\PostgreSQL\data restart     (Windows)
[root@server] service postgresql restart             (Linux)

and at this point your password should be changed to the new password

edited Nov 15 '17 at 17:48

answered Nov 15 '17 at 15:25

Alan Kavanagh

9,425
7
41
65

I'm on Windows. I edited the file, but there's no "service postgresql restart". – gene b. Nov 15 '17 at 15:57
[See this answer for how to restart PostgreSQL on Windows](https://stackoverflow.com/questions/36629963/how-to-start-postgresql-on-windows) – Alan Kavanagh Nov 15 '17 at 16:35
Bruh it says "Windows" – Aitsuken Mar 31 '22 at 04:01
@Kloody see the accepted answer. The author posted a solution based on this – Alan Kavanagh Mar 31 '22 at 10:05

score 0 · Answer 3 · answered May 28 '20 at 12:26

I was having the same issue and I couldn't use Postgres in the CLI on my windows machine but I managed to trace down where the passwords were stored via

%APPDATA%\PostgreSQL\pgpass.conf

NB: You must have selected store password option when creating a server or database in the pgAdmin.

I hope this helps. Thanks.

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

3 Answers3

Linked