Connection error to new pg15 instance

Hi all,

I'm in the process of migrating from an RDS pg14 instance to pg15.3. As
part of the migration process, the application code makes a test connection
to the new instance. This failed. I tried manually connecting to the kube
pod where the test query was submitted from, and from there was able to
manually connect to the new instance (using psql) just fine.

Here are the (hopefully) relevant chunks from the database log:

:
2023-06-04 00:29:11.890 UTC,,,2764,"10.120.80.80:46914",647bdad7.acc,1,"",2023-06-04
00:29:11 UTC,,0,LOG,00000,"connection received: host=10.120.80.80
port=46914",,,,,,,,,"","not initialized",,0
2023-06-04 00:29:11.891 UTC,"b2bc_owner","b2bcreditonline",2764,"
10.120.80.80:46914",647bdad7.acc,2,"authentication",2023-06-04 00:29:11
UTC,7/1009,0,FATAL,28000,"no pg_hba.conf entry for host ""10.120.80.80"",
user ""b2bc_owner"", database ""b2bcreditonline"", no
encryption",,,,,,,,,"","client backend",,0
:
2023-06-04 00:43:56.114 UTC,,,4046,"10.120.80.80:56356",647bde4c.fce,1,"",2023-06-04
00:43:56 UTC,,0,LOG,00000,"connection received: host=10.120.80.80
port=56356",,,,,,,,,"","not initialized",,0
2023-06-04 00:43:56.127 UTC,"b2bc_owner","b2bcreditonline",4046,"
10.120.80.80:56356",647bde4c.fce,2,"authentication",2023-06-04 00:43:56
UTC,7/1626,0,LOG,00000,"connection authenticated: identity=""b2bc_owner""
method=md5 (/rdsdbdata/config/pg_hba.conf:15)",,,,,,,,,"","client
backend",,0
2023-06-04 00:43:56.127 UTC,"b2bc_owner","b2bcreditonline",4046,"
10.120.80.80:56356",647bde4c.fce,3,"authentication",2023-06-04 00:43:56
UTC,7/1626,0,LOG,00000,"connection authorized: user=b2bc_owner
database=b2bcreditonline application_name=psql SSL enabled
(protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384,
bits=256)",,,,,,,,,"","client backend",,0
2023-06-04 00:43:58.814 UTC,"b2bc_owner","b2bcreditonline",4046,"
10.120.80.80:56356",647bde4c.fce,4,"idle",2023-06-04 00:43:56
UTC,7/1627,0,LOG,00000,"statement: select 0 as
dummy;",,,,,,,,,"psql","client backend",,0
2023-06-04 00:43:58.814 UTC,"b2bc_owner","b2bcreditonline",4046,"
10.120.80.80:56356",647bde4c.fce,5,"SELECT",2023-06-04 00:43:56
UTC,7/0,0,LOG,00000,"duration: 0.341 ms",,,,,,,,,"psql","client
backend",,1147616880456321454
2023-06-04 00:44:04.402 UTC,"b2bc_owner","b2bcreditonline",4046,"
10.120.80.80:56356",647bde4c.fce,6,"idle",2023-06-04 00:43:56
UTC,,0,LOG,00000,"disconnection: session time: 0:00:08.287 user=b2bc_owner
database=b2bcreditonline host=10.120.80.80
port=56356",,,,,,,,,"psql","client backend",,0
:

In case it's relevant, the application code is running on nodejs v18.14.2
using pg 8.11.0.

Any thoughts?

Thanks,

Steve

I suspect it may have something to do with ssl. The FATAL error in the log
said "no encryption". I'm not sure what that means. When I look at a
connection to one of our pg14 instances from the same API server, I see
this in the logs:

2023-06-04 00:03:06.210 UTC,"b2bc_api","b2bcreditonline",16024,"
10.120.141.112:49228",647bd4ba.3e98,2,"authentication",2023-06-04
00:03:06 UTC,25/2682741,0,LOG,00000,"connection authenticated:
identity=""b2bc_api"" method=md5
(/rdsdbdata/config/pg_hba.conf:13)",,,,,,,,,"","client backend",,0

Here are the hba rules for that instance:

b2bcreditonline=> select * from pg_hba_file_rules;
line_number | type | database | user_name | address | netmask
| auth_method | options | error

-------------+-------+-------------------+------------+----------+---------+---------------+---------+-------
4 | local | {all} | {all} | |
| scram-sha-256 | |
10 | host | {all} | {rdsadmin} | samehost |
| scram-sha-256 | |
11 | host | {all} | {rdsadmin} | all |
| reject | |
12 | host | {rdsadmin} | {all} | all |
| reject | |
13 | host | {all} | {all} | all |
| md5 | |
14 | host | {replication} | {all} | samehost |
| scram-sha-256 | |
17 | host | {rds_replication} | {all} | all |
| md5 | |
(7 rows)

