RDS can't resolve Route53 private address for foreign server connection

Question

I have a VPC with a Postgresql 17 RDS instance that has a foreign data wrapper to another Postgresql database. The external DB allowlists connections to a proxy on a public IP. The proxy and RDS instances are in the same VPC. The external DB is outside the VPC. My RDS therefore uses the proxy's private address as the address for the foreign server.

This setup works if I use IP addresses. I would prefer to use stable private hostnames (e.g., `db-main.mynetwork.internal` and `db-proxy.mynetwork.internal`). For this, I'm using Route 53 private hosted zones. The hosted zone records look like this: 
![Route 53 records](/media/postImages/original/IMdFHMeEs3Rz2p52RP4WgmFA)

I can confirm that the addresses for both instances resolve correctly using, e.g., hostname from a terminal on the proxy. However, the foreign data wrapper does not appear to cope with these addresses.

When trying to import foreign tables, I get the error

```
ERROR:  could not connect to server "source_server"
DETAIL:  could not translate host name "proxy.mynetwork.internal" to address: System error
```
I'm stumped why this is. It only seems to be my RDS that struggles to resolve the hostnames. Is there a way to get it to resolve them correctly?

re:Post Agent · Answer

The issue you're experiencing is likely due to how DNS resolution works within RDS instances. RDS instances typically use the Amazon-provided DNS server, which may not be configured to resolve custom private DNS names by default.

To resolve this issue, you have a few potential options:

1. Use IP addresses instead of hostnames for your foreign server connections. While this is not your preferred solution, it is the most straightforward way to ensure connectivity.

2. Set up a custom DNS server within your VPC that can handle the resolution for your private domains (like mynetwork.internal). You would then need to configure your RDS instance to use this custom DNS server. However, this may require additional setup and maintenance.

3. Use AWS Route 53 Resolver. This service allows you to create inbound and outbound endpoints for DNS resolution. You can set up forwarding rules for your custom domain (mynetwork.internal) to resolve through Route 53 Resolver.

4. If possible, consider using RDS Proxy. RDS Proxy's DNS name always resolves to private IPs within the VPC, which could simplify your setup.

5. Ensure that both the `enableDnsHostnames` and `enableDnsSupport` attributes are set to true for your VPC. This is required for private hosted zones to work correctly.

6. Double-check that your Route 53 private hosted zone is correctly associated with the VPC where your RDS instance resides.

If none of these solutions work, you may need to contact AWS support for further assistance, as there could be specific configurations or limitations with your RDS setup that are preventing proper DNS resolution of your private hostnames.  
**Sources**  
[Understanding Amazon DNS - Amazon Virtual Private Cloud](https://docs.aws.amazon.com/vpc/latest/userguide/AmazonDNS-concepts.html)  
[Route53 Private | AWS re:Post](https://repost.aws/questions/QUCI_Y_6HmQRKftaPHH1YBFQ/route53-private)  
[How to get PostgreSQL to use internal DNS resolution | AWS re:Post](https://repost.aws/questions/QUVHLmwBc-SvyzDFr2jkVMVg/how-to-get-postgresql-to-use-internal-dns-resolution)

Gary Mclean · Answer

2 Things come to mind..

1. Is your PHZ Associated to your VPC?
2. This is a hunch, but .INTERNAL is specifically set aside for private use within an organization's network and cannot be accessed publicly on the internet; this designation was officially made by ICANN (Internet Corporation for Assigned Names and Numbers) to prevent conflicts with publicly registered domains.

Im wondering if that DOMAIN is reserved internally with AWS already and this is why you may see issues?

Have you tried a different PHZ?

RDS can't resolve Route53 private address for foreign server connection

Add your answer

Relevant content