ポートフォワードを行いローカル環境からプライベートサブネットのAmazonRDSを操作する

こんにちはデータアナリティクス事業本部の下地です。

AmazonRDS(RDS)をプライベートサブネットに配置し、EC2の踏み台経由にアクセスすることでセキュリティを高める構成方法があります。

今回はポートフォワードを行い、プライベートサブネットに配置したRDSにローカルPCのPython環境からRDSのデータベース操作を行う方法について実装しましたのでまとめたいと思います。

開発環境

環境は以下の通りです。EC2とRDSは起動している状態からスタートします。RDSはPostgresを選択しました。

• macOS: Catalina
• python: 3.7.0
• psycopg2: 2.0

ローカルPCからプライベートRDSへの全体像

ローカルPCからパブリックサブネットに配置した踏み台(EC2)を経由してプライベートサブネットのRDSに接続するための全体像です。

ローカルPCに踏み台(EC2)経由のポートフォワードを行うことでAWS上のプライベートRDSをlocalhostとして扱えるようにします。その後ローカル環境からpythonでデータベースを操作します。操作に関してはpsycopg2を使用しました。

作成手順

今回は下記内容の設定を行い、データベースの操作確認を行います。

1. セキュリティグループの設定
2. ポートフォワード
3. ローカル環境のpythonからデータベースを操作する

1. セキュリティグループ(sg)の設定

EC2、RDSにそれぞれ必要なセキュリティグループの設定を行います。

• • EC2にアタッチするsg[指定したIPアドレスからのssh接続を許可する]

• • RDSにアタッチするsg[EC2からの通信をポスグレのポートに接続する]

2. ポートフォワード

プライベートサブネット上のRDSをローカルから操作ために、EC2を踏み台として経由するポートフォワードを行います。 ポートフォワードの設定については下記リンクを参考にしました。

sshポートフォワーディング

プライベートサブネットにあるRDSを踏み台(EC2)経由してローカルPCにポートフォワードするには下記コマンドで実行します。 ssh時にオプションの-Lを使用してポート先:エンドポイント:ポート元になるように設定します。

$ ssh -i ~/.ssh/*******.pem ec2-user@**.**.***.** -L 15432:[RDSエンドポイント]:5432

3. ローカル環境のpythonからデータベースを操作する

ポートフォワードできましたので次はpython環境から接続します。psycopg2の扱い方に関しは以下のリンクを参考にしました。

psycopg2 でよくやる操作まとめ

接続コネクションを設定

# -*- coding: utf-8 -*-
import psycopg2

DATABASE = 'postgres'
HOSTNAME = 'localhost'
USERNAME = データベースのユーザー名
USERPASSWORD = データベースのパスワード
PORTFORWORD = 指定したポート番号

def get_connection():
    conn = psycopg2.connect(
        host = HOSTNAME,
        port = PORTFORWORD,
        user = USERNAME,
        password = USERPASSWORD,
        database = DATABASE
        )        
    return conn

テーブルを作成しデータをinsertする

with get_connection() as conn:
    with conn.cursor() as cur:
        cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")
        cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)", (100, "abc"))
        cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)", (200, "def"))

データ確認

作成しinsertしたデータの確認を行う。下記のコマンドを実行するとinsertしたデータが入っていることがわかる。

with get_connection() as conn:
    with conn.cursor() as cur:
        cur.execute("select * from test;")
        print(cur.fetchall())

[(1, 100, "abc"), (2, 200, "def")]

まとめ

今回は、ローカルのPCからプライベートサブネットのRDSに接続するため踏み台経由で接続する方法について試しました。セキュリティグループやデータベースへのコネクションの設定など詰まるところもあったのですが、他の方のブログを参考に接続することができました。踏み台経由で接続することはセキュリティ面からも大切ですのでこの記事がどなたかの助けになれば幸いです。

参考URL

• sshポートフォワーディング

• SSHポートフォワーディングを知った話

• psycopg2 でよくやる操作まとめ