Customer unable to connect on port 5432, Postgres 10.7

On 1/21/21 7:30 PM, Keith Christian wrote:

Hello again Postgres friends,

Customer is unable to log in to a PG database on 5432 using a Windows client.
Output of my test sessions below, I think the problem may be lines 3 -
7 but no idea.
Ideas greatly appreciated!

Lines 1,2 PG Log entries
Lines 3-7 pg_hba.conf entries
Lines 8-10 postgresql.conf entries
Lines 11-12 Logging into the postgresql machine from a Linux machine
Lines 13-15 Successfully logged in, testing that SSH to the
linuxserver is successful
Lines 16-20 pguser successfully executes a query

1. LOG: connection received: host=aaa.bbb.ccc.ddd port=52310

Is "52310" a typo?

2. 2021-01-21 15:34:00.657 MST [12011] FATAL: no pg_hba.conf
entry for host "aaa.bbb.ccc.ddd", user "pguser", database "testpgdb",
SSL off

3. # IPv4 local connections:
4. # host all all 127.0.0.1/32 ident
5. host pguser all eee.fff.ggg.1/24 password
6. host pguser all hhh.jjj.kkk.1/24 password
7. host pguser all 0.0.0.0/0 password

8. #listen_addresses = 'localhost'
9. listen_addresses = '*'
10. #port = 5432

11. pguser@linuxserver:~$ ssh -q pguser@www.xxx.yyy.zzz
12. pguser@www.xxx.yyy.zzz's password:

13. [pguser@pgserver ~]$ psql -U pguser -W testpgdb
14. pguser=> \conninfo
15. You are connected to database "testpgdb" as user "pguser" via
socket in "/var/run/postgresql" at port "5432".

What if you add aaa.bbb.ccc.ddd/32 to pg_hba.conf?

--
Angular momentum makes the world go 'round.

On 1/21/21 5:30 PM, Keith Christian wrote:

Hello again Postgres friends,

Customer is unable to log in to a PG database on 5432 using a Windows client.
Output of my test sessions below, I think the problem may be lines 3 -
7 but no idea.
Ideas greatly appreciated!

Lines 1,2 PG Log entries
Lines 3-7 pg_hba.conf entries
Lines 8-10 postgresql.conf entries
Lines 11-12 Logging into the postgresql machine from a Linux machine
Lines 13-15 Successfully logged in, testing that SSH to the
linuxserver is successful
Lines 16-20 pguser successfully executes a query

1. LOG: connection received: host=aaa.bbb.ccc.ddd port=52310
2. 2021-01-21 15:34:00.657 MST [12011] FATAL: no pg_hba.conf
entry for host "aaa.bbb.ccc.ddd", user "pguser", database "testpgdb",
SSL off

3. # IPv4 local connections:
4. # host all all 127.0.0.1/32 ident
5. host pguser all eee.fff.ggg.1/24 password
6. host pguser all hhh.jjj.kkk.1/24 password
7. host pguser all 0.0.0.0/0 password

Looks like you have the database and user columns switched. Should be
something like:

host all pguser ...

Follow format as shown here:

https://www.postgresql.org/docs/12/auth-pg-hba-conf.html

8. #listen_addresses = 'localhost'
9. listen_addresses = '*'
10. #port = 5432

11. pguser@linuxserver:~$ ssh -q pguser@www.xxx.yyy.zzz
12. pguser@www.xxx.yyy.zzz's password:

13. [pguser@pgserver ~]$ psql -U pguser -W testpgdb
14. pguser=> \conninfo
15. You are connected to database "testpgdb" as user "pguser" via
socket in "/var/run/postgresql" at port "5432".

16. pguser=> select 5.0/99;
17. ?column?
18. ------------------------
19. 0.05050505050505050505
20. (1 row)

--
Adrian Klaver
adrian.klaver@aklaver.com

Failed to copy pgsql-general, post repeated below:

Adrian,

I used your suggestions, fixed the column order, and it worked the
first time for the customer this morning:

host testpgdb pguser eee.fff.ggg.1/24 password

THANKS!