Use SSL/TLS within PDO declaration

[JeSappelleRoot]

Hi there,

our dev team got some issues when trying to initiate the PDO connection with SSL/TLS support The main documentation doesn't mentions this options, so we tried several configurations Here some details of our environment : Database instance : MariaDB 10.3, with SSL/TLS v2 Framework : Laravel 8 PHP version : 8.0

In our Laravel project, we have a folder config/database.php with a mysql array with the following content :

        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

Our first issue was : SQLSTATE[HY000] [9002] SSL connection is required. Please specify SSL options and retry.

Then, we tried several things at PDO declaration :

$options = [ config('database.connections.mysql.options'), ];
$pdo = new PDO('mysql:host=' . env('DB_HOST') . ':' . env('DB_PORT'), env('DB_USERNAME'), env('DB_PASSWORD'), Arr::flatten($options));

$pdo = new PDO('mysql:host=' . env('DB_HOST') . ':' . env('DB_PORT'), env('DB_USERNAME'), env('DB_PASSWORD'), array(PDO::MYSQL_ATTR_SSL_CA => '/etc/ssl/certs/Baltimore_CyberTrust_Root.pem');

Then, we finally got the following error message : SQLSTATE[HY000] [2002] {"exception":"[object] (PDOException(code: 2002): SQLSTATE[HY000] [2002] at /xxxxxx/xxxx/xxxxxx/xxxxx/xxxxx/xxxxxx.php:40)

Some help will be appreciated Best,

Joseph

[BenMorel]

Hi, I'm sorry but this is a PDO issue, unrelated to this project, I won't be able to help. Maybe try StackOverflow?

[juhasev]

If you are using Laravel and you have your database connection already working simply do this and you are done:

     $pdo = DB::connection()->getPdo();
     GeometryEngineRegistry::set(new PDOEngine($pdo));

[JeSappelleRoot]

Sorry, I didn't follow well this issue Here our solution that we implemented

In .env

POLYGON_MANAGER_REMOTE_DB=true

In config/polygonmanager.php

<?php

return [
    'remote_db' => env('POLYGON_MANAGER_REMOTE_DB', false)
];

In app/Services/PolygonManager.php

    public function __construct(array $origin, array $destination)
    {
        if (config('polygonManager.remote_db')) {
            $options = [
                PDO::MYSQL_ATTR_SSL_CA => true,
                PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
            ];

            $pdo = new PDO('mysql:host=' . config('database.connections.mysql.host') . ':' . config('database.connections.mysql.port'), config('database.connections.mysql.username'), config('database.connections.mysql.password'), $options);

        } else {
            $options = config('database.connections.mysql.options');

            $pdo = new PDO('mysql:host=' . config('database.connections.mysql.host') . ':' . config('database.connections.mysql.port'), config('database.connections.mysql.username'), config('database.connections.mysql.password'), $options);
        }

        GeometryEngineRegistry::set(new PDOEngine($pdo));

    }

[JeSappelleRoot]

Unfortunately we still have randomly some error Brick\Geo\Engine\GeometryEngineRegistry::set(): SSL: Connection reset by peer Any tips ?

[JeSappelleRoot]

If you are using Laravel and you have your database connection already working simply do this and you are done:

     $pdo = DB::connection()->getPdo();
     GeometryEngineRegistry::set(new PDOEngine($pdo));

@juhasev, we are trying your solution right now Was unsuccessful at the begining, that's why we implemented another way to handle database connection.

I'll update this issue if necessary

[JeSappelleRoot]

Quick update on our end Dev team wrote a quick PHPUnit test, that creating 100 connections with basic for loop.

Here an interesting feedback that could help people about performances

Using PDO constructor is slower than reusing existing database PDO

$options = [
                PDO::MYSQL_ATTR_SSL_CA => true,
                PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
            ];

            $pdo = new PDO('mysql:host=' . config('database.connections.mysql.host') . ':' . config('database.connections.mysql.port'), config('database.connections.mysql.username'), config('database.connections.mysql.password'), $options);

pdo = DB::connection()->getPdo();

[JeSappelleRoot]

Hi @BenMorel and @juhasev,

We are still struggling with SSL/TLS issue with the following issue SSL: Connection reset by peer.

Any tips that could help to troubleshoot the issue during GeometryEngineRegistry ? Some help will be appreciated, a support case has been opened on Azure support to get some insight on database side (Azure managed instance)

Best

[BenMorel]

Hi @JeSappelleRoot, I'm sorry but as I stated above, this is a PDO or MariaDB issue, so it's unrelated to this project. As this is an Azure managed instance, Azure support is your best bet.

I'm closing this for now, but please feel free to post your solution here if you manage to fix it!