Skip to main content

Production Database Access Guide

Overview

This guide provides two methods for accessing the production PostgreSQL database through the bastion host:

  1. DBeaver with SSH Tunnel - GUI-based database client with built-in SSH tunneling
  2. Command Line with Port Forwarding - Direct command-line access using SSH port forwarding

Both methods ensure secure access to the production database by routing connections through the authorized bastion host.


Method 1: DBeaver with SSH Tunnel [PROD only]

Prerequisites

  • DBeaver Community or Pro edition installed
  • Access to bastion host credentials
  • Database connection credentials

Step 1: Download and Install DBeaver

  1. Visit the DBeaver website: Navigate to https://dbeaver.io/download/
  2. Choose the appropriate version: Select the DBeaver version compatible with your operating system:
    • Windows: Download the .exe installer
    • macOS: Download the .dmg file
    • Linux: Download the appropriate package (.deb, .rpm, etc.)
  4. Install DBeaver:
    • Windows: Double-click the .exe file and follow installation wizard
    • macOS: Open the .dmg file and drag DBeaver to Applications folder
    • Linux: Use package manager (e.g., dpkg -i for .deb, rpm -i for .rpm)

Step 2: Configure Database Connection

  1. Launch DBeaver and open the Database Navigator view
  2. Create New Connection:
    • Click "New Database Connection" button in toolbar
    • Or right-click in Database Navigator → "New Connection"
  4. Select Database Type: Choose PostgreSQL

Step 3: Configure SSH Tunnel

  1. Navigate to SSH Tab in the connection dialog
  2. Enable SSH Tunnel: Check "Use SSH Tunnel" checkbox
Host/IP:     bastion.bynry.com
Username:    bastion-ssh-user
Password:    bastion-user
Port:        22 (default)
  1. Test SSH Connection: Click "Test tunnel configuration" button
  2. Verify Success: Ensure tunnel test passes before proceeding

Step 4: Configure Database Connection

  1. Return to Main Tab in the connection dialog
Host:        prod-smart360-db.cz00e0kwcgdr.us-east-1.rds.amazonaws.com
Port:        5432
Database:    ax360v1 (example)
Username:    <user-from-shared-sheet>
Password:    <password>
  1. Driver: PostgreSQL driver (auto-detected)
  2. Test Connection: Click "Test Connection" to verify all settings
  3. Save Connection: Click "Finish" to save the configuration

Step 5: Connect to Database

  1. Locate Connection in Database Navigator
  2. Connect: Double-click connection or right-click → "Connect"
  3. Verify Connection: Expand database tree to confirm successful connection



Method 2: Command Line with Port Forwarding [Prod only]

Prerequisites

  • SSH client with sshpass utility
  • PostgreSQL client (psql)
  • Terminal/command line access

Understanding SSH Port Forwarding

SSH port forwarding (also called SSH tunneling) creates a secure encrypted connection between your local machine and the remote database through the bastion host. Here's how it works:

[Your Machine] ←→ [Bastion Host] ←→ [RDS Database]

localhost:5432    bastion.bynry.com    prod-smart360-db:5432

The Process:

  1. Local Port Binding: SSH binds to a local port (5432) on your machine
  2. Secure Tunnel: Creates encrypted tunnel to bastion host
  3. Remote Forwarding: Bastion host forwards traffic to RDS endpoint
  4. Transparent Connection: Your local applications connect to localhost:5432 as if the database were local

Step 1: Establish SSH Port Forward

Execute the following command to create the SSH tunnel:

bash

sshpass -p 'bastion-user' ssh -v -N -L 5432:prod-smart360-db.cz00e0kwcgdr.us-east-1.rds.amazonaws.com:5432 bastion-ssh-user@bastion.bynry.com


windows:

ssh -v -N -L 5432:prod-smart360-db.cz00e0kwcgdr.us-east-1.rds.amazonaws.com:5432 bastion-ssh-user@bastion.bynry.com

Later when password is prompted : use "bastion-user"


Command Breakdown:

  • sshpass -p 'bastion-user' - Provides password for SSH authentication
  • ssh -v - Verbose output for debugging
  • -N - Do not execute remote commands (tunnel only)
  • -L 5432:prod-smart360-db.cz00e0kwcgdr.us-east-1.rds.amazonaws.com:5432 - Local port forwarding
    • Local port: 5432
    • Remote host: prod-smart360-db.cz00e0kwcgdr.us-east-1.rds.amazonaws.com
    • Remote port: 5432
  • bastion-ssh-user@bastion.bynry.com - SSH connection to bastion host


