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

Question

I have installed PostgreSQL and pgAdminIII on my Ubuntu Karmic box.

I am able to use pgAdminIII successfully (i.e. connect/log on), however when I try to login to the server using the same username/pwd on the command line (using psql), I get the error:

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

Does anyone now how to resolve this issue?

• Andrea Araldo: This stackoverflow post worked for me: stackoverflow.com/a/18664239/2110769

Answer 1

The following steps work for a fresh install of postgres 9.1 on Ubuntu 12.04. (Worked for postgres 9.3.9 on Ubuntu 14.04 too.)

By default, postgres creates a user named 'postgres'. We log in as her, and give her a password.

$ sudo -u postgres psql
\password
Enter password: ...
...

Logout of psql by typing \q or ctrl+d. Then we connect as 'postgres'. The -h localhost part is important: it tells the psql client that we wish to connect using a TCP connection (which is configured to use password authentication), and not by a PEER connection (which does not care about the password).

$ psql -U postgres -h localhost

• Sameer: If you set PGHOST=localhost you don't need to specify the -h option every time. This also works with other pg_* commands such as pg_dump.
• O.O: It does documented in here: help.ubuntu.com/12.04/serverguide/postgresql.html
• mivk: This was needed to enable a Mediawiki install on Debian with PostgreSQL.
• Erdinc Ay: bad because there is a security issue, see here: serverfault.com/questions/110154/…
• L Fischman: THANKS - I was totally confused because it was asking for my Windows login password. Once I specified the line above, it pointed to the right location and worked!

Answer 2

Did you set the proper settings in pg_hba.conf?

See https://ubuntu.com/server/docs/databases-postgresql how to do it.

• Sean: This doesn't work for me. I've spent hours on it! All I want to do is run psql commands in my terminal. What do I need to make the file look like to do this??
• JLarky: @SeanA you need something like 'sudo -u postgres psql'
• omrsin: Don't forget to ';' at the end of every statement on psql. Sounds silly but it happens hehe.
• Abe Petrillo: For those using rails, I had to set the the pg_hba.conf, and change 'ident' to 'password'. Changing it to trust did not work.
• Bryan Ash: Why yes, I did read the fine manual, but it didn't help one bit. It does not mention the error, or how to fix it.

Answer 3

Edit the file /etc/postgresql/8.4/main/pg_hba.conf and replace ident or peer by either md5 or trust, depending on whether you want it to ask for a password on your own computer or not. Then reload the configuration file with:

/etc/init.d/postgresql reload

• Tyler Liu: one command restart postgresql: /etc/init.d/postgresql restart
• Frank Heikens: Why a restart when a reload is all you need?
• shaytac: In this case: "/etc/init.d/postgresql-8.4 reload"
• Jonatas CD: this one here worked for me. Change from peer to md5 was enough.
• Mike O'Connor: OK, I'm a postgresql noob, but I must report that only restart worked for me, not reload--- after changes to /etc/postgresql/9.5/main/pg_hba.conf (changing peer to trust).

Answer 4

You're getting this error because you're failing client authentication. Based on the error message, you probably have the default postgres configuration, which sets client authentication method to "IDENT" for all PostgreSQL connections.

You should definitely read section 19.1 Client Authentication in the PostgreSQL manual to better understand the authentication settings available (for each record in pg_hba.conf), but here is the relevant snippet to help with the problem you're having (from the version 9.5 manual):

trust

Allow the connection unconditionally. This method allows anyone that can connect to the PostgreSQL database server to login as any PostgreSQL user they wish, without the need for a password or any other authentication. See Section 19.3.1 for details.

reject

Reject the connection unconditionally. This is useful for "filtering out" certain hosts from a group, for example a reject line could block a specific host from connecting, while a later line allows the remaining hosts in a specific network to connect.

md5

Require the client to supply a double-MD5-hashed password for authentication. See Section 19.3.2 for details.

password

Require the client to supply an unencrypted password for authentication. Since the password is sent in clear text over the network, this should not be used on untrusted networks. See Section 19.3.2 for details.

gss

Use GSSAPI to authenticate the user. This is only available for TCP/IP connections. See Section 19.3.3 for details.

sspi

Use SSPI to authenticate the user. This is only available on Windows. See Section 19.3.4 for details.

ident

Obtain the operating system user name of the client by contacting the ident server on the client and check if it matches the requested database user name. Ident authentication can only be used on TCP/IP connections. When specified for local connections, peer authentication will be used instead. See Section 19.3.5 for details.

peer

Obtain the client's operating system user name from the operating system and check if it matches the requested database user name. This is only available for local connections. See Section 19.3.6 for details.

ldap

Authenticate using an LDAP server. See Section 19.3.7 for details.

radius

