Postgres reset password in PostgreSQL server 8.4

Question

I have more than 20 databases in my PostgreSQL 8.4 server. Someone did something wrong to my Postgres and it is not opening anymore. It gives the following error:

error connecting to the server fatal role postgres is not permitted to log in

I did lot to recover it but it still not working. I have PostgreSQL server 8.4 in windows XP.

pg_hba.conf file configuration is:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# IPv4 local connections:
#local   all   postgres                         ident sameuser
host    all         all         127.0.0.1/32          trust
#host    all         postgres trust 
#host    all         all         192.168.44.12/32      trust
#host    all         all         192.168.44.103/32     trust
# IPv6 local connections:
#host    all         all         192.168.44.18/32      trust
# host   all     all     ::1/128     md5

I suspect that the problem is here because someone modified the ROLE of Postgres user in NOT LOG IN...

is the database on the same machine you are connecting from? — cfeduke
– cfeduke, Commented Mar 2, 2013 at 4:40
did you create any other users than the default postgres user? — cfeduke
– cfeduke, Commented Mar 2, 2013 at 4:48
your pg_hba.conf allows all users to connect; do you have a createuser.exe program hanging around with your Postgres binaries? — cfeduke
– cfeduke, Commented Mar 2, 2013 at 5:03

Bleeding Fingers · Accepted Answer · 2013-07-17 06:04:49Z

4

Possibly try single user mode. Stop postgres, then run it from the command line:

postgres --single -D path/to/your/postgres/directory

Then:

ALTER ROLE postgres WITH LOGIN

Use Ctrl-D to terminate the session.

That should allow the postgres user to log into the server once again.

edited Jul 17, 2013 at 6:04

Bleeding Fingers

7,1508 gold badges49 silver badges80 bronze badges

answered Mar 2, 2013 at 5:08

cfeduke

23.2k10 gold badges64 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

neeraj bharti Over a year ago

Same error is coming postgres is not permited to log in while creating new user through createuser.exe

cfeduke Over a year ago

Added single user mode comment.

neeraj bharti Over a year ago

I got an error Postgres is not recognized as the internal or external command, operable program or batch file

neeraj bharti Over a year ago

the command i given in the cmd is that.. postgres --single -D C:\Program Files\PostgreSQL\8.4\bin\postgres.exe

neeraj bharti Over a year ago

Thanks lot @ cfeduke my problem is solved........... may god bless you always.....

Collectives™ on Stack Overflow

Postgres reset password in PostgreSQL server 8.4

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related