PSQL-restore error user

 

How to fix an error: password authentication failed for the user in PostgreSQL

Hassan Al Najjar

·

Follow

·

I have two solutions for this Error.

First solution :

open Terminal and write this command in terminal

sudo editor /etc/postgresql/<postgresql version >/main/pg_hba.conf

• you can click Tab , if you didn’t know PostgreSQL version

Then, go to the bottom of the file you just played and edit these lines
- if scroll didn’t work you can use keyboard buttons to scroll down

# TYPE  DATABASE        USER            ADDRESS                 METHOD

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

• you must change peer and md5 to trust to be as this

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "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

• peer means it will trust the identity (authenticity) of the UNIX users. So not asking for a password.
• md5 means it will always ask for a password, and validate it after hashing with MD5.
• trust means it will never ask for a password, and always trust any connection.

Then follow these steps to fix Error:-

1- click Ctrl + s to save your edits.
2- click Ctrl + x to close the file.
3- run this command sudo service postgresql restart

Second solution :

If you remember correctly the user postgres has no DB password set on Ubuntu by default. That means, that you can log in to that account only by using the postgres OS user account.

Assuming, that you have root access on the box you can do:

If that fails with a database "postgres" does not exists error, then you are most likely not on a Ubuntu or Debian server :-) In this case simply add template1 to the command:

sudo -u postgres psql template1

If any of those commands fail with an error psql: FATAL: password authentication failed for user "postgres" then check the file /etc/postgresql/8.4/main/pg_hba.conf: There must be a line like this as the first non-comment line:

local   all         postgres                          ident

For newer versions of PostgreSQL ident actually might be peer. That's OK also.

Inside the psql shell you can give the DB user postgres a password:

ALTER USER postgres PASSWORD 'newPassword';

You can leave the psql shell by typing Ctrl-D or with the command \q.

Now you should be able to give pgAdmin a valid password for the DB superuser and it will be happy too. :-)