rds-setsuzoku-chan (RDS接続ちゃん) is a utility script to establish a AWS Systems Manager - Session Manager port-forwarding connection for Amazon RDS via a bastion host.

rds-setsuzoku-chan

#!/usr/bin/env bash

set -euo pipefail

#===============================================================================
# rds-setsuzoku-chan (RDS SETSUZOKU CHAN)
#
# <README>
# https://gist.github.com/5t111111/5e864858250c555da9e4232242af1591#file-rds-setsuzoku-chan-md
#===============================================================================
function usage {
  cat <<EOS
Usage:
  -s|--secretname <Secret Name>             Secret Manager secret name for database connection
  -n|--bastionname <Bastion Instance Name>  Bastion EC2 instance name (either -n / --bastionname or -i / --instancename must be specified)
  -i|--bastionid <Bastion Instance ID>      Bastion EC2 instance ID (either -n / --bastionname or -i / --instancename must be specified)
  -p|--port <Local Port>                    <Option> Local port number for port forwarding (Default to 43306)
  --showpassword=yes                        Display the password in the output (Default to no)
  --getinfo                                 Show information of secrets and instances for the urrent AWS account
  -h|--help                                 Show this usage

Example:
  ./rds-setsuzoku-chan -s DatabaseInstanceSecret-xxxxxxx -p 53306 -n BastionHost
EOS
}

function caller_information {
  cat <<EOS
-----------------------------------------------------------------------
Caller information
-----------------------------------------------------------------------
EOS

  aws sts get-caller-identity | jq
}

function getinfo {
  caller_information

  declare -a ec2_array
  declare -a secrets_array

  # get EC2 instances
  ec2_instances=$(aws ec2 describe-instances | jq -r '.Reservations[].Instances[] | select(.State.Name != "terminated") | {Id: .InstanceId, Name: ( .Tags[] | select(.Key == "Name") | .Value ), VpcId: .VpcId}')

  echo ""
  echo -n "Acquiring EC2 instance information..."
  # loop through EC2 instances to get VPC names
  for row in $(echo "${ec2_instances}" | jq -r '. | @base64'); do
    _jq() {
        echo ${row} | base64 --decode | jq -r ${1}
    }

    instance_id=$(_jq '.Id')
    instance_name=$(_jq '.Name')
    vpc_id=$(_jq '.VpcId')
    vpc_name=$(aws ec2 describe-vpcs --vpc-ids ${vpc_id} | jq -r '.Vpcs[0].Tags[] | select(.Key == "Name") | .Value')
    ec2_array+=("{\"Id\":\"${instance_id}\",\"Name\":\"${instance_name}\",\"VpcName\":\"${vpc_name}\"},")

    # Display progress
    echo -n '.'
  done

  echo ""
  echo -n "Acquiring Secrets information..."
  secrets=$(aws secretsmanager list-secrets | jq -r '.SecretList[] | {Name: .Name}')

  # loop through Secrets
  for row in $(echo "${secrets}" | jq -r '. | @base64'); do
    _jq() {
        echo ${row} | base64 --decode | jq -r ${1}
    }

    secret_name=$(_jq '.Name')
    secrets_array+=("{\"Name\":\"${secret_name}\"},")

    # Display progress
    echo -n '.'
  done

  # output the result
  if [ ${#ec2_array[@]} -gt 0 ]; then
    ec2_array_str=${ec2_array[*]}
  else
    ec2_array_str=''
  fi

  if [ "${ec2_array_str: -1}" == "," ]; then
    # Remove the last character
    ec2_array_str="${ec2_array_str%?}"
  fi

  if [ ${#secrets_array[@]} -gt 0 ]; then
    secrets_array_str=${secrets_array[*]}
  else
    secrets_array_str=''
  fi

  if [ "${secrets_array_str: -1}" == "," ]; then
    # Remove the last character
    secrets_array_str="${secrets_array_str%?}"
  fi

  cat <<EOS


-----------------------------------------------------------------------
Registered EC2 instances and Secrets
-----------------------------------------------------------------------
EOS

  echo "{\"Ec2Instances\":[${ec2_array_str}],\"Secrets\":[${secrets_array_str}]}" | jq
}

if [ $# -eq 0 ]; then
  usage
  exit 0
fi

for arg in "$@"
do
  case $arg in
    -h|--help)
      help_flag="true"
      shift
      ;;
    --getinfo)
      getinfo_flag="true"
      shift
      ;;
  esac
done

if [[ ${help_flag:-"false"} == "true" ]]; then
  usage
  exit 0
elif [[ ${getinfo_flag:-"false"} == "true" ]]; then
  getinfo
  exit 0
fi

while getopts s:n:i:p:-: opt; do
  optarg="$OPTARG"

  if [[ "$opt" = - ]]; then
    opt="-${OPTARG%%=*}"
    optarg="${OPTARG/${OPTARG%%=*}/}"
    optarg="${optarg#=}"

    if [[ -z "$optarg" ]] && [[ ! "${!OPTIND}" = -* ]]; then
      optarg="${!OPTIND}"
      shift
    fi
  fi

  case "-$opt" in
    -s|--secretname)
      secret_name="$optarg"
      ;;
    -n|--bastionname)
      bastion_instance_name="$optarg"
      ;;
    -i|--bastionid)
      bastion_instance_id="$optarg"
      ;;
    -p|--port)
      local_port="$optarg"
      ;;
    --showpassword)
      show_password="$optarg"
      ;;
    \?)
      usage
      exit 0
      ;;
  esac
