Postgresql – psql: FATAL: Ident authentication failed for user “postgres”

postgresql

I have installed PostgreSQL and pgAdminIII on my Ubuntu Karmic box.

I am able to use pgAdminIII successfully (i.e. connect/log on), however when I try to login to the server using the same username/pwd on the command line (using psql), I get the error:

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

Does anyone now how to resolve this issue?

Best Answer

The following steps work for a fresh install of postgres 9.1 on Ubuntu 12.04. (Worked for postgres 9.3.9 on Ubuntu 14.04 too.)

By default, postgres creates a user named 'postgres'. We log in as her, and give her a password.

$ sudo -u postgres psql
\password
Enter password: ...
...

Logout of psql by typing \q or ctrl+d. Then we connect as 'postgres'. The -h localhost part is important: it tells the psql client that we wish to connect using a TCP connection (which is configured to use password authentication), and not by a PEER connection (which does not care about the password).

$ psql -U postgres -h localhost

Related Solutions

Linux – How to configure postgresql for the first time

The other answers were not completely satisfying to me. Here's what worked for postgresql-9.1 on Xubuntu 12.04.1 LTS.

Connect to the default database with user postgres:

sudo -u postgres psql template1
Set the password for user postgres, then exit psql (Ctrl-D):

ALTER USER postgres with encrypted password 'xxxxxxx';
Edit the pg_hba.conf file:

sudo vim /etc/postgresql/9.1/main/pg_hba.conf

and change "peer" to "md5" on the line concerning postgres:

local all postgres ~~peer~~ md5

To know what version of postgresql you are running, look for the version folder under /etc/postgresql. Also, you can use Nano or other editor instead of VIM.
Restart the database :

sudo /etc/init.d/postgresql restart

(Here you can check if it worked with psql -U postgres).
Create a user having the same name as you (to find it, you can type whoami):

sudo createuser -U postgres -d -e -E -l -P -r -s <my_name>

The options tell postgresql to create a user that can login, create databases, create new roles, is a superuser, and will have an encrypted password. The really important ones are -P -E, so that you're asked to type the password that will be encrypted, and -d so that you can do a createdb.

Beware of passwords: it will first ask you twice the new password (for the new user), repeated, and then once the postgres password (the one specified on step 2).
Again, edit the pg_hba.conf file (see step 3 above), and change "peer" to "md5" on the line concerning "all" other users:

local all all ~~peer~~ md5
Restart (like in step 4), and check that you can login without -U postgres:

psql template1

Note that if you do a mere psql, it will fail since it will try to connect you to a default database having the same name as you (i.e. whoami). template1 is the admin database that is here from the start.
Now createdb <dbname> should work.

Postgresql – ‘password authentication failed for user “postgres”‘

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

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

sudo -u postgres psql

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 CtrlD 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. :-)

Best Answer

Related Solutions

Linux – How to configure postgresql for the first time

Postgresql – ‘password authentication failed for user “postgres”‘

Related Topic