Re: Connection error to new pg15 instance

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(dot)baldwin(at)gmail(dot)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
>