Postgresql: password authentication failed for user “postgres”

Question

Recently, I installed the PostgreSQL 8.4, Postgres client and Pgadmin 3. The authentication then failed for the user "postgres" for the console client and the Pgadmin. I had typed the user as "postgres" and the password as "postgres", sinceit was working before. But now the authentication is failed. I have done this before a few times without facing this issue. 

psql -U postgres -h localhost -W

Password for user postgres: 

psql: FATAL:  password authentication failed for user "postgres"

FATAL:  password authentication failed for user "postgres"

Can anyone help with this?

Answer 1

If I recall correctly the user "postgres" has no database password set on Ubuntu by default. This implies, that you could log in to that account simply by using the "postgres" OS user account.

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

sudo -u postgres psql

If this fails with a database "postgres" does not exists error, then you are most likely not running on a Ubuntu or Debian server. In this case, just 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 examine 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 the newer versions of the PostgreSQL ident actually might be a peer. That's OK also.

Then Inside the psql shell, you can give the database user "postgres" a password:

ALTER USER postgres PASSWORD 'newPassword';

You can leave the psql shell by pressing Ctrl+D or with the command \q.

Now you could give pgAdmin a valid password for the DB superuser.

Interested in SQL ? Check out this SQL Certification course by Intellipaat.