SSH Tunnel to AWS RDS in Private Subnet

SSH Tunnel to RDS

We needed to connect to an RDS DB in the Amazon cloud that is not publicly accessible. Normally we  would connect through an ec2 instance but as this is time consuming and gets quite annoying we looked  at setting this up so we could simply connect via

sqlplus user/pass@tns

How to implement

1. Set up the SSH Tunnel

ssh -N -L 1521:your.rds.endpoint.rds.amazonaws.com:1521 sshuser@yourserver.com

 -N   
only set up the tunnel

 -L   
set up the forwarding

 1521
that first number is the port on your local machine

your.rds.endpoint.amazonaws.com
The name of the rds endpoint

1521
the port on the remote computer

sshuser@yourserver.com
how you log in to your ec2 instance

2. Set up a new tns entry for the tunnel connection

ORACLE-AWS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

2. Use the SSH Tunnel

sqlplus user/pass@ORACLE-AWS

This lets you connect to the remote rds instance. Note that you have to use the host here 127.0.0.1 explicitly and that it is not the host you set up earlier. This is because it is now forwarding all of the requests. That’s all.

To be clear on how the ports work, here is another example

ssh -N -L 1234:your.rds.endpoint.rds.amazonaws.com:1521 sshuser@yourserver.com

ORACLE-AWS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

This states that forward from port 1234 on my computer to port 1521 on the remote instance. 

Bash FTP Script Cronjob

Bash FTP Scripting

Creating a Bash FTP Script and installing as a cronjob can be a great way to move files around at scheduled intervals .

Want to move files around at set intervals? Even to internal servers sometimes the best way to accomplish this is with a bash ftp script. The below example of a bash script logs onto a remote FTP server and checks for messages before downloading them to the /home/user/ftp_in folder. It then checks the /home/user/ftp_out folder and transmits any files found there to the remote FTP server and then finally moves any sent files to /home/user/ftp_sent for archiving purposes.

#!/bin/bash
#pull and push from FTP SERVER
#change to the ftp_in directory
cd /home/user/ftp_in
ftp -niv ftp.address.to.connect.to << FTP_COMMAND
user username password
cd To_pvlogistics
mget *
mdel *
bye
FTP_COMMAND

#Change to the ftp_out directory
cd /home/user/ftp_out
ftp -niv ftp.address.to.connect.to << FTP_COMMAND
user username password
mput *.*
bye
FTP_COMMAND
mv * /home/user/ftp_sent

Saving an example of this script in a file called /home/user/ftp-script.sh it can be installed as a cronjob to run every X minutes as below. In this example it is set to run every 10 minutes.
*/10 * * * * /home/user/ftp-script.sh

FTP man pages for further reading and information on standard FTP commands and usage examples:

FTP

File Transfer Protocol

Syntax
      FTP [-options] [-s:filename] [-w:buffer] [host]

Key   
   -s:filename   Run a text file containing FTP commands.

   host          Host name or IP address of the remote host.

   -g            Disable filename wildcards.

   -n            No auto-login.

   -i            No interactive prompts during ftp.

   -v            Hide remote server responses.

   -w:buffer     Set buffer size to buffer
                 (default=4096)

   -d            Debug
 
   -a            Use any local interface when binding data connection.

http://ss64.com/bash/ftp.html