AppInt PostgreSQL Connectors - Connection was forcibly closed

[mattferrari]

Hi Community!

I'm trying to setup a connector for Application Integration to fetch data from a PostgreSQL database on an AWS RDS. Using DBeaver I can connect without any problems (no SSL, no firewalls to be opened, ecc.), but I cannot create the connector to use in Application Integration. I'm trying to use the standard PostgreSQL connector as I see that at the moment there isn't any reference to a connector for AWS RDS instances. I attach the error on the logs. Did anyone ever face something similar? How did you resolve the issue?

I also attach the roles of the service account that we are using for the connector.

[DMulchandani]

Hi @mattferrari 

We are seeing Socket Error (700 - System error: Connection reset), typically this error indicates that the connection is getting closed on the server side. Does your PostgreSQL instance have any kind of IP whitelisting.

[mattferrari]

Thank you @DMulchandani ,

No, in this test instance there are no restrictions at the moment.

[DMulchandani]

Hi @mattferrari 

Is it possible to provide a Wireshark trace or TCP dump so we can take a look at the network traffic. As mentioned, the connection seems to be getting closed by the server without any obvious reason, and typically these errors indicate an underlying networking issue, but if there are no firewalls/proxies/IP restrictions, we can look at the trace file to see if there is any additional information in the packets from the server to indicate what is happening here.

Thanks,

Deepa

[athiranair]

Hi @mattferrari,
We attempted to reproduce the problem and managed to establish a connection. These were the procedures we executed. Perhaps you could try replicating it once and inform us if it works for you.

Steps:
At Instance Level
1. Create HA VPN between GCP and AWS VPC networks 
Reference link - https://medium.com/@nanditasahu031/ha-vpn-connections-between-gcp-and-aws-1e1ce439e055  
https://cloud.google.com/network-connectivity/docs/vpn/tutorials/create-ha-vpn-connections-google-cl...  

2. Configure and create AWS PostgreSQL Instance and with RDS instance security group allowing traffic from GCP VPC 
 
3. Create a custom parameter group for RDS and change the "rds.force_ssl" parameter to '0' value. Attach this parameter group to RDS instance created. Reboot the RDS instance. 
Reference link - https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithDBInstanceParamGroups.html  

Note - This parameter change is required to allow connections that aren't using SSL. When the rds.force_ssl is active on RDS instance the connection may give the following error msg -" no pg_hba.conf entry for host "w.x.y.z", user "testuser", database "testDB", SSL off ". Therefore update the rds.force_ssl parameter to 0.

4. Create Network Endpoint Group in GCP using RDS's IP  
AWS provides an RDS endpoint which is static but its IP is dynamic. We need to use RDS   IP for this setup.  
To get RDS IP refer this document - https://repost.aws/knowledge-center/rds-ip-address-issues  

5. Create internal proxy Network LB with backend as Network endpoint group created in Step 4. 
 
6. Publish the Load Balancer in Private service connect. 
Create an endpoint attachment IP using this PSC in the environment where your user wants to create a connection. 
 
DB Level: 
7. Once the instance is created, we will have to connect to a DB instance using any open-source tool which can be done using the steps mentioned in the document: 
Reference link: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ConnectToPostgreSQLInstance.html  

At Connection level: 
8. Go to the connection page, Click on Create new Connection 
9. In the Location section, choose the preferred location for the connection 
10. In the Connection Details section, choose the Connector i.e., postgresql, Connector version, Connection Name, Service Account, configure the Connection node, Database Name. 
11. In the Authentication section, choose Host Address. Enter the Host URL and port details in the Connection.  

Note: If the Security Group is adjusted to permit listing any request originating from any IP address, specifically 0.0.0.0, then it becomes reachable through the hostname at the connection level. However, if the RDS is restricted, meaning it is set to private and not exposed to the public, then a PSC is required .fir The above steps can be taken to set up the PSC in this scenario.

Regards,
Athira