Troubleshooting AppFlow Connection to Aurora PostgreSQL

I understand your frustration with connecting AppFlow to your Aurora PostgreSQL Serverless v1 instance. Let's go through some potential issues and solutions:

1. VPC Configuration: While you've mentioned that your VPC has internet access through an Internet Gateway, it's important to ensure that the specific subnets you're using for your Aurora instance are properly configured. For AppFlow to connect, it typically requires the database to be in private subnets with proper routing through a NAT Gateway, not just an Internet Gateway.

2. Security Group: Although you've allowed AppFlow IPs on port 5432, make sure that both inbound and outbound rules are correctly set. Sometimes, overlooking outbound rules can cause connection issues.

3. Database Accessibility: Verify that your Aurora PostgreSQL instance is not set to be publicly accessible. AppFlow typically connects to databases within the VPC, so having the database set to public access might cause conflicts.

4. Port Availability: Confirm that port 5432 is not being used by any other service or local PostgreSQL instance on your system.

5. Database Name: When setting up the connection in AppFlow, make sure you're using the correct database name. Sometimes, using the default "postgres" database name can help if you're unsure.

6. IAM Configuration: If you're using IAM authentication for your database, ensure that it's properly configured and that the IAM role associated with AppFlow has the necessary permissions to access the database.

7. Encryption: If you're using encryption for your Aurora instance, make sure that AppFlow is configured to use the correct encryption settings.

8. Network ACLs: Check your VPC's Network ACLs to ensure they're not blocking the necessary traffic.

If you've verified all these points and are still encountering issues, it might be worth reaching out to AWS Support for further assistance. They can provide more detailed troubleshooting based on your specific account and configuration.

Remember, AppFlow supports Amazon RDS for PostgreSQL as a destination for data transfer, not as a source. Make sure you're setting it up correctly in this context.
Sources
Connecting to an Amazon Aurora DB cluster - Amazon Aurora
When the service fails to connect to Amazon RDS or downstream service - AWS App Runner
Troubleshooting connections to your RDS for PostgreSQL instance - Amazon Relational Database Service
Amazon RDS for PostgreSQL connector for Amazon AppFlow - Amazon AppFlow
Unable to connect Amazon RDS for PostgreSQL to Appflow | AWS re:Post

Just adding more context for assistance: I created a new database, a public-facing Serverless V2 instance, and when I open port 5432 to the entire internet (0.0.0.0/32), I am able to connect. I am in the same VPC and using the same Subnet Group. I would like to know which IP AppFlow uses to connect to the database so that I can allow it in the Security Group. The list from ip-ranges doesn’t seem to be working.

Public subnet with internet gateway will not work, you need a public IP.

"You can't give an Aurora Serverless v1 DB cluster a public IP address. You can access an Aurora Serverless v1 DB cluster only from within a VPC."

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-serverless.html#:~:text=You%20can%27t%20give%20an%20Aurora%20Serverless%20v1%20DB%20cluster%20a%20public%20IP%20address.%20You%20can%20access%20an%20Aurora%20Serverless%20v1%20DB%20cluster%20only%20from%20within%20a%20VPC.

In cloudtrail if you look at the request, it is trying to go over the public network:

"requestParameters": { "connectorType": "CustomConnector", "connectorProfileConfig": { "connectorProfileProperties": { "CustomConnector": { "profileProperties": {} } }, "connectorProfileCredentials": "***" }, "connectionMode": "Public", "connectorProfileName": "Postgres", "connectorLabel": "AmazonRDSPostgreSQL" }

I tried creating private customConnector in private mode and failed

An error occurred (ValidationException) when calling the CreateConnectorProfile operation: Private mode is not supported for ConnectorType: CustomConnector, Region: us-east-1

Assume you need public access for your RDS Postgres instance—which isn’t available with Serverless v1. Upgrade to Serverless v2 and configure the endpoint as public, or use an EC2 proxy as a workaround.

--connection-mode (string)

Indicates the connection mode and specifies whether it is public or private. Private flows use Amazon Web Services PrivateLink to route data over Amazon Web Services infrastructure without exposing it to the public internet.

Possible values: Public Private

https://docs.aws.amazon.com/cli/latest/reference/appflow/create-connector-profile.html#:~:text=CUSTOMCONNECTOR%20connector%20type/.-,%2D%2Dconnection%2Dmode%20(string),Private,-%2D%2Dconnector%2Dprofile%2Dconfig