如何通过SSH连接远程PostgreSQL数据库适配Flask-SQLAlchemy

Since your PostgreSQL instance is locked down to accept only local connections, we need to route our Flask/SQLAlchemy traffic through an SSH tunnel first, then adjust the database connection URIs to use this tunnel. Let’s break this down for both SQLAlchemy Core and Flask-SQLAlchemy ORM approaches.

Step 1: Set Up the SSH Tunnel #

First, create an SSH tunnel that forwards a local port on your machine to the PostgreSQL port (default 5432) on your remote server. Run this command in your terminal:

ssh -L 5432:localhost:5432 your_ssh_username@your_server_public_ip

• This maps your local 5432 port to the remote server’s 5432 port (where PostgreSQL listens locally).
• Keep this terminal window open while your Flask app runs—closing it will drop the tunnel.

Step 2: Modify SQLAlchemy Core Code #

Instead of pointing directly to the remote PostgreSQL instance, connect to the local end of the SSH tunnel. Update your engine creation code like this:

from sqlalchemy import create_engine

# Replace with your actual PostgreSQL credentials from the remote server
db_user = "your_postgres_user"
db_password = "your_postgres_password"
db_name = "your_database_name"

# Connect to the local tunnel port instead of the remote server
engine = create_engine(f"postgresql://{db_user}:{db_password}@localhost:5432/{db_name}")

Note: I swapped postgres:// with postgresql://—it’s the updated, preferred URL scheme for modern SQLAlchemy versions.

Step 3: Modify Flask-SQLAlchemy ORM Code #

Similarly, adjust the database URI in your Flask app configuration to use the local tunnel:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# Update the URI to point to the local tunnel endpoint
app.config['SQLALCHEMY_DATABASE_URI'] = "postgresql://your_postgres_user:your_postgres_password@localhost:5432/your_database_name"
# Optional but recommended: Disable modification tracking to save resources
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

Bonus: Auto-Start SSH Tunnel in Code (Optional) #

If you don’t want to manually run the SSH command every time, use the sshtunnel Python library to create the tunnel programmatically. Install it first:

pip install sshtunnel

Then wrap your database setup with the tunnel:

For SQLAlchemy Core: #

from sqlalchemy import create_engine
from sshtunnel import SSHTunnelForwarder

with SSHTunnelForwarder(
    ('your_server_public_ip', 22),
    ssh_username='your_ssh_username',
    ssh_password='your_ssh_password', # Use ssh_pkey instead for key-based auth
    remote_bind_address=('localhost', 5432)
) as tunnel:
    engine = create_engine(f"postgresql://your_postgres_user:your_postgres_password@localhost:{tunnel.local_bind_port}/your_database_name")
    # Use the engine for your database operations as usual

For Flask-SQLAlchemy: #

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sshtunnel import SSHTunnelForwarder

# Initialize and start the tunnel
tunnel = SSHTunnelForwarder(
    ('your_server_public_ip', 22),
    ssh_username='your_ssh_username',
    ssh_password='your_ssh_password',
    remote_bind_address=('localhost', 5432)
)
tunnel.start()

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = f"postgresql://your_postgres_user:your_postgres_password@localhost:{tunnel.local_bind_port}/your_database_name"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

# Stop the tunnel automatically when the app exits
@app.teardown_appcontext
def close_tunnel(exception=None):
    tunnel.stop()

Just double-check that your SSH user has server access, and your PostgreSQL credentials match what you’d use if connecting locally on the remote server.

内容的提问来源于stack exchange，提问作者Afeez Aziz