【Python】PythonからSSH経由（SSHTunnel）でMySQLにアクセスする

Python
2023.06.01

ローカルで動かしているPythonから、Xserver VPS上で動いているデータベースにアクセスする方法を記載しています。

ローカルからサーバへのアクセス

課題と対応方針

ローカルからサーバ上のDBにアクセスする為に、単純な方法としてはSQL Queryをサーバ側でローカルから直接受け付けられるようにfirewalldの設定を変更すれば可能です。

但し、セキュリティ対策で外部からは以下のアクセスしか許可していません。

  • Webサーバへのアクセス
  • 自身のアクセスのみを想定して、IPアドレス限定でのSSHアクセス

このあたりの経緯は、【Xserver VPS】ファイアウォールの設定、【Xserver VPS】KUSANAGI × WordPressのセキュリティ設定 (1)、【Xserver VPS】KUSANAGI × WordPressのセキュリティ設定 (2)で触れています。

そうなると、単純に外部からSQLサーバにアクセスできるようなセキュリティ緩和はしたくありません。WordPressを動かしているので、SQLサーバは守りたいですし、SQL経由で不正アクセスされる可能性だって上がりますしね。

という事で、色々と調べた結果、以下の方針で実現することとしました。

  • ファイアウォールの設定は一切変更しない。
  • SSH経由でサーバにアクセスし、そこからローカルとしてSQLサーバにアクセスする。

これらはPythonのSSHTunnelパッケージを使用することで実現可能です。

SSHトンネルとは

SSHトンネルでは、ローカルのポートがリモートのポートに転送されます。それらのポート間の通信はSSHを通じて行われます。この為、追加でポートを開放する必要がありませんし、通信そのものがSSHで暗号化される利点もあります。

構築の方針

以下の図のようなアクセスを目指します。

以下の手順でMaria DBにアクセスを行います。

  1. SSHトンネル接続を確立し、ローカルの3306番ポートをサーバのServerのローカルバインドポート@localhostと接続する。
  2. PythonからLocalの3306番ポートに接続してSQLクエリを実行する。

Pythonでのコーディング

SSHTunnelのインストール

pipを使用している場合はpip install sshtunnelでインストールが可能です。

コード全体

import pymysql
from sshtunnel import SSHTunnelForwarder

sshConfig = {
    "host": "SSHサーバのホスト名",
    "port": SSHポート番号,
#    "ssh_username": "SSHログインするユーザ名",
#    "ssh_pkey": "SSH秘密鍵のパス",
    "key_password": "SSH秘密鍵のパスフレーズ",
    "allow_agent": False
}

dbConfig = {
    "host": "localhost",
    "localhost": "localhost",
    "port": 3306,
    "user": "DBユーザ名",
    "password": "DBパスワード",
    "database": "接続したいDB",
    "charset": "utf8"
}

# SSHトンネルを通じてMySQL に接続
with SSHTunnelForwarder(
        (sshConfig["host"], sshConfig["port"]),
#         ssh_username=sshConfig["ssh_username"],
#         ssh_pkey=sshConfig["ssh_pkey"],
#         ssh_private_key_password=sshConfig["key_password"],
         allow_agent=sshConfig["allow_agent"],
     　  remote_bind_address=(dbConfig["host"], dbConfig["port"])
) as server:
    connection = pymysql.connect(
                    host=dbConfig["localhost"],
                    port = server.local_bind_port,
                    user = dbConfig["user"],
                    password = dbConfig["password"],
                    database = dbConfig["database"],
                    charset=dbConfig["charset"],
                    cursorclass=pymysql.cursors.DictCursor
                )

    sql = "SELECT * FROM your_table_name"

    with connection.cursor() as cursor:
        cursor.execute(sql)
        result = cursor.fetchall()
    connection.commit()

print(result)
ライブラリのインポート（1-2行目）
import pymysql
from sshtunnel import SSHTunnelForwarder

MySQLへのアクセスに今回はpymysqlを利用しています。勿論、sqlalchemy等でも構いません。

そして、SSHポート転送用にsshtunnelパッケージからSSHTunnelForwarderをインポートします。

SSH接続に必要な情報（4-11行目）
sshConfig = {
    "host": "SSHサーバのホスト名 or Configファイルのホスト名",
    "port": SSHポート番号,
#    "ssh_username": "SSHログインするユーザ名",
#    "ssh_pkey": "SSH秘密鍵のパス",
    "key_password": "SSH秘密鍵のパスフレーズ",
    "allow_agent": False
}

SSH接続に必要な情報を記載しています。SSH接続にConfigファイルで設定している場合には、そのホスト名を使用できます。その場合は、ssh_username、ssh_pkeyの記載は不要ですので、ここではコメントアウトしています。

Configファイルについては、【Xserver VPS】SSHの設定に記載してあります。

DB接続に必要な情報（13-21行目）
dbConfig = {
    "host": "localhost",
    "localhost": "localhost",
    "port": 3306,
    "user": "DBユーザ名",
    "password": "DBパスワード",
    "database": "接続したいDB",
    "charset": "utf8"
}

DB接続に必要な情報を記載しています。SSHトンネル経由ならではの設定は以下となります。

  • host
    SSHTunnel経由でのアクセスですのでトンネルを出たところから見たDBのホスト名となり、localhostとします。
  • localhost
    SSHTunnel入口のlocalhostを指定するのでlocalhostとします。
  • port
    SSHトンネルを出たリモートでのMySQLへの接続ポートなので3306とします。
SSHトンネルを経由してDBに接続する（24-40行目）
with SSHTunnelForwarder(
        (sshConfig["host"], sshConfig["port"]),
#         ssh_username=sshConfig["ssh_username"],
#         ssh_pkey=sshConfig["ssh_pkey"],
#         ssh_private_key_password=sshConfig["key_password"],
         allow_agent=sshConfig["allow_agent"],
     　  remote_bind_address=(dbConfig["host"], dbConfig["port"])
) as server:
    connection = pymysql.connect(
                    host=dbConfig["localhost"],
                    port = server.local_bind_port,
                    user = dbConfig["user"],
                    password = dbConfig["password"],
                    database = dbConfig["database"],
                    charset=dbConfig["charset"],
                    cursorclass=pymysql.cursors.DictCursor
                )

SSHTunnelForwarderを使用してローカルとリモートをトンネル接続し、それを経由してMySQLに接続します。

26-27行目はSSH接続用にConfigファイルで設定している場合は不要なのでコメントアウトしています。

33-34行目の記載の通り、Pythonプログラムから見るとMySQLサーバへの接続先はローカルのポートとなります。

ローカルのポート番号はSSHTunnelForwarderクラスのlocal_bind_port変数で取得しますので、コードではserver.local_bind_portとしています（34行目）。

SQL文の実行（42-49行目）
    sql = "SELECT * FROM your_table_name"

    with connection.cursor() as cursor:
        cursor.execute(sql)
        result = cursor.fetchall()
    connection.commit()

print(result)

テストとして、テーブルの全データを取得してみます。cursorを作成してSQL文を実行し、結果を表示させます。テーブルの全データが表示されればOKです。

コメント

タイトルとURLをコピーしました