Authenticate using a RADIUS server. See Section 19.3.8 for details.

cert

Authenticate using SSL client certificates. See Section 19.3.9 for details.

pam

Authenticate using the Pluggable Authentication Modules (PAM) service provided by the operating system. See Section 19.3.10 for details.

So ... to solve the problem you're experiencing, you could do one of the following:

1. Change the authentication method(s) defined in your pg_hba.conf file to trust, md5, or password (depending on your security and simplicity needs) for the local connection records you have defined in there.

2. Update pg_ident.conf to map your operating system users to PostgreSQL users and grant them the corresponding access privileges, depending on your needs.

3. Leave the IDENT settings alone and create users in your database for each operating system user that you want to grant access to. If a user is already authenticated by the OS and logged in, PostgreSQL won't require further authentication and will grant access to that user based on whatever privileges (roles) are assigned to it in the database. This is the default configuration.

Note: The location of pg_hba.conf and pg_ident.conf is OS dependent.

• venkatareddy: For me, this is the best answer. When you know all these options you can easily tweak the conf. And especially when you are on Dev machine you can simply set 'ident' for all entries to avoid wasting your time. Thanks
• edencorbin: This was helpful for me too. In my case the pg_hba.conf file was set to peer, I changed it to password. Note that from a vanilla install I also had to set a password for the postgres user, sudo su - postgres psql, \password set a password. Then launch a default connection from pdgadmin3 with username postgres and your password you set.
• JosephK: And where is that file found? Granted, you may need to make a list, since there seems to be no consistency between versions. I guess I'll just run find on '/' .
• Mike O'Connor: On Ubuntu-16.04 it's /etc/postgresql/9.6/main/pg_hba.conf.
• Vyrnach: As someone who is new to psql, this is a huge help and should be the accepted answer as it caters to various authentication methods

Answer 5

Simply adding the -h localhost bit was all mine required to work

• Michael Pell: Do we know why this fixes it?
• boulder_ruby: postgresql's defaults aren't set reasonably. They may have fixed it by now, I don't know. Obviously the default url should be this_computer = 'http://localhost'
• Avin Mathew: so fully using "pg_dump -U postgres test -f test_1.sql -h localhost" command worked for me
• Kenny Evitt: If you don't include a hostname (via the -h ... option) then psql will, by default, attempt to connect over a Unix-domain socket in which case psql (or maybe the PostgreSQL cluster itself) queries your OS for your user name to use peer authentication, i.e. confirm that your user name matches the PostgreSQL user (role) you specify (which it very well might not). When you specify a hostname, psql will use TCP/IP to connect to the cluster.

Answer 6

For fedora26 and postgres9.6

First, log as user root then enter to psql by the following commands

$ su postgres 

then

$ psql

in psql find location of hba_file ==> means pg_hba.conf

postgres=# show hba_file ; 
 hba_file 
-------------------------------------- 
 /etc/postgresql/9.6/main/pg_hba.conf 
(1 row) 

in file pg_hba.conf change user access to this

host all all 127.0.0.1/32 md5

Answer 7

In case none of the above works for you:

I've done quite a few Postgres installations, but was flummoxed today on a RedHat 6.5 system (installing Postgres 9.3). My typical hba.conf configuration that Aron shows above didn't work. It turned out that my system was using IPV6, and ignoring the IPV4 configuration. Adding the line:

host all all ::1/128 password

allowed me to login successfully.

• Ibn Saeed: Thanks Ethan. I am running Fedora 20 and i have facing the same issue as by the OP. After changing the IPV4 and IPV6 to password. The connection was successful.
• Ethan Brown: You're very welcome. So many times I've benefited from others posts when encountering a programming or system problem. Glad I could give back a bit.
• Rami: This saved my life on Fedora 32!
• zslim: working on Fedora 37 🙏🏻

Answer 8

Out of all the answers above nothing worked for me. I had to manually change the users password in the database and it suddenly worked.

psql -U postgres -d postgres -c "alter user produser with password 'produser';"

I used the following settings:

pg_hba.conf

local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 password 
# IPv6 local connections:
host all all ::1/128 password

Connection is successful finally for the following command:

psql -U produser -d dbname -h localhost -W 

• Harlin: Following this helped me out here, Joseph. Especially, the connection command at the end to help me test it. Also, I'd add a restart of Postgresql service in case anyone is wondering (but I get it that it's implied). Appreciate it much!

Answer 9

In my case, solution here: (for people who concerned) login to postgres:

sudo -i -u postgres
psql
ALTER USER postgres WITH PASSWORD 'postgres'; # type your password here

regards

Answer 10

Hmmm ...

If you can connect with the username and password in pgAdminIII but you can't connect with psql then those two programs are probably connecting to the database differently.

