Ident authentication failed for user – PostgreSQL

« Unable to delete file from thumb drive !!!

Turning off automatic gnome-session »

Ident authentication failed for user – PostgreSQL

October 10, 2008 by msverman

Good Morning !!!

Today morning i open the my desktop terminal and just tried to work on the database by typing

verman@verman-desktop:~$ psql -U postgres -d verman

while try to connect, I got the following error message

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

Then i found that you need to “trust” local users in pg_hba.conf,this file
is said to be the PostgreSQL Client Authentication Configuration File.

My pg_hba.conf file looks like below
———————————————————————
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# “local” is for Unix domain socket connections only
local all all ident sameuser
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::2/128 md5
———————————————————————–

then i comment the present local type and added a new line
to access database for all local users

———————————————————————
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# “local” is for Unix domain socket connections only
#local all all ident sameuser

local all all md5

# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::2/128 md5
———————————————————————–

then restarted the postgresql database

verman@verman-desktop:~$ sudo /etc/init.d/postgresql-8.3 restart

then i connect the database

verman@verman-desktop:~$ psql -U postgres -d verman
Password for user admin:
Welcome to psql 8.3.0, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

verman=# \q

Now it is working fine.

Like this:

Be the first to like this post.

Posted in Postgresql | 17 Comments

17 Responses

on May 4, 2009 at 3:52 am | Reply doug

thanks. this saved me from certain insanity.
on June 9, 2009 at 5:01 pm | Reply Evan

Whoa, I was getting pretty frustrated too.
I work in a bio lab and my boss told me I will soon take over the routine database entry on PostgreSQL. So to learn this database I installed the client and server from the Synaptic Package Manager. It invisibly installed — barely knew anything had worked.
And then it treated me like an invader on my own computer.
Anyway, thanks for the advice
- on June 10, 2009 at 2:17 am | Reply msverman
  
  Hi Evan !
  
  if you want to learn more on postgresql, Kindly follow the link “http://www.postgresql.org/docs/manuals”
  
  you can get Technical Documentation,books and manuals for learning postgresql.
  
  thanks,
  
  verman
on June 10, 2009 at 6:56 pm | Reply Evan

Thanks Verman,

I am very new to the Linux.
I had managed to install postgreSQL via ubuntu synaptic package manager. But then I tried to just start using it.
In case anyone reads this and is in a similar situation, note:
After you install the SQL you still have to follow a few line command instructions which are given here:

http://www.ubuntugeek.com/howto-setup-database-server-with-postgresql-and-pgadmin3.html

before it will run. Especially you have to reset the password so it knows you have permission.

-Evan
- on June 11, 2009 at 7:23 am | Reply msverman
  
  that’s great Evan !
  
  Very much a useful link to know about postgreSQL.
  
  thanks,
  
  - verman
- on April 17, 2012 at 11:25 pm | Reply Tony
  
  That was one useful link you posted there. Solved a really serious issue for me! Thanks a mil…
  
  Tony
on July 21, 2009 at 9:11 am | Reply Muthukumar

Dear Verman

Your blogs are excellent…keep going!!!

I am basically Oracle Database Administrator and also managing some of the Postgres databases.

I have one question regarding Postgres:

In one of the database, the user is not able access where as i am able to access without any issues.

The user is connecting using pgadminIII to a db and got FATAL: No pg_hba.conf entry for the host x.x.x.x, user postgres, database dbname.

I resolved this by adding his system’s IP Addr and Subnet with password authentication.

Now the error is little different that, FATAL: Password authentication failed for user “postgres”.

Can you please help me in this regard.

