PythonでSSHトンネリングしてデータベースアクセス

f:id:piro_suke:20160625013046j:plain

稼働中のWebアプリのデータベースに定期的にアクセスして

データの状態をチェックしたい、でもデータベースには

外部から直アクセスできないようになっているので、

SSHトンネル経由でないとアクセスできない、という場合。

A5:SQLなりpgAdminなり最近のDBアクセスツールは

大抵SSH経由でのアクセスに対応しているので、

こういったツールを使えばDBに手動アクセスすることができる。

A5:SQL Mk-2 - フリーの汎用SQL開発ツール/ER図ツール

自分で書いたプログラムからSSH経由でDBにアクセスしたい場合は、

WindowsならPutty(SSHクライアントソフト)が

SSHトンネリング機能を持っているので、PuttyなりCUIツールの

plinkなりを立ち上げておくことで割と簡単に実現できる。

qiita.com

今回紹介するのは、

Windows以外のクライアントからアクセスする場合だったり、

WindowsだけどPutty立ち上げるのめんどくさい、という場合に

PythonプログラムでSSHトンネリングを実現する方法。

sshtunnelパッケージを使う

PythonでSSH/SCPクライアント機能を実現するためのパッケージとして、

paramikoという（知らなかったけど）有名なパッケージがある。

github.com

paramikoでSSHトンネリングが実現できないかと調べてみたけど

よく分からなかったので、他の選択肢を探していたら、

paramikoの機能を使ってSSHトンネリング機能を

提供するsshtunnelというパッケージにたどり着いた。

github.com

このパッケージを使うことで

Windows7+Python3環境でSSHトンネル経由でDBアクセスできたので、

使い方を紹介する。

まずはpipでパッケージをインストールする：

pip install sshtunnel

トンネルの開き方はファイルを開く方法と似ていて、

open-closeとwithブロックのどちらにも対応している。

今回は好みのwithブロック版で試した。

open-close版（実際のメソッド名はstart-stop）のサンプルも

上記のGithubページに記載されているので、そちらをご参照ください。

トンネルの作成はSSHTunnelForwarder関数を使用して行う：

from sshtunnel import SSHTunnelForwarder
...
with SSHTunnelForwarder(
    ("＜SSHサーバアドレス＞", ＜SSHサーバポート＞),
    ssh_host_key="＜SSHホストキー(不要ならNone)＞",
    ssh_username="＜SSHユーザー名＞",
    ssh_password="＜SSHパスワードもしくは鍵ファイルのパスフレーズ＞",
    ssh_pkey="＜SSH鍵ファイルのパス(パスワード認証ならNone)＞",
    remote_bind_address=("＜SSHサーバから見た接続先サーバのアドレス＞", ＜SSHサーバから見た接続先サーバのポート＞)
) as server:
    # SSHトンネル内処理
    # 「server.local_bind_port」でトンネリング用に確保されたローカルポート番号を取得できる

SSHトンネル接続中は、「localhost」の「server.local_bind_port」経由で

remote_bind_addressで指定したリモートのDBサーバなりWebサーバなりにアクセスできる。

例えばSSHサーバ上の5432ポートで動いているPostgreSQLにアクセスしたい場合は、

remote_bind_address=("localhost", 5432)

と指定する。

SQLAlchemyでリモートDBサーバにアクセスするなら

下記のようなコードでアクセスできる。

from sshtunnel import SSHTunnelForwarder
import sqlalchemy

db_info = {
    "user": "testdbuser",
    "pass": "testdbpass",
    "dbname": "testdbname"
}

with SSHTunnelForwarder(
    ("192.168.0.200", 22),
    ssh_host_key=None,
    ssh_username="testuser",
    ssh_password="testpass",
    ssh_pkey=None,
    remote_bind_address=("localhost", 5432)
) as server:
    engine = sqlalchemy.create_engine("postgresql+psycopg2://{user}:{pass}@localhost:{db_port}/{name}".format(db_port=server.local_bind_port, **db_info), client_encoding="utf8")
    with engine.connect() as conn:
        # DB処理