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.

[ssddanbrown]

@MaartenUreel It really depends on many factors tbh, this is a messy area, with mulitple layers of "validation" and many different configuration and options involved.
Might depend on what exact "validation" you want to ignore and where SSL is enforced.

You might be able to enforce SSL at a MySQL-user level, can't remember how certs and validation interplay there.

Have you tried using a the DigitalOcean CA cert?
Based upon their docs, it looks like they do provide it: https://docs.digitalocean.com/products/databases/mysql/how-to/connect/#download-the-ssl-encryption

Note: I remember some other users reporting issues about indexes using Digital Ocean hosted MySQL service. Might have been the kind of thing they had to connect support or something to make compatible. Can't remember the detail.

[MaartenUreel]

Hey @ssddanbrown; apparently it is not required although it does looks like it in the console. I was able to connect without SSL and indeed I also had to arrange the index setting to allow creation of records without primary keys.

I managed to get it up and running on DO with their managed database and using Spaces as storage layer.