So line 13 is type 'host'.

When I look at the hba rules for the pg15 instance, there is no
corresponding entry:

b2bcreditonline=> select * from pg_hba_file_rules;
line_number | type | database | user_name | address |
netmask | auth_method | options | error

-------------+---------+-------------------+------------+----------+---------+---------------+-----------+-------
2 | local | {all} | {rdsadmin} | |
| peer | {map=rds} |
6 | local | {all} | {all} | |
| scram-sha-256 | |
12 | host | {all} | {rdsadmin} | samehost |
| scram-sha-256 | |
13 | host | {all} | {rdsadmin} | all |
| reject | |
14 | host | {rdsadmin} | {all} | all |
| reject | |
15 | hostssl | {all} | {all} | all |
| md5 | |
16 | host | {replication} | {all} | samehost |
| scram-sha-256 | |
21 | hostssl | {rds_replication} | {all} | all |
| md5 | |
(8 rows)

The entry that was used when I made a psql connection was line 15 which
has a type of 'hostssl'.

I'm not sure what this means in terms of what I need to change. Maybe I
need to raise this with AWS support?

Cheers,

Steve

On Sun, Jun 4, 2023 at 11:11 AM Steve Baldwin <steve.baldwin@gmail.com>
wrote:

Hi all,

I'm in the process of migrating from an RDS pg14 instance to pg15.3. As
part of the migration process, the application code makes a test connection
to the new instance. This failed. I tried manually connecting to the kube
pod where the test query was submitted from, and from there was able to
manually connect to the new instance (using psql) just fine.

Here are the (hopefully) relevant chunks from the database log:

:
2023-06-04 00:29:11.890 UTC,,,2764,"10.120.80.80:46914",647bdad7.acc,1,"",2023-06-04
00:29:11 UTC,,0,LOG,00000,"connection received: host=10.120.80.80
port=46914",,,,,,,,,"","not initialized",,0
2023-06-04 00:29:11.891 UTC,"b2bc_owner","b2bcreditonline",2764,"
10.120.80.80:46914",647bdad7.acc,2,"authentication",2023-06-04 00:29:11
UTC,7/1009,0,FATAL,28000,"no pg_hba.conf entry for host ""10.120.80.80"",
user ""b2bc_owner"", database ""b2bcreditonline"", no
encryption",,,,,,,,,"","client backend",,0
:
2023-06-04 00:43:56.114 UTC,,,4046,"10.120.80.80:56356",647bde4c.fce,1,"",2023-06-04
00:43:56 UTC,,0,LOG,00000,"connection received: host=10.120.80.80
port=56356",,,,,,,,,"","not initialized",,0
2023-06-04 00:43:56.127 UTC,"b2bc_owner","b2bcreditonline",4046,"
10.120.80.80:56356",647bde4c.fce,2,"authentication",2023-06-04 00:43:56
UTC,7/1626,0,LOG,00000,"connection authenticated: identity=""b2bc_owner""
method=md5 (/rdsdbdata/config/pg_hba.conf:15)",,,,,,,,,"","client
backend",,0
2023-06-04 00:43:56.127 UTC,"b2bc_owner","b2bcreditonline",4046,"
10.120.80.80:56356",647bde4c.fce,3,"authentication",2023-06-04 00:43:56
UTC,7/1626,0,LOG,00000,"connection authorized: user=b2bc_owner
database=b2bcreditonline application_name=psql SSL enabled
(protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384,
bits=256)",,,,,,,,,"","client backend",,0
2023-06-04 00:43:58.814 UTC,"b2bc_owner","b2bcreditonline",4046,"
10.120.80.80:56356",647bde4c.fce,4,"idle",2023-06-04 00:43:56
UTC,7/1627,0,LOG,00000,"statement: select 0 as
dummy;",,,,,,,,,"psql","client backend",,0
2023-06-04 00:43:58.814 UTC,"b2bc_owner","b2bcreditonline",4046,"
10.120.80.80:56356",647bde4c.fce,5,"SELECT",2023-06-04 00:43:56
UTC,7/0,0,LOG,00000,"duration: 0.341 ms",,,,,,,,,"psql","client
backend",,1147616880456321454
2023-06-04 00:44:04.402 UTC,"b2bc_owner","b2bcreditonline",4046,"
10.120.80.80:56356",647bde4c.fce,6,"idle",2023-06-04 00:43:56
UTC,,0,LOG,00000,"disconnection: session time: 0:00:08.287 user=b2bc_owner
database=b2bcreditonline host=10.120.80.80
port=56356",,,,,,,,,"psql","client backend",,0
:

In case it's relevant, the application code is running on nodejs v18.14.2
using pg 8.11.0.

Any thoughts?

Thanks,

Steve