1

I upgraded postgresql from 8.4.2 to 12.3. I created a dump of the db from the old version, and restored into the new version. The port for the new version is 5532. The old one uses 5432. After I restore the dump, I check that the service is running for the new version, and the server is listening on port 5532 for all interfaces. I even copy the pg_hba.conf entries from the old version to the new version. But instead of "password" I change all the entries to "md5" because this is a newer version. After changes, I reload pg_hba.conf. However, when we test access to the new db now, it does not work. It fails with error :

Your login attempt was not successful, try again. Reason : Could not get JDBC Connection; nested exception is org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "192.168.130.209", user "app_support", database "bosupportdb"

Postgres ports :

[root@reportv2 data]# ss -tulpn | grep postgres
tcp    LISTEN     0      128       *:5432                  *:*                   users:(("postgres",pid=3454,fd=3))
tcp    LISTEN     0      128       *:5532                  *:*                   users:(("postgres",pid=4763,fd=3))
tcp    LISTEN     0      128      :::5432                 :::*                   users:(("postgres",pid=3454,fd=4))
tcp    LISTEN     0      128      :::5532                 :::*                   users:(("postgres",pid=4763,fd=4))
[root@reportv2 data]#

These are my pg_hba.conf entries :

# TYPE  DATABASE    USER        CIDR-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
host    all             all             192.168.130.0/24        md5
#host   all             all             192.168.8.0/24          md5
host    all             all             192.168.90.8/32         md5
host    all             all             192.168.8.175/32        md5
host    all             all             192.168.112.0/23        md5

Please help me resolve this. I am clueless.

2
  • 1
    Maybe you copied the pg_hba.conf file to the wrong location. If you can log in as the superuser, you can use show hba_file; to verify which files is used
    – user1822
    Aug 27, 2020 at 10:45
  • It's showing the correct path : postgres=# show hba_file; hba_file -------------------------------------- /usr/local/pgsql123/data/pg_hba.conf (1 row) postgres=#
    – anaigini
    Aug 27, 2020 at 11:14

1 Answer 1

1

Make sure that the HBA rules that PostgreSQL is actually using are the ones you expect:

select * 
from pg_hba_file_rules 
where type = 'host' 
order by line_number ;

Check the error column for anything amiss with the file syntax.

4
  • The error column is empty.for all.
    – anaigini
    Aug 27, 2020 at 12:59
  • So the file is valid. That's good. Do the rules listed /match/ those in your pg_hba.conf? Is PostgreSQL looking in the right place (what's the "-D" argument on the postmaster process). Enable "connection" logging in postgresql.conf and try again. You may get more information in the postgres error log. Add a rule specifically for this machine ("host all all 192.168.130.209/32 md5"). See if that works. As a /really/ long shot, change the authentication on that rule to use "trust". If that works, then it may be a problem with the password (but I'd be surprised).
    – Phill W.
    Aug 27, 2020 at 13:11
  • Yes, the rules match with pg_hba.conf. The -D arg is the data dir which is /usr/local/pgsql123/data. I saw in the old version's postgresql.conf that the max_connections is 200. In the new version it was 100. Could this be the reason?
    – anaigini
    Aug 27, 2020 at 14:41
  • I just checked, and log connections is not on in the new version.
    – anaigini
    Aug 27, 2020 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.