Support SSL-secured MySQL database connectivity

[nmehlei]

Describe the feature you'd like
If I understand correctly, database connectivity between BookStack and the MySQL database server is currently not SSL encrypted. This could be supported to enable more security and more use cases. Possibly, the underlying database provider/library already supports it.

Describe the benefits this feature would bring to BookStack users
Allows for more usage scenarios. What might be relevant, the default connection security settings for Azure's MySQL database service offering enforces SSL connection security. Thus I had to disable this to allow BookStack using the database.

[tomaskir]

+1 to this - would definitely appreciate a native way to do MySQL TLS connections from BookStack.
You can however already do this.

Here is how our BookStack is setup to use TLS to our Mariadb server:

In config/database.php you need to edit the mysql DB connection section:

'mysql' => [
    ...
    'strict'    => false,
    'engine' => null,
    'sslmode' => 'require',
    'options' => array(
        PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
        PDO::MYSQL_ATTR_SSL_CA => '/path/to/ca_chain.pem',
    ),
],

You can also use PDO::MYSQL_ATTR_SSL_CERT and PDO::MYSQL_ATTR_SSL_KEY if you want to do client verification using a client cert.

[sosamv]

Anyone know about this? I've tried this option from @tomaskir but seems like bookstack is ignoring it. I even add error to that database file and keep getting the SSL error. Any clue on what to do?

[sosamv]

Anyone know about this? I've tried this option from @tomaskir but seems like bookstack is ignoring it. I even add error to that database file and keep getting the SSL error. Any clue on what to do?

Ok guys! for anyone having the same problem. You have to refresh laravels cacheeeeee OMG!

php artisan optimize:clear

[grasshopper139]

