Home > mailing lists

Re: Connection error to new pg15 instance - Mailing list pgsql-general

From	Steve Baldwin
Subject	Re: Connection error to new pg15 instance
Date	June 4, 2023 08:10:11
Msg-id	CAKE1AiadYXQfjuDUODvB9Bh2y5_chjQADKzwRVB54AfEOadmTg@mail.gmail.com Whole thread Raw
In response to	Connection error to new pg15 instance (Steve Baldwin <steve.baldwin@gmail.com>)
Responses	Re: Connection error to new pg15 instance
List	pgsql-general

Tree view

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

pgsql-general by date:

From: Kirk Wolak
Date: 04 June 2023, 06:40:56
Subject: Re: Interconnected views

From: Andrus
Date: 04 June 2023, 10:00:46
Subject: Re: How to remove user specific grant and revoke

Re: Connection error to new pg15 instance - Mailing list pgsql-general

Previous

Next