Step 2: Connect to Database

Open a new terminal window (keep the SSH tunnel running) and execute:

bash

psql -h localhost -p 5432 -d ax360v1 -U authuser

Note: The original command shows port 5435, but since we're forwarding to local port 5432, use port 5432 unless you have a specific reason to use a different local port.

Connection Parameters:

  • -h localhost - Connect to local forwarded port
  • -p 5432 - Local port (matches the forwarded port)
  • -d ax360v1 - Database name
  • -U authuser - Database username

Step 3: Working with the Connection

Once connected, use the same DB URL (localhost) with the port you forwarded on local in your code.

Step 4: Closing the Connection

  1. Exit psql: Type \q or exit
  2. Stop SSH tunnel: Return to the terminal with SSH tunnel and press Ctrl+C



Port Forwarding Deep Dive

How SSH Port Forwarding Works

Local Port Forwarding (-L) creates a tunnel where:

Local Machine              Bastion Host                RDS Database

localhost:5432 ←→ SSH Tunnel ←→ bastion.bynry.com ←→ prod-smart360-db:5432

The Flow:

  1. Application Request: Your psql client connects to localhost:5432
  2. SSH Interception: SSH client intercepts the connection
  3. Tunnel Transport: Data is encrypted and sent through SSH tunnel to bastion
  4. Remote Forward: Bastion host forwards the connection to RDS endpoint
  5. Response Path: Database responses follow the same path in reverse

Security Benefits:

  • Encryption: All data is encrypted through the SSH tunnel
  • Access Control: Must authenticate to bastion host first
  • Network Isolation: Database is not directly accessible from internet
  • Audit Trail: All connections go through monitored bastion host

Alternative Port Forwarding Options

If port 5432 is already in use locally, you can use a different local port:

bash

# Forward to local port 5433

sshpass -p 'bastion-user' ssh -v -N -L 5433:prod-smart360-db.cz00e0kwcgdr.us-east-1.rds.amazonaws.com:5432 bastion-ssh-user@bastion.bynry.com

# Connect using the alternate port

psql -h localhost -p 5433 -d ax360v1 -U authuser



Troubleshooting

Common Issues and Solutions

DBeaver Connection Issues

Problem: SSH tunnel test fails

  • Solution: Verify bastion host credentials and network connectivity
  • Check: Ensure bastion.bynry.com is accessible from your network

Problem: Database connection fails after successful SSH tunnel

  • Solution: Verify RDS endpoint and database credentials
  • Check: Confirm database name and username are correct

Command Line Issues

Problem: sshpass command not found (common)

  • Solution: Install sshpass utility
    • Ubuntu/Debian: sudo apt-get install sshpass
    • macOS: brew install sshpass
    • RHEL/CentOS: sudo yum install sshpass

Problem: psql command not found

  • Solution: Install PostgreSQL client
    • Ubuntu/Debian: sudo apt-get install postgresql-client
    • macOS: brew install postgresql
    • Windows: Download from PostgreSQL official site

Problem: Port already in use

  • Solution: Use different local port in SSH command
  • Alternative: Kill process using port 5432: sudo lsof -ti:5432 | xargs kill -9

Problem: SSH connection hangs

  • Solution: Check network connectivity and firewall settings
  • Debug: Use -vvv flag for detailed SSH debugging

Security Reminders

  1. Keep Tunnels Short-lived: Close SSH tunnels when not in use
  2. Monitor Connections: Be aware of active database connections
  3. Protect Credentials: Never commit passwords to version control
  4. Use Strong Authentication: Consider SSH key-based authentication for enhanced security
  5. Audit Access: Regular review of who has bastion host access



Quick Reference

DBeaver Quick Setup

SSH Host: bastion.bynry.com
SSH User: bastion-ssh-user
SSH Pass: bastion-user

DB Host: prod-smart360-db.cz00e0kwcgdr.us-east-1.rds.amazonaws.com
DB Port: 5432
DB Name: ax360v1
DB User: authuser

Command Line Quick Connect

bash

# Terminal 1 - SSH Tunnel
sshpass -p 'bastion-user' ssh -v -N -L 5432:prod-smart360-db.cz00e0kwcgdr.us-east-1.rds.amazonaws.com:5432 bastion-ssh-user@bastion.bynry.com

# Terminal 2 - Database Connection
psql -h localhost -p 5432 -d ax360v1 -U authuser



Document Version: 1.0
Last Updated: June 2025
Support: DevOps Team


No Comments
Back to top