org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host Linux error that works on Windows

708 views

Skip to first unread message

Aaron Sheppard

unread,

Aug 23, 2022, 5:49:54 PM8/23/22

to rundeck-discuss

I have looked at many (too many) similar questions here and tried over and over to solve this one but I can't quite get it to resolve.

I have a java code that connects to a PostGreSQL server on a RHEL 8.2 box to query and return a value. When I run it on my Windows 10 laptop; it works with no issues. However, when I run it locally on the Linux box it fails with the dreaded org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host.

The java code is made by a ETL software called Talend; and the code is run on Linux by RunDeck.

To make matters more confusing, I can login on Linux as the postgres user and use the psql interface to connect to the db with the same host, port, db, and user I'm using in Java without error-

psql -h [server ip] -p 5432 -d my_special_db -U my_db_user

I have checked the server settings, the pg_hba.conf file, the user permissions, the log files and tried changing all of the above to get some sort of return; all to no avail.

I have added to pg_hba.conf:

host my_special_db my_db_user [server ip]/32 md5

host my_special_db my_db_user [server ip with .0 as the last quatraine]/24 md5

host my_special_db root ::1/128 trust

host all all [server ip]/32 trust

I have tried multiple connection modifiers like:

sslfactory=org.postgresql.ssl.NonValidatingFactory&sslmode=prefer

sslmode=prefer

sslmode=Require

no modifier at all

Please advise.

Details:

NAME : Linux RHEL 8.2

VERSION : 4.18.0-372.16.1.el8_6.x86_64

JVM

IMPLEMENTATIONVERSION : 11.0.15+9-LTS

NAME : OpenJDK 64-Bit Server VM

VENDOR : Amazon.com Inc.

VERSION : 11.0.15

PostGreSQL

postgresql11-libs-11.7-1PGDG.rhel8.x86_64.rpm \

postgresql11-11.7-1PGDG.rhel8.x86_64.rpm \

postgresql11-server-11.7-1PGDG.rhel8.x86_64.rpm

Under server > properties > SSL

All Defaults:

SSL Mode: Prefer

Client Cert: [blank]

Client Cert Key: [blank]

Root Cert: [blank]

Cert Rev List: [blank]

SSL compression?: no

//////////////////

Under /etc/systemd/system/postgresql-11.service

[Unit]

Description=PostgreSQL 11 database server

Documentation=https://www.postgresql.org/docs/11/static/

After=syslog.target

After=network.target

[Service]

Type=notify

User=postgres

Group=postgres

# Note: avoid inserting whitespace in these Environment= lines, or you may

# break postgresql-setup.

# Location of database directory

Environment=PGDATA=[my data directory path]

# Where to send early-startup messages from the server (before the logging

# options of postgresql.conf take effect)

# This is normally controlled by the global default set by systemd

# StandardOutput=syslog

# Disable OOM kill on the postmaster

OOMScoreAdjust=-1000

Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj

Environment=PG_OOM_ADJUST_VALUE=0

ExecStartPre=[path to server]/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA}

ExecStart=[path to server]/pgsql-11/bin/postgres -D ${PGDATA}

#ExecReload=/bin/kill -HUP $MAINPID

KillMode=mixed

KillSignal=SIGINT

# Do not set any timeout value, so that systemd will not kill postmaster

# during crash recovery.

TimeoutSec=0

[Install]

WantedBy=multi-user.target

[Service]

Environment=PGDATA=[my data directory path]

//////////////////

Top of the pg_hba.conf file:

# TYPE DATABASE USER ADDRESS METHOD

# linux requires a 'local' entry

#local all all md5

local all all md5

# IPv4 local connections:

host all all 0.0.0.0/0 md5

# IPv6 local connections:

host all all ::0/0 md5

# no ssl connection

hostnossl all all 0.0.0.0/0 trust

# IPv4 local connections:

hostnossl all all 127.0.0.1/32 md5

# IPv6 local connections:

hostnossl all all ::1/128 md5

# Allow replication connections from localhost, by a user with the

# replication privilege.

host replication all 127.0.0.1/32 md5

host replication all ::1/128 md5

# This Servers IP and Ranges as Talend doesn't use localhost

host postgres postgres [server ip]/32 md5

host postgres all [server ip with .0 as the last quatraine]/24 md5

host all postgres [server ip]/32 md5

host all all [server ip with .0 as the last quatraine]/24 md5

host my_special_db my_db_user [server ip]/32 md5

host my_special_db my_db_user [server ip with .0 as the last quatraine]/24 md5

host my_special_db root ::1/128 trust

host all all [server ip]/32 trust

//////////////////

Part of My Java Code:

// Set Reports DB values

context.DB_HOST = [server ip];

context.DB_NAME = my_special_db;

context.DB_PORT = 5432;

//------------

currentComponent="tDBConnection_1";

int tos_count_tDBConnection_1 = 0;

String dbProperties_tDBConnection_1 = "sslfactory=org.postgresql.ssl.NonValidatingFactory&sslmode=prefer";

String url_tDBConnection_1 = "jdbc:postgresql://"+context.DB_HOST+":"+context.DB_PORT+"/"+context.DB_NAME;

if(dbProperties_tDBConnection_1 != null && !"".equals(dbProperties_tDBConnection_1.trim())) {

url_tDBConnection_1 = url_tDBConnection_1 + "?" + dbProperties_tDBConnection_1;

}

String dbUser_tDBConnection_1 = context.DB_USER;

//////////////////

Log entry from the time of trying to connect both locally and remotely-

2022-08-23 15:20:45.452 EDT [67687] LOG: database system is ready to accept connections

2022-08-23 15:22:00.719 EDT [67687] LOG: received fast shutdown request

2022-08-23 15:22:00.720 EDT [67687] LOG: aborting any active transactions

2022-08-23 15:22:00.722 EDT [67687] LOG: background worker "logical replication launcher" (PID 67696) exited with exit code 1

2022-08-23 15:22:00.722 EDT [67691] LOG: shutting down

2022-08-23 15:22:00.754 EDT [67687] LOG: database system is shut down

2022-08-23 15:22:00.814 EDT [67739] LOG: database system was shut down at 2022-08-23 15:22:00 EDT

2022-08-23 15:22:00.818 EDT [67735] LOG: database system is ready to accept connections

2022-08-23 15:52:25.666 EDT [67735] LOG: received fast shutdown request

2022-08-23 15:52:25.668 EDT [67735] LOG: aborting any active transactions

2022-08-23 15:52:25.669 EDT [67735] LOG: background worker "logical replication launcher" (PID 67745) exited with exit code 1

2022-08-23 15:52:25.669 EDT [67740] LOG: shutting down

2022-08-23 15:52:25.692 EDT [67735] LOG: database system is shut down

2022-08-23 15:52:34.188 EDT [79803] LOG: database system was shut down at 2022-08-23 15:52:25 EDT

2022-08-23 15:52:34.194 EDT [79801] LOG: database system is ready to accept connections