Thanks and regards,
Muthu
- on July 22, 2009 at 4:33 am | Reply verman
  
  if you type wrong password you get this error
  
  psql: FATAL: password authentication failed for user
  
  try to create a user
  
  # adduser admin
  # passwd admin
  
  then login as superuser
  
  # su – postgres
  
  Connect the database
  
  $ psql -d template1 -U postgres
  
  Create a user ‘admin’
  
  template1=# CREATE USER admin WITH PASSWORD ‘myPassword’;
  
  Add a database
  
  template1=# CREATE DATABASE admindb;
  
  Grant all privileges on database
  
  template1=# GRANT ALL PRIVILEGES ON DATABASE admindb to admin;
  
  Type \q to quit
  
  template1=# \q
  
  Test admin user login
  
  $ su – admin
  $ psql -U admin -d admindb
  
  you will get the output as :
  
  Welcome to psql 8.3.0, the PostgreSQL interactive terminal.
  
  Type: \copyright for distribution terms
  \h for help with SQL commands
  \? for help with psql commands
  \g or terminate with semicolon to execute query
  \q to quit
  
  admindb=>
  
  —————————————–
  
  I tested in my desktop by typing wrong password
  
  verman@verman-desktop:~$ psql -U verman -d vermandb
  Password for user verman:
  psql: FATAL: password authentication failed for user “verman”
  
  after I connected with right password, it worked fine
  
  verman@verman-desktop:~$ psql -d vermandb -U verman
  Password for user verman:
  Welcome to psql 8.3.0, the PostgreSQL interactive terminal.
  
  Type: \copyright for distribution terms
  \h for help with SQL commands
  \? for help with psql commands
  \g or terminate with semicolon to execute query
  \q to quit
  
  vermandb=>
  
  if does’nt work just mail me your pg_hba.conf
- on July 23, 2009 at 8:18 am | Reply msverman
  
  By default there is no password for the user postgres.
  
  Open pg_hba.conf
  
  find the line that say “md5″
  
  like — > local all all md5
  
  just change ‘md5′ to ‘trust’
  
  then restart your postgresql database.
  
  $ sudo /etc/init.d/postgresql-8.3 restart
  
  $ su – postgres
  
  postgres@verman-desktop:~$ psql template1
  
  Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
  
  Type: \copyright for distribution terms
  \h for help with SQL commands
  \? for help with psql commands
  \g or terminate with semicolon to execute query
  \q to quit
  
  template1=#
  template1=# alter user postgres with password ‘mypasswd’;
  ALTER ROLE
  
  Now the password of user postgres will be ‘mypasswd’
  
  then type “\q” to quit
  
  template1=# \q
  postgres@verman-desktop:~$
  
  I think this will be problem on your part.
on October 22, 2009 at 10:37 pm | Reply Coder33

Rig some strobes in the structure, bouced up, and maybe even add extra diffusion at the accordion level. ,
on December 10, 2009 at 7:44 am | Reply sathyz

verman@verman-desktop:~$ psql -U postgres -d verman
psql: FATAL: Ident authentication failed for user “postgres”

ident authentication works by using the user name logged into the system.

The above will work if you have logged in as postgres user.

$ sudo -u postgres sh
$ psql
psql (8.4.1)
Type “help” for help.

postgres=#

Alternatively if you have the user “verman” in your postgres. Just typing “psql” should work with ident. It will try to allow you to login as “verman” connecting to db “verman”
on January 25, 2010 at 1:50 pm | Reply Marcos

WWwwoooowWW !!!!!
really thanks man !!!
it works so fine

thanks
on April 14, 2010 at 2:16 am | Reply Tim

For those who are looking for the pg_hba.conf file, on ubuntu (and possibly other distros) it can be found in:

/etc/postgresql/8.4/main/

(replace 8.4 with whatever version you’re using)
on October 7, 2010 at 11:04 am | Reply dmydry

Thank you very much, you helped me
on October 22, 2010 at 2:02 pm | Reply jareksuaff

Hi ,

Thank you for sharing

You saved my day !
I had to change system locale and then pg_dropcluster –stop 8.3 main to have utf-8 in my database. After recreating the cluster I was getting Ident authentification failed .
Now it works again

Thanks

and

Regards
on June 23, 2011 at 8:47 am | Reply Hg

How do you edit the pg_hba.conf file?
I have zero knowledge of Unix- the command line says

root@debian:~#

What do I type from here?
on August 2, 2011 at 5:13 am | Reply gen1us2k

rm -rf /*