Postgres Error and troubleshooting

===============================================================================
What if we directly want to access the db without logining into postgres user
-----------------------------------------------------------------------------

Step 1 login as root and give password for postgres user if not given
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
login as: root
root@10.249.95.96's password:
Last login: Fri Sep 11 21:19:05 2015 from 10.33.177.202
[root@localhost ~]# sudo -u postgres psql
could not change directory to "/root"
psql (8.4.9)
Type "help" for help.

postgres=# \password
Enter new password:    ##here password is postgres
Enter it again:

Step 2 quit DB
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
postgres=# \q

Step 3 Try to login to PSQL db from root user or any other user
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------------------------------------------
[root@localhost ~]# psql -U postgres

psql: FATAL:  Ident authentication failed for user "postgres"
You have new mail in /var/spool/mail/root
[root@localhost ~]# psql -U postgres -h localhost
psql: FATAL:  Ident authentication failed for user "postgres"
[root@localhost ~]# cd /var/lib/pgsql/
You have mail in /var/spool/mail/root
------------------------------------------------------------------------
You are getting FATAL:  Ident authentication failed for user "postgres" Error

We need to Allow local user connections to access pgssl, for this edit pg_hba.conf
===============================================================

The file pg_hba.conf governs the basic constraints underlying connection to PostgreSQL. By default, these settings are very conservative. Specifically, local connections are not allowed for the postgres user.

To allow this:

As a super user, open /var/lib/pgsql/data/pg_hba.conf file
---------------------------------------------------------------
[root@localhost pgsql]# ls
backups  data  pgstartup.log
[root@localhost pgsql]# cd data/
[root@localhost data]# ls
base         pg_ident.conf  pg_subtrans  pg_xlog
global       pg_log         pg_tblspc    postgresql.conf
pg_clog      pg_multixact   pg_twophase  postmaster.opts
pg_hba.conf  pg_stat_tmp    PG_VERSION   postmaster.pid
[root@localhost data]# vim pg_hba.conf

Scroll down to the line that describes local network. It may look like this:
------------------------------------------------------------------------------

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

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


Change the ident method to trust or md5.
------------------------------------------------------------------------------

Using trust allows the all local users to connect to the database without a password. This is convenience, but insecure. If you want a little more security, replace trust with md5, and use the password you set in the previous section to connect.

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

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



Save and close the file.
-----------------------------
Restart PostgreSQL:
-----------------------------
[root@localhost data]# sudo /etc/init.d/postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]


To test your connection using psql, run the following command:psql -U postgres -W and enter your password when prompted. You should be able to access the psql console.
--------------------------------------------------------------------------------------------------------------------------------------
[root@localhost data]# psql -U postgres -W
Password for user postgres:
psql (8.4.9)
Type "help" for help.

postgres=# \q
You have mail in /var/spool/mail/root

[root@localhost data]# psql -U postgres
Password for user postgres:
psql (8.4.9)
Type "help" for help.

postgres=# \q