done

shift $((OPTIND - 1))

if [ -z ${secret_name:-""} ]; then
  echo 'Secret Name (-s) must be specified.' >&2
  exit 1
fi

instance_name="${bastion_instance_name:-}"
instance_id="${bastion_instance_id:-}"

if [ ! -z $instance_name ] && [ ! -z $instance_id ]; then
  echo 'Bastion Instance Name (-n) and ID (-i) cannot be specified at the same time.' >&2
  exit 1
elif [ -z $instance_name ] && [ -z $instance_id ]; then
  echo 'Either Bastion Instance Name (-n) or ID (-i) must be specified.' >&2
  exit 1
fi

caller_information

echo ""
echo -n "Preparing session..."

if [ -z $instance_id ]; then
  instance_id=$(aws ec2 describe-instances --filter "Name=instance-state-name,Values=running" "Name=tag:Name,Values=${bastion_instance_name}" --query "Reservations[].Instances[].InstanceId"  | jq -r '.[0]')
  # Display progress
  echo -n '.'
fi

secret=$(aws secretsmanager get-secret-value --region ap-northeast-1 --secret-id $secret_name  | jq .SecretString | jq fromjson)

host=$(echo $secret | jq -r .host)
port=$(echo $secret | jq -r .port)
username=$(echo $secret | jq -r .username)
password=$(echo $secret | jq -r .password)
engine=$(echo $secret | jq -r .engine)
dbname=$(echo $secret | jq -r .dbname)
local_port=${local_port:-43306}

# Display progress
echo -n '.'

cat <<EOS


-----------------------------------------------------------------------
RDS information
-----------------------------------------------------------------------
EOS

if [[ ${host} == "null" ]]; then
  echo host: N/A
else
  echo host: ${host}
fi

if [[ ${port} == "null" ]]; then
  echo port: N/A
else
  echo port: ${port}
fi

if [[ ${engine} == "null" ]]; then
  echo engine: N/A
else
  echo engine: ${engine}
fi

if [[ ${dbname} == "null" ]]; then
  echo dbname: N/A
else
  echo dbname: ${dbname}
fi

cat <<EOS

-----------------------------------------------------------------------
Connection information
-----------------------------------------------------------------------
EOS

echo "host: 127.0.0.1 | localhost"
echo port: ${local_port}

if [[ ${username} == "null" ]]; then
  echo username: N/A
else
  echo username: ${username}
fi

if [[ ${show_password:-"no"} == "yes" ]]; then
  if [[ ${password} == "null" ]]; then
    echo password: N/A
  else
    echo "password: ${password}"
  fi
else
  echo "password: *************************"
fi

echo ""

# Error when Failed to retrieve RDS host endpoint and connection port information
if [[ ${host} == "null" && ${port} != "null" ]]; then
  echo "ERROR: Failed to retrieve RDS host endpoint. Unable to start session. Are you sure the host value is registered in the Secrets Manager secret?"
  exit 1
elif [[ ${host} != "null" && ${port} == "null" ]]; then
  echo "ERROR: Failed to retrieve RDS host connection port. Unable to start session. Are you sure the port value is registered in the Secrets Manager secret?"
  exit 1
elif [[ ${host} == "null" && ${port} == "null" ]]; then
  echo "ERROR: Failed to retrieve RDS host endpoint and connection port. Unable to start session. Are you sure the host and port values are registered in the Secrets Manager secret?"
  exit 1
fi

cat <<EOS
-----------------------------------------------------------------------

EOS

if [[ ${engine} == "mysql" ]]; then
  echo "You can connect to the database via MySQL client with the following command once session is started:"
  echo ""
  if [[ ${dbname} == "null" ]]; then
    echo "mysql -h 127.0.0.1 -P ${local_port} -u ${username} -p"
  else
    echo "mysql -h 127.0.0.1 -P ${local_port} -u ${username} -p ${dbname}"
  fi
elif [[ ${engine} == "postgres" ]]; then
  echo "You can connect to the database via PostgreSQL client with the following command once session is started:"
  echo ""
  if [[ ${dbname} == "null" ]]; then
    echo "psql -h 127.0.0.1 -p ${local_port} -U ${username} -d <database name>"
  else
    echo "psql -h 127.0.0.1 -p ${local_port} -U ${username} -d ${dbname}"
  fi
else
  echo "Unsupported database engine. Although a session manager session can be started, please try connecting to the target RDS on localhost port ${port} with a client that supports the corresponding RDS engine."
fi

cat <<EOS

-----------------------------------------------------------------------

Establishing session...
EOS

aws ssm start-session \
  --target ${instance_id} \
  --document-name AWS-StartPortForwardingSessionToRemoteHost \
  --parameters host=${host},portNumber=${port},localPortNumber=${local_port}

