Null password failing login for postgres 9.3 windows

Question

I changed the postgres password to NULL for the user postgres on my local machine. using the following command in SQL window: ALTER ROLE postgres WITH PASSWORD NULL;

Now, I cannot login back again as it asking for password and not accepting anything. I can't run the SQL query to change the password as I am not logged in now. Can I do something from windows command prompt to change the password back?

http://dba.stackexchange.com/q/19643/7788, http://stackoverflow.com/q/10845998/398670 . — Craig Ringer, Sep 22 '14 at 12:43
PostgreSQL could possibly stop you doing this if it sees that your `pg_hba.conf` has `trust` authentication, but it doesn't know you're not doing it on purpose (say, after creating another superuser). — Craig Ringer, Sep 22 '14 at 12:45

score 4 · Accepted Answer · answered Sep 22 '14 at 12:13

4

You can edit pg_hba.conf file to enable trust (no password) auth-method for localhost. Details here:http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

Adding

host    all             all             127.0.0.1/32            trust

to the start of the pg_hba.conf file will enable no-password login from 127.0.0.1/32 (localhost)

answered Sep 22 '14 at 12:13

Ihor Romanchenko

26,995
8
48
44

I tried to change the text in config file list this: localhost:5432:*:postgres:trust
but this does not work. Is something wrong with it? Also tried localhost:5432:*:postgres:iden but this is also not working. – Cagg Sep 22 '14 at 12:16
1

Format of your string is not the one that `pg_hba.conf` needs. try the string I posted in the answer. After the change to `pg_hba.conf` - restart the server. – Ihor Romanchenko Sep 22 '14 at 12:19
I edited the the lines at the bottom of the file like this and now it works. Thanks for your help: # IPv4 local connections: host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 trust – Cagg Sep 22 '14 at 12:49
@user3789876 Do not forget to remove the line after you fixed the login. – Ihor Romanchenko Sep 22 '14 at 13:13

score 3 · Answer 2 · answered Feb 27 '16 at 10:13

First go to the pg_hba.conf file and open it in any editor (notepad or notepad++).

Location of pg_hba.conf is mainly

C:\Program Files\PostgreSQL\9.3\data

Change method as md5 to trust

#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

Instead of

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

then save it.

Run Command prompt as administrator and goto

C:\Program Files\PostgreSQL\9.3\bin

directory through cmd.

Run reload postgres service command

pg_ctl.exe reload -D "C:\Program Files\PostgreSQL\9.3\data"

Got to PgAdmin and connect localhost database without using password

Go to the properties

Change the password in Definition tab

Then again change method trust to md5 .

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Again Run reload postgres service command

pg_ctl.exe reload -D "C:\Program Files\PostgreSQL\9.3\data"

Null password failing login for postgres 9.3 windows

2 Answers2