Connect to a database from your computer

This guide shows you how to connect to a database via localhost on a specific port.

Reference implementation

See how RDS bastion is configured in pirates-iac.

Things to consider

The database will be accessible by anyone who can log in to the AWS account.

If you choose to omit the optional step involving the setup of VPC endpoints, it's important to understand the implications. When you run the ok forward command, as outlined in this guide, it initiates an ECS task running Nginx. This setup allows the container to access your database. However, it also grants Nginx access to the entire internet, denoted by the CIDR block 0.0.0.0/0. You should consider the security implications of this, but as a general guideline, you should not run this in production.

Before you begin

You have followed the setup guide and have a working environment
You have a database in a private subnet
You have an ECS cluster in a private subnet

Step 1: Configure

In your IaC repository on disk, enter the infra directory.

Download the template from the Golden Path repository

# Current directory: "our-repository-iac/dev/infra"
ok get-template rds_bastion

Open the file rds_bastion.tf in your favorite editor.
Set the name variable to a name of your choice. This will be used to name the resources created by the module, and should be unique within your environment. You can leave it as it is.
```
# Example:
name = "${local.environment}-main"
```
Set the cluster_name variable to the name of the ECS cluster you want to use for the port forwarding.

If you are unsure what name to use, you can find the name of the ECS cluster in ecs_cluster.tf file in the directory for which stack this exists in. Typically this is infra. If the variable cluster_name is set to local.environment, then you can use the same value for cluster_name in this module.
```
# Example:
cluster_name = "my-ecs-cluster" # or "local.environment"
```
Set the db_name or db_names variable to the name or names of the databases you want to port forward to.

If you have multiple RDS instances in your environment that you want to support port forwarding to, you can use the db_names variable to specify a list of database names instead of just one.

If you are unsure what the name of the database is, you can find the name of the database in the file typically named with something like postgres_aurora.tf in the directory for which stack this exists in. Typically this is infra. The name of the database is the value of the name variable.
```
# Example:
db_name = "my-database"
# or
db_names = ["my-database", "my-other-database"]
```
There are other variables that can be set, but these are optional. See the variables.tf file for more information.

Step 2: Enhance container security with VPC endpoints (optional)

To enhance the security of your container and prevent it from accessing the internet directly, you can utilize VPC endpoints.

Before you begin

Ensure you have set up VPC endpoints. For guidance, refer to Create VPC endpoints for ECS services.
Make sure you have a suitable container image in your private ECR registry. For details on setting this up, see Create and use ECR pull through cache rules.

Once you have the VPC endpoints set up and your container image in place, the next step is to enable VPC endpoint support in your Terraform configuration.

Open the rds_bastion.tf file.
Modify the file by setting the use_vpc_endpoints variable to true. This enables the use of VPC endpoints.
Set the container_image_uri variable to the URI of your container image in the private ECR registry.

An example configuration looks like this:

use_vpc_endpoints   = true
container_image_uri = "${var.account_id}.dkr.ecr.${var.region}.amazonaws.com/${var.environment}-ecr-public/nginx/nginx:alpine-slim"

Reference implementation

See how RDS bastion is configured in pirates-iac.

Step 3: Apply the configuration

Run terraform init to initialize the Terraform environment.
Run terraform plan to see what resources will be created.
Run terraform apply to create the resources.
Commit rds_bastion.tf and the builds directory.

You might notice that the terraform apply command generates a lot of output. This is because the module creates a Lambda function based of a package.json file and then zips it up. This is just noise and can be ignored.

What is the builds directory?

terraform apply will create a directory called builds. This should be committed to your IaC repository. If not, future runs of terraform apply will always detect a change.

Step 3: Connect to database

Run the ok forward command to start the port forwarding session.
```
ok forward
```
You will be presented a list of Task Definitions which is applicable for port forwarding. Select the one you want to use using the arrow keys and press enter.
```
> ssm-pf-my-bastion-task-definition
> ssm-pf-another-bastion-task-definition
```
Now a new ECS task will be started. This usually takes 20 to 30 seconds.
Next you will be presented a list of all the RDS instances in your environment. Select the one you want to port forward to using the arrow keys and press enter.
```
> my-team-db.c7d1xxxi7fm.eu-west-1.rds.amazonaws.com
> some-other-db.c7d1xxxi7fm.eu-west-1.rds.amazonaws.com
```
Last step is to enter the ports you want to forward to and expose locally for the forwarded database.

Now the database is port forwarded and you can connect to it using the forwarded ports.

LOCAL_PORT=4812 # or replace this with the port you entered in step 4
psql --host=localhost --port=$LOCAL_PORT --username=db_username --password --dbname=postgres

When you are done, press Ctrl+C to stop the port forwarding session.

If you don't stop the port forwarding session manually, it will be stopped automatically by the Lambda function after some time of inactivity.