使用跳转主机和远程数据库进行 SSH 隧道转发 [英] SSH tunnel forwarding with jump host and remote database

问题描述

我在 Amazon RDS(D")上托管了一个远程 MySQL 数据库.出于安全目的，它只能通过远程服务器(C")访问.C 可通过跳转主机B"通过 ssh 访问.我需要一个双 ssh 隧道然后访问远程 SQL 主机.

I have a remote MySQL database hosted on Amazon RDS ("D"). For security purposes, it is only accessible through a remote server ("C"). C is accessible via ssh through a jump host "B". I need a double ssh tunnel to then access a remote SQL host.

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

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

I would like to access D through Python, using paramiko and/or sshtunnel. All of the information I can find involves:

• 一个 ssh 隧道和一个远程 SQL 主机(例如 A -> C => D，没有跳转主机)
  • 首先使用 python 中的 mysqldb 进行 ssh
  • 通过 ssh 连接 python mysql
  • 使用 python 通过 ssh 隧道连接到远程 Postgresql 数据库
  • Paramiko:围绕 NAT 路由器的端口转发
  • 与 Paramiko 的嵌套 SSH 会话

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

  So far, I'm using paramiko with a proxy command to get from A to C. I can access D by executing a command on C, but not by connecting with mysqldb or sqlalchemy (my ultimate goal).

  我当前的代码:

  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
  

  我正在寻找这样的东西(从 sshtunnel 文档中的示例 2 修改):

  I'm looking for something like this (modified from example 2 in the sshtunnel docs):

  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 跳转来转发端口?

  tl;dr: How do I forward a port through two ssh jumps in Python?

  推荐答案

  我想通了.它结合了 ssh 配置设置和 sshtunnel 库中的 SSHTunnelForwarder 上下文管理器.

  I figured it out. It works with a combination of ssh config settings and the SSHTunnelForwarder context manager from the sshtunnel library.

  使用以下模型和命名约定:

  [A:本地主机] ->[B: 跳转主机] ->[C:目标主机] =>[D: RDS MySQL 主机]

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

  I set up my ~/.ssh/config to get from A to C through B:

  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:

  I added the key/keys I used to log in to B and C to my ssh-agent:

  ssh-add
  

  最后我设置了 SSHTunnelForwarder:

  And finally I set up 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
  

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

  From here, I am able to use my engine as usual to interact with my database.

  这篇关于使用跳转主机和远程数据库进行 SSH 隧道转发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！