Connect to remote database through gateway with Ruby

Question

My target oracle database server exists beyond a firewall. I use an ssh tunnel to connect to a server beyond the firewall, call it friendly-server. Then from my localhost, I use DBI to connect to the target database which exists on the another server, call it target-dbserver. Similar question recommends using command line below.

This is the ssh command i use to set up the tunnel:

ssh -nN -L 1520:target-dbserver:1520 friendly-server

I want to implement this in Ruby so I am using the net-ssh-gateway gem. I'm probably just not understanding how ssh tunneling works because I do not see how once I establish the gateway and then the subsequent ssh to friendly-server, I'm not seeing how to then establish the DBI connection to the db on target-dbserver.

# Set up the gateway. The gem will find an unused port.
gateway = Net::SSH::Gateway.new('friendly-server', 'user')
# Establish ssh connection
gateway.ssh('friendly-server', 'user') do |ssh|
# now how to make DBI connection to target-dbserver ?
end

Tried this which fails because how does the DBI call know to use the tunnel?

gateway = Net::SSH::Gateway.new('friendly-server', 'user')
gateway.ssh('friendly-server', 'user') do |ssh|
  db = DBI.connect('target server connection string', 'user','password')
end

Using the gateway.open method. This seems logical to me but it always hangs when I try to connect via DBI to the target db via the localhost (gateway). I think maybe its because the .open method establishes an ssh connection to the targetdb which I don't want. I want the only contact with the dbtarget to be with the DBI connection.

# open gateway from localhost to bastion
gateway = Net::SSH::Gateway.new('friendly-server', 'user')
# Opens a new port on the local host and forwards it to the given host/port
# via the gateway host.
gateway.open(targetdb, 1520) do |port|
  db = DBI.connect(localhost, port, sid)
end

UPDATE

Couldn't get the gateway or net-ssh forward_local to work for me. Frustrated I opted to fork the ssh tunnel. Not ideal but works for the time being. If the local port is taken then I'd like to trap that error but don't know how with this method.

tunnel = fork do
  exec 'ssh -nN -L 1520:targetdb:1520 friendly-server'
end
Process.detach(tunnel)
#do stuff
Process.kill('HUP', tunnel) if tunnel

i assume, that within that block, you write the normal connection code for your database. — phoet, Mar 13 '13 at 17:01
yes though I'm not sure how to do that within this context. When not using a tunnel `db = DBI.connect(connection_string, user, password)` — Todd, Mar 13 '13 at 17:15

score 1 · Accepted Answer · answered Mar 18 '13 at 16:24

1

My solution is to fork an ssh command and then to kill that ssh process when I've finished.

# obj_hash contains remote db details and a flag for when a tunnel is needed
local_port = 3535
if obj_hash[:tunnel]
  tunnel = fork do
    exec "ssh -nN -L #{local_port}:#{obj_hash[:server]}:#{obj_hash[:port]} #{obj_hash[:tunnel]} 2> /dev/null; "
  end
  Process.detach(tunnel)
end
# do stuff
Process.kill('HUP', tunnel) if tunnel

At present I do not know how to check if the tunnel is already up on the local_port so not a perfect solution.

answered Mar 18 '13 at 16:24

Todd

652
2
19
37

I created a class to manage my ssh tunnels. Using nmap/parser I was able to detect which ports were already in use when the class initialized. Then as tunnels are added/removed I keep track of that by updating a data structure. The this structure includes the PID so when the need comes to drop a tunnel it's easy to kill the process. – Todd Dec 10 '13 at 18:20

Connect to remote database through gateway with Ruby

1 Answers1