Securely Access Localhost Database via SSH on Ubuntu

This article provides a straightforward guide on how to use SSH port forwarding (also known as SSH tunneling) on Ubuntu Linux to securely access a remote database that is restricted to localhost. By creating a secure, encrypted tunnel, you can connect your local database administration tools directly to a remote database without exposing database ports to the public internet.

Why Use SSH Tunneling?

For security reasons, production databases (such as MySQL, PostgreSQL, or MongoDB) are often configured to only listen on localhost (127.0.0.1). This prevents external actors from attempting to exploit the database port. An SSH tunnel routes your local database traffic through a secure SSH connection, making the remote server believe the connection is originating locally.

Step 1: Run the SSH Tunneling Command

Open your terminal on Ubuntu and run the following SSH command:

ssh -L [LOCAL_PORT]:localhost:[REMOTE_DB_PORT] [SSH_USER]@[REMOTE_SERVER_IP] -N

Parameter Breakdown: * -L: Specifies local port forwarding. * [LOCAL_PORT]: The port on your local machine you want to use to access the database (e.g., 3307 or 5433). Using a custom port prevents conflicts with databases running locally on your machine. * localhost: Tells the remote server to forward the traffic to its own localhost loopback. * [REMOTE_DB_PORT]: The port the database is listening on on the remote server (e.g., 3306 for MySQL, 5432 for PostgreSQL). * [SSH_USER]@[REMOTE_SERVER_IP]: Your SSH login credentials for the remote Ubuntu server. * -N: Tells SSH not to execute a remote command. This is ideal for port forwarding as it keeps the connection open without launching an interactive shell.

Step 2: Common Examples

For MySQL/MariaDB (Default port 3306): To forward your local port 3307 to the remote MySQL port 3306:

ssh -L 3307:localhost:3306 admin@192.168.1.50 -N

For PostgreSQL (Default port 5432): To forward your local port 5433 to the remote PostgreSQL port 5432:

ssh -L 5433:localhost:5432 admin@192.168.1.50 -N

Step 3: Connect to the Database

Keep the terminal window running the SSH command open. While the tunnel is active, configure your local database GUI (such as DBeaver, pgAdmin, or VS Code extensions) or CLI tool using the following connection parameters:

For example, to connect via the MySQL command line tool from your local terminal:

mysql -h 127.0.0.1 -P 3307 -u db_user -p

Step 4: Close the Connection

Once you are done managing your remote database, return to the terminal window where the SSH tunnel is running and press CTRL + C to close the tunnel and terminate the secure session.