Cannot connect to local postgresql DB using DBeaver

Question

I'm trying to connect to a postgresql database which is in localhost:5432 but I keep getting the error: FATAL: Ident authentication failed for user "".

I installed Postgres11 on virtual machine running Centos7. Created a database through command line, with the name business_db.

I've checked and postgresql is running in localhost:5432.

My pg_hba.conf file is like this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
host    all             all             127.0.0.1/32           ident
host    all             all             ::1/128                 ident
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident

The pg_ident.conf file doesn't hold any configurations:

# Put your actual configuration here
# ----------------------------------

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME

The database exists as shown by the command:

I'm logged into the system as "dev" user but., whatever I try while testing connection whit DBeaver, I allways get the error:

I also tried to set User as postgres and use my system password but get the same error. What am I missing?

Nitrate

Still true for DBeaver. I tried to upgrade driver version, but could not find clear guidelines how to do this. – Upsydaisy

Answer 1

When you use JDBC, you have to use password authentication. Neither ident nor peer will work for that.

You will need to add, e.g.:

host    all             all             127.0.0.1/32           md5

at the top of your pb_hba.conf

(replace md5 with scram-sha-256 if you are using that)

Toby

Preliminary to this record in pg_hba.conf some configuration steps related to database itself (password encryption method) should be done as described in article https://mcmap.net/q/264604/-unable-to-connect-to-postgres-db-due-to-the-authentication-type-10-is-not-supported – Upsydaisy

Why should ident not work with JDBC? I admit it is a bad choice. – Jarad

Answer 2

In (Ubuntu OS), change (or set) password in this way to solve this problem:

1- In terminal, run this command:

sudo -u postgres psql template1

2- Then run this command:

ALTER USER postgres with encrypted password 'your_password';

Note: if command sudo -u postgres psql template1 require password for postgres, here you need to go to pg_hba.conf file and make sure this line end with peer:

local   all             all                                     peer 

if peer is md5 or other you will asked to enter password of postgres even if you did not set it.

Langobard

Answer 3

Change the ident to trust if you don't want to enter a password or to scram-sha-256 if you want to enter a password.

Then reload the PostgreSQL server.

Jarad

Answer 4

remove any spaces from your password, this worked for me

Il

Answer 5

1.Find the port running postgres using:

sudo netstat -plunt |grep postgres

Note that postgres may NOT always run at default port 5432. It may change to other port than default port 5432. In new version of postgres, it changes to 5434.

2.login to postgres with default user and then change password as desired:

sudo -u postgres psql
\password

3.Enter above set up username, password and port to DBeaver. It will run as normal.

Ariana