Securely connect to an Amazon RDS via PrivateLink using SQL
Tags: aws, postgres, sql, tutorial
Published: 2023-03-29 20:55:59


Do you have some database instances on RDS and wonder what's the most secure way to reach them? In this post, we will walk you through how to securely connect to an AWS RDS instance using PrivateLink and IaSQL. AWS PrivateLink provides private connectivity between virtual private clouds (VPCs), supported AWS services, and your on-premises networks without exposing your traffic to the public internet. IaSQL is an [open-source](https://github.com/iasql/iasql) software tool that creates a two-way connection between an unmodified PostgreSQL database and an AWS account so you can manage your infrastructure from a database.

When creating a database, AWS provides some specific information about the hostnames and ports used. You can access those to reach your databases by using specific clients for MySQL, Postgres, etc...:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lyif41v4pes3b1egsbgo.png)

On a public VPC, those endpoints will be public by default. However, consider creating them under a private VPC and accessing them internally, to grant additional security to critical services. AWS PrivateLink offers the possibility to securely access those services without exposing them to the internet, just using Amazon's private network.

### Are my RDS instances properly configured?

Please use this query to check it:

```sql
 ---- Installing the needed modules
SELECT
  iasql_install ('aws_rds', 'aws_vpc');

-- Perform the query for endpoints
SELECT
  rds.region,
  vpc.is_default,
  vpc.cidr_block,
  (
    SELECT
      COUNT(*) > 0
    FROM
      endpoint_interface
    WHERE
      endpoint_interface.region = rds.region
      AND service = 'rds'
      AND endpoint_interface.vpc_id = vpc.id
  ) AS has_endpoint_interface
FROM
  rds
  LEFT OUTER JOIN vpc ON vpc.region = rds.region;
```

Have you found missing endpoints? No problem, IaSQL can generate missing Endpoint Interfaces for you:

```sql
SELECT
  *
FROM
  iasql_begin ();

-- Inserts the missing endpoints
INSERT INTO
  endpoint_interface (region, vpc_id, service, private_dns_enabled)
SELECT
  RDS.region,
  vpc.id,
  'rds',
  TRUE
FROM
  rds
  INNER JOIN vpc ON rds.region = vpc.region
WHERE
  NOT EXISTS (
    SELECT
      id
    FROM
      endpoint_interface
    WHERE
      endpoint_interface.region = rds.region
      AND endpoint_interface.vpc_id = vpc.id
  );

-- Preview the changes
SELECT
  *
FROM
  iasql_preview ();

-- Apply the changes
--select * from iasql_commit();
-- Rollback the changes
select * from iasql_rollback();
```

Running this query on an IaSQL database will auto-generate the missing endpoint interfaces and will allow you to preview the changes to be applied on your cloud. Once you are OK with the results, you can uncomment the `iasql_commit` query, comment the `iasql_rollback` query, and it will create the endpoints for you. If the final results in the cloud are not as expected, you can always roll back your changes by calling the `iasql_rollback` command.

### Testing the result

After running the query, you should have Endpoint Interfaces created for your RDS resources. Those should be on the region and VPC where you had your databases:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3t739za4kz7vgzk3376q.png)

To start testing the result, you can start a new EC2 instance on a private VPC in the same region where your RDS and Endpoint interface is configured. You can double-check that there is no internet connectivity. But the RDS endpoint could still be reached, by using the interface that has been created:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rqhqpzqyueo97g4vm373.png)

Please note that you could only use those endpoints from the same region. You could reach the services in multiple regions with the use of [VPC peering](https://docs.aws.amazon.com/vpc/latest/peering/what-is-vpc-peering.html)