带有跳转主机和远程数据库的SSH隧道转发

我有一个托管在 Amazon RDS (“D”) 上的远程 MySQL 数据库。出于安全目的，只能通过远程服务器（“C”）访问它。 C 可以通过跳转主机“B”通过 ssh 访问。我需要一个双 ssh 隧道来访问远程 SQL 主机。

[A: local host] -> [B: jump host] -> [C: target host] => [D: RDS MySQL host]

我想使用 paramiko 和/或 sshtunnel 通过 Python 访问 D。我能找到的所有信息涉及：

• a single ssh tunnel and a remote SQL host (ex. A -> C => D, no jump host)
  • 首先在 python 中使用 mysqldb 进行 ssh https://stackoverflow.com/a/31890160/6412017
  • python 通过 ssh 连接 mysql https://stackoverflow.com/a/33016779/6412017
• a double ssh tunnel to an SQL host (ex. A -> B -> C, D is hosted on C).
  • 使用 python 通过 ssh 隧道连接到远程 Postgresql 数据库 https://stackoverflow.com/a/31890133/6412017
  • Paramiko：NAT 路由器周围的端口转发 https://stackoverflow.com/a/19039769/6412017
  • 与 Paramiko 的嵌套 SSH 会话 https://stackoverflow.com/a/10432669/6412017

到目前为止，我使用 paramiko 和代理命令从 A 到 C。我可以通过在 C 上执行命令来访问 D，但不能通过连接 mysqldb 或 sqlalchemy（我的最终目标）来访问 D。

我当前的代码：

import paramiko

ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
proxy = paramiko.ProxyCommand("ssh -A B_username@B_host -W C_host:12345")
ssh.connect("C_host", username="C_username", sock=proxy)

stdin, stdout, stderr = ssh.exec_command("mysql -u D_username -p D_password -h D_host_rds")
print("STDOUT:\n{}\n\nSTDERR:\n{}\n".format(stdout.read(), stderr.read()))
# successfully prints out MySQL welcome screen

我正在寻找类似的东西（根据示例2进行修改）ssh隧道文档 https://pypi.python.org/pypi/sshtunnel):

import paramiko
from sshtunnel import SSHTunnelForwarder

with SSHTunnelForwarder(
    intermediate = {
        ("B_host", 22),
        ssh_username = "B_username",
        ssh_password = "B_password")},
    remote = {
        ("C_host", 12345),
        ssh_username = "C_username",
        ssh_password = "C_password")},
    remote_bind_address=("D_host_rds", 3306),
    local_bind_address=("0.0.0.0", 3307)) as server:

    conn = MySQLdb.connect(
        user = "D_username",
        passwd = "D_password",
        db = "my_database",
        host = "127.0.0.1",
        port = 3307)

tl;dr:如何在Python中通过两次ssh跳转来转发端口？

我想到了。它与 ssh 配置设置和 SSHTunnelForwarder 上下文管理器的组合一起使用ssh隧道 https://sshtunnel.readthedocs.io/en/latest/图书馆。

使用以下模型和命名约定 https://i.stack.imgur.com/94xHh.png:

[A: local host] -> [B: jump host] -> [C: target host] => [D: RDS MySQL host]

我将 ~/.ssh/config 设置为通过 B 从 A 到 C：

Host C_ssh_shortcut
    HostName C_host
    User C_user
    Port 22
    ForwardAgent yes
    ProxyCommand ssh B_user@B_host -W %h:%p

我将用于登录 B 和 C 的密钥添加到我的 ssh-agent 中：

ssh-add

最后我设置了 SSHTunnelForwarder：

import sqlalchemy
from sshtunnel import SSHTunnelForwarder

with SSHTunnelForwarder(
    "C_ssh_shortcut",                     # The SSHTunnelForwarder "ssh_address_or_host" argument, which takes care of bypassing B through the ProxyCommand set up in ~/.ssh/config
    remote_bind_address=(D_host, 3306),   # Points to your desired destination, ie. database host on 3306, which is the MySQL port
    local_bind_address=('', 1111)         # Gives a local way to access this host and port on your machine. '' is localhost / 127.0.0.1, 1111 is an unused port
) as server:
    connection_string = "mysql+pymysql://D_user:D_password@localhost:1111/D_dbname"  # note that D_host and D_port were replaced by the host and port defined in "local_bind_address"
    engine = sqlalchemy.create_engine(connection_string)
    # do your thing

从这里，我可以像往常一样使用我的引擎与我的数据库进行交互。