Table of contents¶

[[TOC]]

Introduction¶

This document provides basic info on using SSL with various MariaDB instances used in the FTMO infrastructure. The same information is scaterred across several documents, so the purpose of this one is to be a single source of information about using SSL.

These MariaDB instances require mandatory SSL: * DB1 * DB2 * SkySQL instances * Old beta_ziskejucet at beta.db.fftrader.cz

Last change 10.12.2024, gnd

Using SSL to connect to DB1 / DB2 / Old beta¶

Certificates for these servers are emitted by Lets Encrypt. The Lets Encrypt CA's certificate is included in all modern operating systems. Therefore there is no need to download and install any certificates in order to use SSL for a connection.

Command line example in Linux¶

Just use mariadb-client like this:

mysql -h db1.ftmo.com -u USER -p --ssl-verify-server-cert

PHP example using PDO¶

<?php

$options = array(
    // In PDO we need to provide either the CA certificate or a directory where it is stored
    / This directory is '/etc/ssl/certs' on most Linux systems
    PDO::MYSQL_ATTR_SSL_CAPATH => '/etc/ssl/certs',
    // Never turn off server certificate verification
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
);

try {
    $conn = new PDO("mysql:host=db1.ftmo.com;port=3306;dbname=ziskejucet","USER","PASS",$options);

    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully"; 
    var_dump($conn->query("SHOW STATUS LIKE 'Ssl_cipher';")->fetchAll());
    var_dump($conn->query("SHOW STATUS LIKE 'Ssl_version';")->fetchAll());
    $conn = null;
}
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
}
?>

In some rare cases you might need to download the root certificate for PDO, because the CAPATH is missing, or because its complicated.

In such a case: * Download the "pem" version of the Lets Encrypt "ISRG Root X1" certificate from the url: https://letsencrypt.org/certificates/ * Place the certificate into a config directory in your project and make it read-only. * Connect to the database like this:

<?php

$options = array(
    // Lets Encrypt root CA certificate
    PDO::MYSQL_ATTR_SSL_CA => '/path/to/isrgrootx1.pem',
    // Never turn off server certificate verification
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
);

try {
    $conn = new PDO("mysql:host=db1.ftmo.com;port=3306;dbname=ziskejucet","USER","PASS",$options);

    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully"; 
    var_dump($conn->query("SHOW STATUS LIKE 'Ssl_cipher';")->fetchAll());
    var_dump($conn->query("SHOW STATUS LIKE 'Ssl_version';")->fetchAll());
    $conn = null;
}
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
}
?>

Please, make sure you always verify the server certificate. If your connection works only without verification something is wrong and contact the DevOps Team. Without server verification it is possible to attack such a connection and do a MITM attack.

PHP example using mysqli¶

<?php

$mysqli = mysqli_init();

// NEVER TURN OFF SERVER CERTIFICATE VERIFICATION
$mysqli->options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);

// Use MYSQLI_CLIENT_SSL to start a SSL connection
$mysqli->real_connect("db1.ftmo.com", "USER", "PASS", "ziskejucet", 3306, NULL, MYSQLI_CLIENT_SSL);

// show status
$result = $mysqli->query("SHOW STATUS");
$rows = $result->fetch_all(MYSQLI_ASSOC);

// print ssl status
foreach ($rows as $row) {
    if ($row["Variable_name"] == "Ssl_version") {
        print("\nSSL version: " . $row["Value"] . "\n");
    } 
    if ($row["Variable_name"] == "Ssl_cipher") {
        print("SSL cipher used: " . $row["Value"]);
    }
}

?>

Please, make sure you always verify the server certificate. if your connection works only without verification something is wrong and contact the DevOps Team. Without server verification it is possible to attack such a connection and do a MITM attack.

Python exampnle using SQLAlchemy¶

connection_string = (
    f"mysql+pymysql://{user}:{password}@{host}:{port}/{database}"
    "?ssl=true"  # Enforce SSL
    "&ssl_verify_identity=true"  # Mandatory: Enforces server identity check
)

Windows example using HeidiSQL¶

The connection requires SSL.

Open up HeidiSQL and configure the connection. In tab Settings set:

Network type: MariaDB or MySQL (TCP/IP)
Library: libmariadb.dll
Hostname / IP: db1.ftmo.com / db2.ftmo.com / beta.db.fftrader.cz (choose one)
User: your user
Password: your pass
Port: 3306

In tab SSL:

Enable "Use SSL"

Save the connection and connect.

Using SSL to connect to SkySQL instances¶

Please refer to this document: https://gitlab.fftrader.cz/devops/docs/-/blob/main/skysql-notes.md?ref_type=heads#how-to-connect-to-skysql