Missing Tls in Flask with Cockroachdb

A Flask application that connects to CockroachDB without TLS exposes database credentials and query data in transit. CockroachDB supports TLS for client-to-node encryption, and when a Flask app uses a connection string or driver that does not enforce encrypted connections, credentials, and potentially sensitive query results traverse the network unencrypted.

Consider a typical Flask configuration that constructs a CockroachDB connection URI without specifying certificate requirements or forcing encryption:

app.config['COCKROACH_URI'] = 'postgresql://myuser:secretpass@cockroachdb-host:26257/defaultdb?sslmode=disable'

Using sslmode=disable (or omitting sslmode in some drivers) disables encryption. An attacker on the same network or path can observe the username and password and any data returned from tables containing personal or financial information. This maps directly to the Data Exposure checks in middleBrick’s 12 security checks and can contribute to a poor security risk score (e.g., D or E grade) because credentials and potentially regulated data are exposed in cleartext.

Additionally, without TLS, there is no server identity verification. The application is vulnerable to man-in-the-middle attacks where an attacker presents a fake certificate or simply intercepts traffic. middleBrick’s Encryption check would flag this missing encryption, and the findings would include remediation guidance to use TLS with proper certificate validation.

In a typical CI/CD workflow, the GitHub Action can be configured to fail a build if a submitted endpoint exhibits such insecure configurations. Since middleBrick scans unauthenticated attack surfaces, it can detect the use of insecure connection strings and report the issue without requiring access to source code repositories.

To remediate, enforce TLS by configuring the connection to require and validate the server certificate. CockroachDB typically provides a CA certificate that signs server certificates. The Flask app should use that CA to verify the server identity and, if needed, present client certificates.

Below is a secure example using psycopg2 (the common PostgreSQL wire protocol driver for CockroachDB) with TLS enabled:

import psycopg2
from flask import Flask, jsonify

app = Flask(__name__)

app.config['COCKROACH_URI'] = (
    'postgresql://myuser:secretpass@cockroachdb-host:26257/defaultdb'
    '?sslmode=verify-full'
    '&sslrootcert=/path/to/ca.crt'
    '&sslcert=/path/to/client.crt'
    '&sslkey=/path/to/client.key'
)

@app.route('/users/')
def get_user(user_id):
    conn = None
    try:
        conn = psycopg2.connect(app.config['COCKROACH_URI'])
        cur = conn.cursor()
        cur.execute('SELECT id, name, email FROM users WHERE id = %s;', (user_id,))
        row = cur.fetchone()
        if row:
            return jsonify({'id': row[0], 'name': row[1], 'email': row[2]})
        return jsonify({'error': 'not found'}), 404
    except Exception as e:
        return jsonify({'error': str(e)}), 500
    finally:
        if conn:
            conn.close()

Key points in this configuration:

• sslmode=verify-full ensures the server certificate is validated against the provided CA and that the hostname matches the certificate.
• sslrootcert points to the CA certificate that signed CockroachDB’s server certificate.
• sslcert and sslkey are optional but recommended for mutual TLS, ensuring the client proves its identity.

If you are using an ORM like SQLAlchemy with the psycopg2 backend, the equivalent connection string would be:

app.config['SQLALCHEMY_DATABASE_URI'] = (
    'postgresql+psycopg2://myuser:secretpass@cockroachdb-host:26257/defaultdb'
    '?sslmode=verify-full'
    '&sslrootcert=/path/to/ca.crt'
)

For environments without client certificates, at minimum use sslmode=require and provide sslrootcert to prevent cleartext transmission and server impersonation:

app.config['COCKROACH_URI'] = (
    'postgresql://myuser:secretpass@cockroachdb-host:26257/defaultdb'
    '?sslmode=require'
    '&sslrootcert=/path/to/ca.crt'
)

middleBrick’s detailed findings would highlight the absence of these TLS settings and link them to relevant compliance frameworks such as OWASP API Top 10 and PCI-DSS requirements for encryption in transit. The remediation guidance provided would reference the exact configuration changes shown above.