Using docker - Connecting to MYSQL via SSL?

[icsy7867]

I have a fairly simple deployment I am trying to test:

docker run --name some-yourls \ -e YOURLS_DB_HOST="db.corporate.co:3306" \ -e YOURLS_DB_USER="yourlsadmin" \ -e YOURLS_DB_PASS='securePassword' \ -e YOURLS_DB_NAME="yourls" \ -p 8095:80 \ -d yourls

However I am repeatedly getting "Access denied" on the DB attempts. After talking to IT, they require SSL over 3306. However I dont see an easy way to tell yourls to do this. Is this possible? If so, what's the best way to do this?

EDIT - RESOLVED
Working db.php listed below. Issue turned out to be that the docker entrypoint shell script is using mysqli and not PDO, and also failing due to SSL.

[dgw]

Best I can tell, connecting to a database over SSL/TLS (still) requires adding a "must-use" plugin in user/db.php. See #2783 for the discussion, but the gist is something like (modify with your own certificate path):

<?php
/*
Plugin Name: Secure MySQL
Plugin URI: https://github.com/YOURLS/YOURLS/issues/2783
Description: SSL/TLS PDO Connection
Version: 1.0
Author: YOURLS
Author URI: https://yourls.org/
*/
// No direct call
if( !defined( 'YOURLS_ABSPATH' ) ) die();

// Add custom cert
yourls_add_filter( 'db_connect_driver_option', function ( $options ) {
    // Add your certificate paths
    // https://secure.php.net/manual/ref.pdo-mysql.php
    return $options + [ PDO::MYSQL_ATTR_SSL_CA => '/etc/ssl/certs/db-ca.crt' ];
} );

// Load DB layer as usual
require_once YOURLS_INC.'/class-mysql.php';
yourls_db_connect();

[icsy7867]

I FIXED IT! Well more accurately, I made it work....But it is not useable at the moment for production.

I have been poking around your code this morning and I manually added this into:
class-mysql.php

$driver_options = yourls_apply_filter( 'db_connect_driver_option', [PDO::MYSQL_ATTR_SSL_CA => false,PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,] ); // driver options as key-value pairs

I added two things:
PDO::MYSQL_ATTR_SSL_CA => false
(I read that this has to be set to SOMETHING for the verify line to work)

AND
PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
(Why check? Dont really care if the SSL certs are verified or not)

However this is broken for the docker image. The latest docker image dies after about 15 seconds when the DB is not connected. However I created a deployment using a local MySQL, and then dropped into the shell of the container, manuall defined my DB_HOST, DB_USER, DB_PASS and edited this line above. Which is not feasible for production.

My guess is the "hook" from db.php is either not loading correctly, or there is something wrong with the code. Any suggestions?

[LeoColomb]

Any suggestions?

Permissions 🙂

[icsy7867]

I appreciate the suggestion. It looks like permissions are fine.
644 www-data:www-data

PHP should have no trouble reading/loading this file.

Also I think this could be made standard:
$driver_options = yourls_apply_filter( 'db_connect_driver_option', [PDO::MYSQL_ATTR_SSL_CA => false,PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,] ); // driver options as key-value pairs

And not break anything. Would need to test a non-SSL connection, but I believe since it is set to false, this would allow for SSL and non-SSL connections to work out of the box.

[icsy7867]

In addition to not being able to get the db.php hook working, I found another oddity.

Nothing works in the docker container, unless I connect it to a local, working mysql container first. If I do this, and get it setup, I can change either the "define" variables in user/config.php or by editing the DB variables directly in includes/class-mysql.php.

docker run --name yourls-test --link yourls-mysql:mysql \
    -e YOURLS_SITE="http://server.organization.org:8096" \
    -e YOURLS_USER="admin" \
    -e YOURLS_PASS="admin" \
    -e YOURLS_DB_host=localhost \
    -e YOURLS_DB_PASS=localPasswordHere \
    -p 8096:80 \
    -d yourls

Assuming I connected it to a local mysql container first, this in conjunction with the SSL Verify and the SSL CA directives, I can edit those variables and everything works fine. I poked around the code and tried to see where all the environmental variables were being used, but I could not find a reason for this behavior.

There is something about requiring working DB creds when the container starts that seems to break things. I think it is due to:
https://github.com/YOURLS/docker-yourls/blob/main/docker-entrypoint.sh
Where it tries the DB 10 times then exits. But this is using MYSQLi and not PDO. Which actually makes sense. After changing the class-mysql, PDO will work, but the mysqli would still fail on this connection, causing the container to stop.

[icsy7867]

Update!

the db.php is working!

It is simplified from the suggested answer and does not require a CA.crt. The issue is due to the docker containers docker-entrypoint.sh, which uses mysqli, and not PDO, and thus fails for the same reason, but the fix is different. I have made a working fix for the docker-entrypoint.sh (However it does require a CA crt), and submitted an issue:
YOURLS/images#98

So ultimately, the db.php was in fact, loading fine. However due to the docker container setup, it was failing in a different location.

<?php
/*
Plugin Name: Secure MySQL
Plugin URI: https://github.com/YOURLS/YOURLS/issues/2783
Description: SSL/TLS PDO Connection
Version: 1.0
Author: YOURLS
Author URI: https://yourls.org/
*/
// No direct call
if( !defined( 'YOURLS_ABSPATH' ) ) die();

// Add custom cert
yourls_add_filter( 'db_connect_driver_option', function ( $options ) {
    // Add your certificate paths
    // https://secure.php.net/manual/ref.pdo-mysql.php
    return $options + [PDO::MYSQL_ATTR_SSL_CA=> false,PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,];
} );

// Load DB layer as usual
require_once YOURLS_INC.'/class-mysql.php';
yourls_db_connect();

[icsy7867]

I added my first ever pull request to an open source project!
(Pretty cool to me anyways!)

YOURLS/images#99

I have implemented a fix in docker-entrypoint.sh that checks for the db-ca.crt to exist in /etc/ssl/certs/db-ca.crt and this has to be mounted using
-v /path/to/cert/ca.crt:etc/ssl/certs/db-ca.crt

additionally added a "db.php" file that uses a ca.crt instead of "Verify" being set to false. Since we have the CA crt anyways, we should use it. This of course would also need to be mounted (file is included in the root of the project):

-v /path/to/php/db.php:/var/www/html/user/db.php