Q. I’ve installed Postgresql under Red Hat Enterprise Linux 5.x server. I’ve created username / password and database. But when I try to connect it via PHP or psql using following syntax:
psql -d myDb -U username -W
It gives me an error that read as follows:
psql: FATAL: Ident authentication failed for user “username”
How do I fix this error?
A. To fix this error open PostgreSQL client authentication configuration file /var/lib/pgsql/data/pg_hba.conf :
# vi /var/lib/pgsql/data/pg_hba.conf
This file controls:
- Which hosts are allowed to connect
- How clients are authenticated
- Which PostgreSQL user names they can use
- Which databases they can access
By default Postgresql uses IDENT-based authentication. All you have to do is allow username and password based authentication for your network or webserver. IDENT will never allow you to login via -U and -W options. Append following to allow login via localhost only:
local all all trust host all 127.0.0.1/32 trust
Save and close the file. Restart Postgresql server:
# service postgresql restart
Now, you should able to login using following command:
$ psql -d myDb -U username -W
🐧 39 comments so far... add one ↓
Postfix SASL ERROR: Password not accepted from…
PHP Fatal error: Allowed Memory Size of 20971520…
CentOS: rpmdb: PANIC: fatal region error detected;…
Mediawiki PHP Fatal error: Cannot redeclare…
axfr-get: fatal: unable to parse AXFR results:…
PostgreSQL psql: could not connect to server:…
ERROR: Test failed: [SSL: CERTIFICATE_VERIFY_FAILED]…
| Category | List of Unix and Linux commands |
|---|---|
| Disk space analyzers | ncdu • pydf |
| File Management | cat |
| Firewall | Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04 |
| Network Utilities | NetHogs • dig • host • ip • nmap |
| OpenVPN | CentOS 7 • CentOS 8 • Debian 10 • Debian 8/9 • Ubuntu 18.04 • Ubuntu 20.04 |
| Package Manager | apk • apt |
| Processes Management | bg • chroot • cron • disown • fg • jobs • killall • kill • pidof • pstree • pwdx • time |
| Searching | grep • whereis • which |
| User Information | groups • id • lastcomm • last • lid/libuser-lid • logname • members • users • whoami • who • w |
| WireGuard VPN | Alpine • CentOS 8 • Debian 10 • Firewall • Ubuntu 20.04 |
I did the modifications, and get an error message (pg version 8.3):
psql: FATAL: missing or erroneous pg_hba.conf file
HINT: See server log for details.
Hi, its very urgent.
I m continuously getting below error, while running JSP file in jakarta-tomcat.
PSQLException: FATAL: Ident authentication failed for user “root”
I am trying to connect to a database ‘bedrock’. I am able to login & see the DB by ‘psql -d bedrock -U root’ from cmd-prompt. But the same does not happen from JSP file. Why is it so?? Please help me, its been 5 days struggling like mads……….
Hey
Tanks
Hi, I have the same problem. I’ve created a program in perl, which contains connector to database DBI, and driver DBD::Pg. But I have still the same answer Fatal: Ident Authetification failed for user postgres…
this is the connector:
my $dbh = DBI->connect(“DBI:Pg:dbname=test;host=localhost”, “postgres”, “”, {‘RaiseError’ => 1});
please help…
Tank you!
Thank You! saved me once :-)
Thanks saved one life here!
@Vivek Gite
Your description is causing an error:
FATAL: missing or erroneous pg_hba.conf file
this is coming from missing column:
host all 127.0.0.1/32 trust
Here is what the working line looks like
host all all 127.0.0.1/32 trust
hmm… this is not working for me … any other ideas?
trust means to allow with no password. The command in the example will prompt for a password, but hitting enter will get you right in. You might want to use “password” instead of “trust.” See http://developer.postgresql.org/pgdocs/postgres/auth-pg-hba-conf.html.
Felicia is exactly right, following this article is a potential security risk for your database. Use with caution!
Thanks, its worked for me. Saved my time.
worked like a charm thanks!
I found the file at /etc/postgresql/8.4/main/pg_hba.conf
I had to comment out most of the lines:
# local all all ident
# host all all 127.0.0.1/32 md5
# host all all ::1/128 md5
and add:
local all all trust
host all all 127.0.0.1/32 trust
at the bottom of pg_hba.conf
save,
sudo su – postgres
service postgresql restart
then I could do stuff (Rails 3 with RSpec)
I’m using postgresql version 8.4.5. I dont have /var/lib/pgsql/data/pg_hba.conf file. where is file located in this version?
Hi Paul,
If you had installed pgsql using *.bin file. it will be installed in /opt/Postgres/* by default. I think this file will be in /opt/P***/8.4/data/. Better you find this file uinsg “find ./ -name pg_hba.conf” in installed location.
thanks Ramanathan
If you are on Ubuntu it will be /etc/postgres/8.4/main/pg_dba.conf
i’ve solved this problem by adding the following line :
local all user md5
before:
local all all ident sameuser
in my pg_hba.conf. I think it’s more elegant and secure solution.
Make sure to put in password instead trusted, and check top line, it usually have separate line for default user. Check config twice!
Why restart the service and kick everyone out? Just reload the config file via:
pg_ctl reload
This is what I had to do to fix mine, although I’m using Windows. When I attempted to use Cmd it would just give me the same error. Spent many hours attempting to fix that and finally I found that I had to create a user and database using a GUI on my server that had the same username and a password as it was asking for. Don’t know if that’ll help anyone, but that did the trick for me. =)
P.S.
My pg_hba are the defaults:
Host all all 127.0.0.1/32 md5
Host all all ::1/128 md5
hey chris,I am currently in that situation..
how do i find if i have got the gui installed along with postgresql8.2?
Thanks Rod McLaughlin, your script worked for me. Vivek, could you please fix your post? Your config is completely broken for PG 8.4 and above.
Thanks a ton !
Thanks for the pretty explained post!
Thanks you so much……….password authentication just bypass
What can I do, if I see this? (I couldn’t restart the server)
CET LOG: local connections are not supported by this build
CET CONTEXT: line 88 of configuration file “D:/pgdata/pg_hba.conf”
CET FATAL: could not load pg_hba.conf
In pg_hba.conf added:
local all all all trust
host all all 127.0.0.1/32 trust
This TRUST mechanism can be used on a per-database basis. For example,
local somedatabase someuser trust
On IPv6 enabled hosts, you also must set a TRUST method for localhost too.
I did it using :
# IPv6 local connections:
host somedatabase someuser ::1/128 trust
host all all ::1/128 ident
Great!! thanks :)
It has already been said, but again don’t set it to “trust”… that means you get in without a password. md5 and password auth are not much better, but surely don’t use trust.
Dear author, please fix that. Thanks.
@unbekannt
From the path of your pg_hba.conf file, which is apparently
‘D:\pgdata\pg_hba.conf’, you are running in some version of windows.
the ‘local’ at the beginning of the pg_hba.conf lines refers to the type of connection.
‘local’ basically means ‘a unix socket’ — ( unix/linux: man 2 socket; man 7 unix )
Since you are in windows, you have to connect via a TCP/IP socket.
You already have the following line.
You should just comment out the line that says
because I dont believe that you can use Unix sockets in Postgres for Windows.
Also, as said before, you should preferably change all the ‘trust’ keywords to ‘md5’ as stated earlier. You should try not to use ‘password’ because the passwords are sent in cleartext. I know the md5 algo is weak compared to sha-2, or sha-3, but ‘md5’ is still better than ‘password’. Also, a more complicated but more flexible setup would be to use LDAP or Kerberos/GSSAPI via the ‘ldap’ or ‘gss’ keywords respectively.
as far as the ‘host’ at the beginning of the line, if you are connecting to your own machine (127.0.0.1, ::1, localhost), then host is fine, if you are connecting to a remote machine, you should consider setting up SSL certificates for your postgres server. Then you would change the ‘host’ to ‘hostssl’ like the following:
you would change the ip address under ‘address’ to whatever address, or addresses, that your clients would connect from.
Hope this helps anyone. :)
Follow up to my last comment:
with psql you would need to use the ‘-h’ option to tell psql which host to connect to.
if it is your own machine, the localhost, it would be as follows:
psql -h localhost -U your_user_name -d database_name
I’m using postresql 9.2.4 on openSuse 13.1 Linux. I received authentication errors, as above. The postgresql.log reported:
“could not connect to Ident server at address “::1″, port 113 connection refused…”
See RFC 1413. The IPv6 local connections section of pg_hba.conf was edited as follows:
“host all all ::1/128 trust”
This allowed me to connect but is probably not the most secure. Hope this helps.
Thnxs a lot.. It Worked 4 me
Felt lot of head-ache untill i come to see this. Thanks a lot. I wish i had seen this 2days before. Thankyou once again.
If changes in the /etc/hba.conf file don’t work for you, you might try switching to root and changing:
/var/lib/pgsql/9.3/data/pg_hba.conf
This has worked for me.
Thanks Bro you save my life yuhoooooo
Thanks a lot you save me
This article solved my problem.Thanks a lot.