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.

Posted in Postgresql | 9 Comments

9 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 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. ,