Shin Code log

Logs of solution for code
October 23, 2024

Connect to sql server through bastion from LOCAL

First, you need to establish an SSH tunnel the SQL server. On the client machine, run:

ssh -fNg -L 5433:sql_server_address:3306 user@bastion_address

-f Requests ssh to go to background just before command execution. This is useful if ssh is going to ask for passwords or passphrases, but the user wants it in the background. This implies -n. The recommended way to start X11 programs at a remote site is with something like ssh -f host xterm.
-N Do not execute a remote command. This is useful for just forwarding ports. Refer to the description of SessionType in sshconfig(5) for details.
-g Allows remote hosts to connect to local forwarded ports. If used on a multiplexed connection, then this option must be specified on the master process.
-L local
socket:host:hostport Specifies that connections to the given TCP port or Unix socket on the local (client) host are to be forwarded to the given host and port, or Unix socket, on the remote side. This works by allocating a socket to listen to either a TCP port on the
local side, optionally bound to the specified bindaddress, or to a Unix socket. Whenever a connection is made to the local port or socket, the connection is forwarded over the secure channel, and a connection is made to either host port hostport,
or the Unix socket remote
socket, from the remote machine.

         Port forwardings can also be specified in the configuration file.  Only the superuser can forward privileged ports.  IPv6 addresses can be specified by enclosing the address in square brackets.

         By default, the local port is bound in accordance with the GatewayPorts setting.  However, an explicit bind_address may be used to bind the connection to a specific address.  The bind_address of “localhost” indicates that the listening port be bound
         for local use only, while an empty address or ‘*’ indicates that the port should be available from all interfaces.

That will establish an SSH tunnel to the bastion_address PC. Any connections to localhost port 5433 will get sent through the tunnel to the remote host on port 5433.

Then just configure your database.yml like you would for a local connection, but specify the forwarded port 5433:

canine:
adapater: mysql2 | postgres | something
database: shinshin
username: bowser
password: *secret*
port: 5433