PostgreSQL - FATAL: Ident authentication failed for user

Question

I've created a simple table in postgres called employees in database mytestdb

I would like to import this table into hdfs.

bin/sqoop import --connect 'jdbc:postgresql://127.0.0.1/mytestdb' --username user -P --table employees --target-dir /user/postgres

But, I keep receiving an error:

WARNING: SQLException occurred while connecting to 127.0.0.1:5432 org.postgresql.util.PSQLException: FATAL: Ident authentication failed for user "user" at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:473)

/var/lib/pgsql/data/pg_hba.conf set up as follows:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident

usr · Accepted Answer · 2018-07-16 09:44:58Z

25

I found a workable solution from a combination of several different sources on the web.

Edit the configuration file

nano /var/lib/pgsql/data/pg_hba.configuration

Replace the first two ident's with md5, like so:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident

Save file.

Then, restart the server

sudo systemctl restart postgresql

Lastly, grant all privileges on database testdb to hduser;

answered Jul 16, 2018 at 9:44

usr

8321 gold badge9 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Giampaolo Ferradini Over a year ago

Ah, thanks. An update for newer users: you might need sudo service postgresql-XX.service restart (where XX is your Postgres release, 11 in my case) instead than sudo systemctl restart postgresql

Amos Korir Over a year ago

In some versions, the file is located at nano /var/lib/pgsql/data/pg_hba.conf

NightOwl19 Over a year ago

I'd been trying editing the .conf file multiple times but was using sudo systemctl restart post that. But, using the service restart command worked for me.

Andomar · Accepted Answer · 2018-04-29 11:40:36Z

2

Check the log file in (for CentOS, probably in /var/lib/pgsql/data/pg_log) for more details.

If the user doesn't exist, create it. With psql, you can create a user like:

create role hduser with login, superuser;

Or from the command line:

createuser -s -e hduser

If identd is not installed, install it:

yum install authd xinetd

Then edit /etc/xinet.d/auth and change disable = yes to disable = no:

service auth 
{ 
        disable = no 
        socket_type = stream 
        ....
}

And restart the xinetd service:

systemctl restart xinetd

edited Apr 29, 2018 at 11:40

answered Apr 29, 2018 at 9:53

Andomar

239k55 gold badges387 silver badges412 bronze badges

4 Comments

Andomar Over a year ago

Is your server running identd on tcp port 113? What do the postgres logs say (on centos, probably /var/lib/pgsql/data/logs) ?

usr Over a year ago

How would I know identd is running? I don't have /var/lib/pgsql/data/logs, but there's /var/lib/pgsql/data/pg_log and then today's log: postgresql-Sun.log in this directory

Andomar Over a year ago

That's the right log, anything useful in it? Check if identd is running with commands like service identd status, ps afx | grep identd, telnet localhost 113, netstat -a

wildplasser Over a year ago

How would I know identd is running? telnet localhost 113 #ftfy

Collectives™ on Stack Overflow

PostgreSQL - FATAL: Ident authentication failed for user

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related