[If you're connecting to different databases, first try connecting to the same database. See below.]

From PostgreSQL: Documentation: 9.3: psql:

If you omit the host name, psql will connect via a Unix-domain socket to a server on the local host, or via TCP/IP to localhost on machines that don't have Unix-domain sockets.

If you're not running something like psql ... -h host_name ..., and you're running Ubuntu, psql should be connecting via a Unix-domain socket, so PostgreSQL probably isn't configured to allow one of the password authentication methods for the postgres user.

You can test this by running:

sudo -u postgres psql

If the above works, your server is probably configured to use peer authentication for local connections by the postgres user, i.e. asking the OS for your user name to confirm that you're postgres.

So It's Probably Your pg_hba.conf File

The full path of the file will be something like /etc/postgresql/9.3/main/pg_hba.conf. You can view it by, e.g. sudo cat /etc/postgresql/9.3/main/pg_hba.conf | more.

If you're omitting the host name in your psql command, you should be able to connect if you add the following entry to your pg_hba.conf file:

# Connection type Database User IP addresses Method
local all postgres md5

[Commented lines in the pg_hba.conf file start with #.]

If you are including the host name in your psql command, add this entry instead:

# Connection type Database User IP addresses Method
host all postgres 127.0.0.1/32 md5

You need to put the entry before any other entries are matched for your connection via psql. If in doubt about where to put it, just put it before the first un-commented line.

More about pg_hba.conf

From PostgreSQL: Documentation: 9.3: The pg_hba.conf File [bold emphasis mine]:

The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no "fall-through" or "backup": if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied.

Note that records are not matched on authentication method. So, if your pg_hba.conf file contains the following entry:

# Connection type Database User IP addresses Method
local all postgres peer

Then you won't be able to connect via:

psql -u postgres

Unless one of these entries is in your pg_hba.conf file above the former entry:

# Connection type Database User IP addresses Method
local all postgres md5
local all postgres password # Unencrypted!
local all all md5
local all all password # Unencrypted!

• Jeffrey 'jf' Lim: late to the game, but, seriously, thanks for putting in the effort to explain things properly so that I understand!
• Zaffer: I have this issue when starting up the docker container, there is no pg_hba.conf file on my ubuntu system but maybe it is in the docker image? Then why is it giving this error for me?
• Kenny Evitt: @Zaffer I'm not sure what you're asking but it doesn't seem like it's necessarily related to this question. I'm also not sure SO (this site) is a good venue for you to get help with whatever your underlying issue is either. I created a chat room and added you to it if you want to discuss your issue in more detail.

Answer 11

You can set the environment variable PGHOST=localhost:

$ psql -U db_user db_name
psql: FATAL: Peer authentication failed for user "db_user"
$ export PGHOST=localhost
$ psql -U db_user db_name
Password for user mfonline:

Answer 12

I found that I had to install an identity server, that listens on port 113.

sudo apt-get install pidentd
sudo service postgresql restart

And then ident worked.

Answer 13

The problem is still your pg_hba.conf file. This line: You can found this file in /etc/postgres/varion/main

local all postgres peer
Should be
local all postgres md5

These are brief descriptions of both options according to the official PostgreSQL docs on authentication methods.

Peer authentication

The peer authentication method works by obtaining the client's operating system user name from the kernel and using it as the allowed database user name (with optional user name mapping). This method is only supported on local connections.

Password authentication

The password-based authentication methods are md5 and password. These methods operate similarly except for the way that the password is sent across the connection, namely MD5-hashed and clear-text respectively.

If you are at all concerned about password "sniffing" attacks then md5 is preferred. Plain password should always be avoided if possible. However, md5 cannot be used with the db_user_namespace feature. If the connection is protected by SSL encryption then password can be used safely (though SSL certificate authentication might be a better choice if one is depending on using SSL).

After altering this file, don't forget to restart your PostgreSQL server. If you're on Linux, that would be sudo service postgresql restart.

Answer 14

my solution on PostgreSQL 9.3 on Mac OSX in bash shell was to use sudo to go into the data folder, and then append the necessary lines to the pg_hba.conf file to allow for all users to be trusted and be able to log in. This is what I did:

# in bash_profile edit PGDATA environmental variable
open ~/.bash_profile
# append this line to bash_profile
export PGDATA="/Library/PostgreSQL/9.3/data"
# reload bash_profile
source ~/.bash_profile
# open pg_hba.conf in vim
sudo vi /Library/PostgreSQL/9.3/data/pg_hba.conf
# append these two lines to the end of the pg_hba.conf file
local all all trust
host all all 127.0.0.1/32 trust
# can now login as user in bash
psql -d <db_name> -U <user_name> -W

• Michael: This is fine for the development server, but I wouldn't recommend this for a production environment as you no longer need a password to connect to the database with these settings.

Answer 15

I've spent more time solving this error that I care to admit.

The order of authentication configuration in pg_hba.conf is relevant in your case I think. The default configuration file includes several lines in a vanilla install. These defaults can match the conditions of your authentication attempts resulting in a failure to authenticate. It fails regardless of additional configuration added at the end of the .conf file.

To check which line of configuration is use make sure to look at the default log file for messages. You might see something like this

LOG: could not connect to Ident server at address "127.0.0.1", port 113: Connection refused
FATAL: Ident authentication failed for user "acme" 
DETAIL: Connection matched pg_hba.conf line 82: "host all all 127.0.0.1/32 ident"

It turns out this default line is causing the rejection.

host all all 127.0.0.1/32 ident

try commenting it out.

Answer 16

Its related to configuration issue of PostgreSQL installation:

Configure # TYPE DATABASE USER ADDRESS METHOD section in below mentioned conf file

Find and Edit /var/lib/pgsql/10/data/pg_hba.conf or based on your file location to update method(md5). Update entry in the file if not existing for your config by comparing as below:

# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 0.0.0.0/0 md5
# IPv6 local connections:
host all all ::1/128 md5

Configure CONNECTIONS AND AUTHENTICATION section in below mentioned conf file

Find and Edit /var/lib/pgsql/10/data/postgresql.conf or based on your file location Update Listen Address and Port

listen_addresses = '*' // # what IP address(es) to listen on;
 # comma-separated list of addresses;
 # defaults to 'localhost'; use '*' for all
port = 5432 // Set port as 5432

Restart your PostgreSQL:

sudo systemctl restart postgresql-10 # Update service name based on your installation

Answer 17

If you've done all this and it still doesn't work, check the expiry for that user:

Postgres password authentication fails

Answer 18

I had similar problem and I fixed it in pg_hba.conf when removing all ident methods even for IP6 address (in spite I have only IP4 on machine).

host all all 127.0.0.1/32 password
host all all ::1/128 password
#for pgAdmin running at local network
host all all 192.168.0.0/24 md5

Answer 19

One hack around this is to edit pg_hba.conf

sudo vi /etc/postgresql/9.3/main/pg_hba.conf

To temporarily

# Database administrative login by Unix domain socket
local all postgres trust

At this point you are done. For security, then go and

sudo -u postgres psql template1
ALTER USER postgres with encrypted password 'your_password';

then go back and set pg_hba.conf back to

# Database administrative login by Unix domain socket
local all postgres md5

Answer 20

If you are using it on CentOS,you may need to reload postgres after making the above solutions:

systemctl restart postgresql-9.3.service

• Neil: It's now just postgresql
• Ikrom: @NeilChowdhury postgresql service still has got version in service name at least in linux systems. Run this command to see systemctl status | grep postgres

Answer 21

Any of them above didn't work for me. The steps which worked for me are as follows:

Step 1- I uninstalled and re-installed but this time I made a check.

Step 2- This time I unchecked the Stack Builder and installed it.

Sharing the screenshot.

Visit this link for the steps I followed

[enter image description here]

It worked magically and I didn't face the above error.

P.S : It didn't ask any password while installing and able to connect with my project.

• JoBaHP: It was asking for password but anyway now works fine.
• Preety Sethi: @JoBaHP Good to hear, it worked !! Congratulations and Thankyou.

Answer 22

I had the same issuse after following this: PostgreSQL setup for Rails development in Ubuntu 12.04

I tried the other answers but all I had to do was in: "config/database.yml"

development:
 adapter: postgresql
 encoding: unicode
 database: (appname)_development
 pool: 5
 username: (username you granted appname database priviledges to)
 password:

Answer 23

I had to reinstall pdAdmin to resolve this issue

brew cask reinstall pgadmin4

Answer 24

For Windows if you dont want to edit pb_gba.conf ie leave the method to MD5(default), create a new user, by running this query in Query tool in PGadmin PGadmin

CREATE USER admin WITH PASSWORD 'secret'

then in cmd

psql "dbname=Main_db host=127.0.0.1 user=admin password=secret port=5432

where dbname is your db in postgresql

enter image description here

Answer 25

I provisioned the username and password via terraform in GCP SQL and the problem was the password was not set properly via terraform so though not a proper fix but just to figure out the exact cause.

I changed the password for the user from GCP console and that worked.

Answer 26

This worked for me : http://tecadmin.net/fatal-ident-authentication-failed-for-user-postgres/#

local all postgres trust
local all myapp_usr trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
#host all all ::1/128 trust

• rkapl: This allows anyone from localhost to login as any user. This behavior is not mentioned anywhere in the link. And yes, the debian info pages contain the same thing.