rds-setsuzoku-chan.md

rds-setsuzoku-chan (RDS接続ちゃん)

rds-setsuzoku-chan (RDS接続ちゃん) is a utility script to establish a AWS Systems Manager - Session Manager port-forwarding connection for Amazon RDS via a bastion host. Since it uses Session Manager to connect to a bastion server, there is no need to manage SSH key pairs.

Requirements

Requirements for the connection source (your local machine)

• AWS CLI is installed and configured with valid credentials
• Session Manager plugin for AWS CLI is installed
• AWS CLI output format is set to JSON (default is JSON, so you might be OK)
• A database client corresponding to the target RDS is installed
• A bash script is executable in the terminal
• jq is installed

AWS resource requirements for connection

• The bastion server is configured to be connectable via Session Manager
• RDS connection information is registered in AWS Secret Manager, and as the secret values, it must contain connection information as the following key / value pairs:
  • host : RDS connection endpoint
  • port : RDS connection port
  • username : RDS connection username
  • password : RDS connection user's password
  • (optional) engine : RDS database engine. It is only used to display the example of the client CLI command to connect (currently only MySQL, Aurora MySQL, PostgreSQL and Aurora PostgreSQL are supported)
  • (optional) database : RDS default database name. It is only used to display the example of the client CLI command to connect too

Note that the above format of the connection information data in Secret Manager is compatible with the one set when configuring RDS with AWS CDK constructs.

Information required for connection

• "EC2 instance name" or "EC2 instance ID" of the bastion server
• "Secret name" in Secrets Manager where RDS connection information is registered

Usage

Starting a connection session

Execute the script with:

• the specified secret name (-s or --secretname)
• either the bastion host instance name (-n or --bastionname) or instance ID (-i or --bastionid) to start a session.

Example of specifying an EC2 instance name:

./rds-setsuzoku-chan -s <secret name> -n <bastion name>

Example of specifying an EC2 instance ID:

./rds-setsuzoku-chan -s <secret name> -i <bastion ID>

You will see kind of the following output:

-----------------------------------------------------------------------
RDS information
-----------------------------------------------------------------------
host: xxxxxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com
port: 3306
engine: mysql
dbname: db_name

-----------------------------------------------------------------------
Connection information
-----------------------------------------------------------------------
host: 127.0.0.1 | localhost
port: 43306
username: db_admin
password: *************************

-----------------------------------------------------------------------

You can connect to the database via MySQL client with the following command once session is started:

mysql -h 127.0.0.1 -P 43306 -u admin -p db_name

-----------------------------------------------------------------------

Establishing session...

Starting session with SessionId: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Now you can use your preferred database client to connect to the RDS via the port-forwarding connection.

By default, the database password is masked and not displayed in the output. If you want to display the password in the output, use the --showpassword=yes option.

Convenient option to get EC2 instance and secret information registered in the AWS account

There is --getinfo option allows you to list registered EC2 instances and secrets in your configured AWS account / region.

./rds-setsuzoku-chan --getinfo

CLI options

• -s <Secret Name> / --secretname=<Secret Name>
  • Secret Manager secret name for database connection
• -n <Bastion Instance Name> / --bastionname=<Bastion Instance Name>
  • Bastion EC2 instance name (either -n / --bastionname or -i / --instancename must be specified)
• -i <Bastion Instance ID> / bastionid=<Bastion Instance ID>
  • Bastion EC2 instance ID (either -n / --bastionname or -i / --instancename must be specified)
• -p <Local Port> / --port=<Local Port>
  • (Optional) Local port number for port forwarding (Default to 43306)
• --showpassword=yes
  • Display the password in the output (Default to no)
• --getinfo
  • Show information of secrets and instances for the current configured AWS account / region
• -h / --help
  • Show help

FAQ

When RDS Secret Manager integration is enabled

When using the built-in RDS Secret Manager integration feature, secrets are automatically registered and their values are managed by RDS. Unfortunately, the automatically configured secrets do not include information such as host endpoints or ports, so it is not possible to automatically start a session using the retrieved values from the secret.

However, since this script can retrieve information such as the username, it is possible to execute ssm start-session directly from the AWS CLI by supplementing the missing information yourself.

aws ssm start-session \
  --target <your bastion host instance id> \
  --document-name AWS-StartPortForwardingSessionToRemoteHost \
  --parameters host=<your RDS endpoint>,portNumber=<your RDS port>,localPortNumber=<local port you provided>

How to run the script without downloading it locally

Since there is a possibility that the network is unstable and there is no guarantee that the content of the Gist will not be changed maliciously, this method is not recommended in general.

It is also possible to execute the script directly by specifying the script URL as follows:

curl -sSL https://gist.githubusercontent.com/5t111111/0d596d980be2a76ea20fe91eea6ca4ad/raw/rds-setsuzoku-chan | bash /dev/stdin -s <secret name> -n <bastion server instance name>

Of course, you can also use --getinfo without downloading:

curl -sSL https://gist.githubusercontent.com/5t111111/0d596d980be2a76ea20fe91eea6ca4ad/raw/rds-setsuzoku-chan | bash /dev/stdin --getinfo