Centos – PostgreSQL can’t authenticate from localhost, but only from remote

centospostgresql

I have a server with postgreSQL running. A few days ago I set it up so that I can access the database remotely from my home PC and that worked (based on this guide: http://www.cyberciti.biz/tips/postgres-allow-remote-access-tcp-connection.html). Now I noticed that for whatever reason, I can not access the database via localhost or 127.0.0.1.

psql mydatabase works without any problem. psql -h myDomain.net mydatabase also works. psql -h localhost mydatabase tells me

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

I tried reverting the changes that I made earlier to get remote access to the DB to no avail.

What could be the cause of this problem?

Best Answer

ok I found out the problem. Going to the file /var/lib/pgsql/data/pg_hba.conf, looking for the line

host all all 127.0.0.1/32 ident

and replacing ident with trust apparently fixed the issue.

Related Solutions

Postgresql – Enable both ident and md5 authentication in PostgreSQL

https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html indicates that the order of entries maters, specifically:

Since the pg_hba.conf records are examined sequentially for each connection attempt, the order of the records is significant. Typically, earlier records will have tight connection match parameters and weaker authentication methods, while later records will have looser match parameters and stronger authentication methods. For example, one might wish to use trust authentication for local TCP/IP connections but require a password for remote TCP/IP connections. In this case a record specifying trust authentication for connections from 127.0.0.1 would appear before a record specifying password authentication for a wider range of allowed client IP addresses.

(so try reversing the order - put the line you are adding at the top)

Ssh – can not connect via SSH to a remote Postgresql database

Check if the remote host allow tunneling. You can check with the client if the tunnel is refused with:

ssh -v -L 3333:myHost:5432 myUser@myHost

Best Answer

Related Solutions

Postgresql – Enable both ident and md5 authentication in PostgreSQL

Ssh – can not connect via SSH to a remote Postgresql database

Related Topic