Accessing Redshift cluster using Public and Private IPs

When you have a publicly accessible Amazon Redshift cluster in a VPC, DNS resolution can cause connectivity issues for internal VPC resources like QuickSight and site-to-site VPN connections. This happens because the hostname resolves to the public IP address instead of the private IP address when accessed from within the VPC.

To resolve this issue, you need to set two important VPC parameters to true:

1. DNS resolution
2. DNS hostnames

Setting these parameters to true will ensure that connections made from within the VPC (including through your site-to-site VPN and QuickSight) will resolve to the private IP address of your Redshift cluster instead of the elastic IP address (public IP).

This is the recommended approach when you need both public access for external BI tools and private access for resources within your VPC or connected via VPN. Without these settings enabled, connections from within the VPC will continue to resolve to the public IP address, which can cause routing issues with your VPN configuration.

Alternatively, you could consider using a Redshift-managed VPC endpoint (powered by AWS PrivateLink) to create a private connection between your VPCs. This would allow you to access your data warehouse without using public IP addresses or routing traffic through the internet, which might be a more secure approach depending on your requirements.
Sources
Redshift resources in a VPC - Amazon Redshift
Site-to-Site VPN | AWS re:Post
Redshift-managed VPC endpoints - Amazon Redshift

By default, a private hosted zone in Route 53 is only accessible within the associated VPC. If you need to resolve the Redshift cluster's private IP address from outside the VPC—such as from an on-premises environment over a site-to-site VPN—you'll need to configure Route 53 inbound resolver endpoints. Amazon provides detailed guidance on this here: How to use Route 53 resolver with inbound endpoints.

To make this work:

1. Create a Route 53 inbound resolver endpoint in your VPC.
2. Update your on-premises DNS configuration to forward DNS queries for the Redshift domain (or the domain used in your private hosted zone) to the IP address of this inbound resolver endpoint.

This allows your on-premises DNS infrastructure to resolve internal AWS private hosted zone, via Route 53.

Alternative Approach:

If you prefer not to use Route 53 resolver endpoints, you can:

• Manually configure a DNS entry in your on-premises DNS server mapping the Redshift hostname to its private IP.
• Or, update the local hosts file on each system that needs access.

To avoid conflicts with the default public DNS resolution, consider using a custom domain name for the private IP record (e.g., redshift-private.example.com), which won't overlap with the public Redshift hostname.

This approach allows you to distinguish between public and private DNS resolution, enabling both QuickSight (within VPC) and on-premise clients to access the cluster reliably via private IP.