Is there any update on this issue? I modified app/Config/database.php:

       'mysql' => [
            'driver'         => 'mysql',
            'url'            => env('DATABASE_URL'),
            'host'           => $mysql_host,
            'database'       => env('DB_DATABASE', 'forge'),
            'username'       => env('DB_USERNAME', 'forge'),
            'password'       => env('DB_PASSWORD', ''),
            'unix_socket'    => env('DB_SOCKET', ''),
            'port'           => $mysql_port,
            'charset'        => 'utf8mb4',
            'collation'      => 'utf8mb4_unicode_ci',
            // Prefixes are only semi-supported and may be unstable
            // since they are not tested as part of our automated test suite.
            // If used, the prefix should not be changed otherwise you will likely receive errors.
            'prefix'         => env('DB_TABLE_PREFIX', ''),
            'prefix_indexes' => true,
            'strict'         => false,
            'engine'         => null,
            'sslmode'        => 'require',
            'options'        => extension_loaded('pdo_mysql') ? array_filter([
                    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
                    PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

Then I cleared the cache by issuing:
php artisan optimize:clear
php artisan cache:clear

When I run php artisan migrate I get:

Illuminate\Database\QueryException  : SQLSTATE[HY000] [9002] SSL connection is required. Please specify SSL options and retry. (SQL: select * from information_schema.tables where table_schema = bookstack and table_name = migrations and table_type = 'BASE TABLE')

  at /opt/bookstack/vendor/laravel/framework/src/Illuminate/Database/Connection.php:669
    665|         // If an exception occurs when attempting to run a query, we'll format the error
    666|         // message to include the bindings with SQL, which will make this exception a
    667|         // lot more helpful to the developer instead of just the database's errors.
    668|         catch (Exception $e) {
  > 669|             throw new QueryException(
    670|                 $query, $this->prepareBindings($bindings), $e
    671|             );
    672|         }
    673|

I am running Bookstack v21.10.2 on Ubuntu 20.04.

[hovanvydut]

Is there any update on this issue? I modified app/Config/database.php:

       'mysql' => [
            'driver'         => 'mysql',
            'url'            => env('DATABASE_URL'),
            'host'           => $mysql_host,
            'database'       => env('DB_DATABASE', 'forge'),
            'username'       => env('DB_USERNAME', 'forge'),
            'password'       => env('DB_PASSWORD', ''),
            'unix_socket'    => env('DB_SOCKET', ''),
            'port'           => $mysql_port,
            'charset'        => 'utf8mb4',
            'collation'      => 'utf8mb4_unicode_ci',
            // Prefixes are only semi-supported and may be unstable
            // since they are not tested as part of our automated test suite.
            // If used, the prefix should not be changed otherwise you will likely receive errors.
            'prefix'         => env('DB_TABLE_PREFIX', ''),
            'prefix_indexes' => true,
            'strict'         => false,
            'engine'         => null,
            'sslmode'        => 'require',
            'options'        => extension_loaded('pdo_mysql') ? array_filter([
                    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
                    PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

Then I cleared the cache by issuing: php artisan optimize:clear php artisan cache:clear

When I run php artisan migrate I get:

Illuminate\Database\QueryException  : SQLSTATE[HY000] [9002] SSL connection is required. Please specify SSL options and retry. (SQL: select * from information_schema.tables where table_schema = bookstack and table_name = migrations and table_type = 'BASE TABLE')

  at /opt/bookstack/vendor/laravel/framework/src/Illuminate/Database/Connection.php:669
    665|         // If an exception occurs when attempting to run a query, we'll format the error
    666|         // message to include the bindings with SQL, which will make this exception a
    667|         // lot more helpful to the developer instead of just the database's errors.
    668|         catch (Exception $e) {
  > 669|             throw new QueryException(
    670|                 $query, $this->prepareBindings($bindings), $e
    671|             );
    672|         }
    673|

I am running Bookstack v21.10.2 on Ubuntu 20.04.

I have the same error. Do you have the solution?
Thank you so much.

[bourdaisj]

You're missing some PDO attributes.

PDO::MYSQL_ATTR_SSL_KEY=>'/certs/client-key.pem',
PDO::MYSQL_ATTR_SSL_CERT=>'/certs/client-cert.pem',

It could be supported out of the box by creating a bool DB_SSL_CONNECTION variable in the .env, and common pdo ssl attributes.

[ssddanbrown]

Just spent a while looking into this after being asked in the BookStack discord.

From my testing, SSL usage is technically currently possible within BookStack using the below .env option:

MYSQL_ATTR_SSL_CA="/path/to/server-cert.pem"

This sets the PDO::MYSQL_ATTR_SSL_CA value in the background.

The below are assumptions from spending hours testing and tweaking variables. There seems to be a lack of information out there regarding how PHP, PDO, MySQL & SSL entwine. In addition, the errors thrown in failure scenarios, at least with MariaDB, don't provide any information at all as far as I could see.

Setting the above also seems to set the following default option:

PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true

From the MySQL docs:

This option causes the client to perform host name identity verification by checking the host name the client uses for connecting to the server against the identity in the certificate that the server sends to the client: ... the client checks whether the host name that it uses for connecting matches the Common Name value in the server certificate.

This means that the MySQL server certificate must be using a Common Name that matches the DB_HOST value that you use for BookStack. This may also need to match the MySQL server's hostname value but I have not verified this.

Upon the above, using localhost as a hostname appears to fail more commonly than other hostnames/IP addresses when getting involved with SSL verification. This probably won't matter for most cases (Since SSL becomes somewhat redundant if the database is local) but it did cause confusion in my testing.

---

Copy of notes based upon past comments here:

• Any changes to the database.php file are not considered supported and may break or cause troubles upon update.
• Adding PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false to the array_filter([ part of database.php won't take any affect since the option would be filtered out right away.

---

If any additional options are definitely required feel free to explain the reason why, but I would like some insight into why they would be needed since I'll need to understand the usage when maintaining/testing things. Upon that, I'd want to know that an option is needed rather than it being added just because it made things work in one instance.

[bourdaisj]

• My comment was about client cert verification, just sayin it could be supported this way but since it's really not a frequent use-case I don't think it should actually be supported. This was a bit off-topic because I though @hovanvydut wanted to do client cert verif (after looking at the PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false ) but now I don't think thats what he wanted to achieve right?
• The .env variable MYSQL_ATTR_SSL_CA="/path/to/ca.pem" works perfectly fine for me, but indeed you cannot use the default mysql certificate CN (it will mismatch with the DB_HOST), or you can add an entry in your /etc/hosts file but it does looks like a nasty hack...

The use of MYSQL_ATTR_SSL_CA="/path/to/ca.pem" could just be added to the documentation but it's not a bookstack-specific think so idk if it's in the scope of the doc...

[ssddanbrown]

@Julesdevops Sure. Just to confirm my comment above was not in response to you previous comment at all. I was just looking to provide any clarification I can offer regarding existing settings based on my findings. I'm sure there are requirements for other settings but I'd want confirmation of requirement with explanation of use-base before expanding out our remit of support. Your exactly right in that we should add documentation for this option, if the current option is enough for most use-cases.

[hovanvydut]

@ssddanbrown Thank you so so much. I have resolved my issue by removing condition in this code

'options'        => extension_loaded('pdo_mysql') ? array_filter([
                    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
                    PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],

to

'options'        => [
                    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
                    PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]

Again, I'm very pleased with your help

[MaartenUreel]

If I understand the above correctly, there is currently no way to enforce SSL but ignore validation of the certificate?

I am trying to deploy BookStack as a DigitalOcean App usin the LinuxServer.io container. In order to use the managed database service of DigitalOcean, we apparently cannot validate the certificate.