Обсуждение: Connection fails on one system in a address range allowed to connect

I am migrating an existing web application from Oracle to postgres and I’m testing the connectivity.

Trying to run a test program (that works on another system in the same subnet!) I get this error:

Error system:

[root@dhbroomscheduling4 ~]# ./pg_test.pl 
DBI connect('dbname=webdata;host=dhbpostgres.pharmacy.arizona.edu;port=5432','trav',...) failed: FATAL:  password
authenticationfailed for user "trav"
 
FATAL:  no pg_hba.conf entry for host "150.135.124.50", user "trav", database "webdata", no encryption at ./pg_test.pl
line8.
 

Working system:

[root@avipg perl]# ./pg_test.pl
Sector Alpha Crucis has 44 worlds  
Sector Antares has 37 worlds  
Sector Core has 221 worlds …

(The test dataset is a collection of mapping data for an old RPG game) 

Note the pg_test.pl script was copied from the working server to the non working one.

The pg_hba.conf on the server includes this which should encompass all systems in this VLAN

# external 'OldMTM' site range
hostssl all all 150.135.124.0/25 password

Another system in the same address range is working just fine with the identical setup, in fact it’s in production
withoutissues. 
 

Both systems are running Rocky Linux 9, using the perl DBI interface with DBD::Pg all installed from the Rocky Linux
repositories.

Firewall settings, Perl version, env variables etc are the same on both client hosts

I know the password is correct because I just successfully logged in on the server with psql -U trav -d webdata -W  and
usedthe password in the connect statement in the script.
 

Anywhere else that I should look for a cause?


-- 
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

On Tue, 2023-11-21 at 23:27 +0000, Johnson, Bruce E - (bjohnson) wrote:
> DBI connect('dbname=webdata;host=dhbpostgres.pharmacy.arizona.edu;port=5432','trav',...) failed: FATAL:  password
authenticationfailed for user "trav" 
> FATAL:  no pg_hba.conf entry for host "150.135.124.50", user "trav", database "webdata", no encryption at
./pg_test.plline 8. 
>
> The pg_hba.conf on the server includes this which should encompass all systems in this VLAN
>
> # external 'OldMTM' site range
> hostssl all all 150.135.124.0/25 password
>
> Anywhere else that I should look for a cause?

"no encryption" does not match a "hostssl" entry.

Either add "sslmode=require" to the connection string, or use a "hostnossl" entry.

Yours,
Laurenz Albe

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Tue, 2023-11-21 at 23:27 +0000, Johnson, Bruce E - (bjohnson) wrote:
>> DBI connect('dbname=webdata;host=dhbpostgres.pharmacy.arizona.edu;port=5432','trav',...) failed: FATAL:  password
authenticationfailed for user "trav" 
>> FATAL:  no pg_hba.conf entry for host "150.135.124.50", user "trav", database "webdata", no encryption at
./pg_test.plline 8. 
>>
>> The pg_hba.conf on the server includes this which should encompass all systems in this VLAN
>> # external 'OldMTM' site range
>> hostssl all all 150.135.124.0/25 password

> "no encryption" does not match a "hostssl" entry.

Yeah.  What is probably happening here is that (with the default sslmode)
libpq is trying an SSL connection, that's failing for some reason, and
then it tries a non-SSL connection which definitely fails for lack of
a matching pg_hba.conf entry; and then for some other reason you are
only shown the message concerning the last attempt.

Theory B is that your libpq wasn't compiled with SSL support so it
skips right to the non-SSL attempt.

Laurenz's suggestion of adding sslmode=require is a good debugging
step either way, since it will either tell you for sure that you
are missing SSL support or show you the failure from the single
SSL-enabled attempt.  Alternatively, turn on log_connections and
see what the server log captures.  (You might need to do that
anyway if the client-side message isn't sufficiently informative.)

            regards, tom lane