Re: Connection error to new pg15 instance

It turns out RDS by default forces ssl connections for pg15 instances. I
have turned this off for now while I work out how that impacts my code and
I can now connect to the pg15 instance.

Thanks,

Steve

On Sun, Jun 4, 2023 at 3:10 PM Steve Baldwin <steve(dot)baldwin(at)gmail(dot)com>
wrote:

> 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
>>
>