Document SSL PDO Connection Options

Changing status to needs review

I manually applied the patch in #1 and it worked/looked fined except for one small misspelling. I corrected that and attached an updated patch to this comment.

I also use @JohnWSmith's method for setting up MySQL communications over SSL in the settings.php file. It works nicely. I think this documentation addition will raise awareness of this security related configuration. I would also like to see this in core so it is available without patching in the Guardr Drupal security distribution (At this time, inclusion in Guardr will require a 7.x backport.)

looks good here. Good work.

Patch from #3 applied successfully and looks good.

I'm currently running Drupal 8.1.8 on a Fedora 24 server with the following configuration :
PHP 7.0.9
MariaDB 10.0.16
PHP-FPM
Apache 2.4.23

I have been able to create the SSL certificates for the CA, Server and Client, and have successfully tested connectivity via the MySQL client. However I have not been able to connect Drupal 8 to the MariaDB database via SSL . I have seen two different versions of the database configuration in settings.php on drupal.org, however both versions have been unsuccessful.

Scenario 1
=========
$databases['default']['default'] = array (
'database' => 'drupal8',
'username' => 'dbuserssh',
'password' => '****',
'prefix' => '',
'host' => '127.0.0.1',
'port' => '3306',
'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
'driver' => 'mysql',
'pdo' => array(
MYSQL_ATTR_SSL_KEY => '/sites/tomthorp_intnet/client-key.pem',
MYSQL_ATTR_SSL_CERT => '/sites/tomthorp_intnet/client-cert.pem',
MYSQL_ATTR_SSL_CA => '/sites/tomthorp_intnet/ca.pem',
),
);

yields ....

[13-Aug-2016 03:43:03 UTC] PDOException: SQLSTATE[HY000] [1045] Access denied for user 'dbuserssh'@'localhost' (using password: YES) in /usr/share/website/drupal/core/lib/Drupal/Component/DependencyInjection/PhpArrayContainer.php on line 79

Scenario 2
=========
$databases['default']['default'] = array (
'database' => 'drupal8',
'username' => 'dbuserssh',
'password' => '******',
'prefix' => '',
'host' => '127.0.0.1',
'port' => '3306',
'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
'driver' => 'mysql',
'pdo' => array(
PDO::MYSQL_ATTR_SSL_KEY => '/sites/tomthorp_intnet/client-key.pem',
PDO::MYSQL_ATTR_SSL_CERT => '/sites/tomthorp_intnet/client-cert.pem',
PDO::MYSQL_ATTR_SSL_CA => '/sites/tomthorp_intnet/ca.pem',
),
);

yields ....

[13-Aug-2016 03:36:27 UTC] PDOException: SQLSTATE[HY000] [2002] in /usr/share/website/drupal/core/lib/Drupal/Component/DependencyInjection/PhpArrayContainer.php on line 79

Has anyone been able to get SSL database connectivity to work in Drupal 8, as there appears to be plenty of examples of SSL working in Drupal 7. Any input would be much appreciated.

This works on my Drupal 8 setup. Should we consider a "Backport to D7" tag?

Rerolled, moved the word "the" to a previous line and changed the array to short syntax.

The patch looks good, but I have some remarks:

1. The setting of the pdo attributes works only for MySQL. I am missing that in the patch.
2. With PostgreSQL you can require a SSL connection with the following setting:

    * @code
    * $databases['default']['default'] = array(
    *   'init_commands' => array(
    *     'sslmode' => 'require',
    *   ),
    * );
    * @endcode
   

3. A SQLite database is always on the local machine so a SSL connection is not useful. Can we add this to the documentation.

Please ignore that one.I will upload another patch.

+++ b/sites/default/default.settings.php
@@ -193,6 +193,47 @@
+ * Advanced users can also specify MySQL database layer connection security using the

Line exceeding 80 characters
Applying the patch, please review.

Looks better. Some remarks:

1. +++ b/sites/default/default.settings.php
   @@ -193,6 +193,47 @@
   + * Now if you have the following settings in PostgreSQL:
   

   Can we change this to: "For requiring a SSL connection to a PostgreSQL database add:"

2. +++ b/sites/default/default.settings.php
   @@ -193,6 +193,47 @@
   + * For PostgreSQL SSL connection use the following settings:
   + * @code
   + * 'pdo' => [
   + *   PGSQL_ATTR_SSL_KEY => '/path/to/ssl-cert.key',
   + *   PGSQL_ATTR_SSL_CERT => '/path/to/ssl-cert.crt',
   + *   PGSQL_ATTR_SSL_CA => '/path/to/ca-cert.crt',
   + *   // Optional
   + *   PGSQL_ATTR_SSL_CAPATH => '/ca/path',
   + *   PGSQL_ATTR_SSL_CIPHER => 'ssl cipher',
   + * ],
   + * @endcode
   + *
   

   There are no such PDO attributes for PostgreSQL

Thanks for review @daffie. Regarding 19.2 i also wasn't sure about them , saw that in some solutions on stackexchange. Does the MySQL way sufficient for postgresql as well.??

Looks good to me. Just one remark:

+++ b/sites/default/default.settings.php
@@ -193,6 +193,35 @@
+ * For SQLite database ssl connection settings are not required as it is
+ * only supported on localhost.

Can we change the text to: "SQLite databases do not support a SSL connection, because they only live on the localhost."

Adding Guardr tag back in. We use this for tracking issues related to the Guardr distro. Thanks!

@shrop: Sorry about removing the tag.

@daffie: No worries! Thanks for the help on this issue!

Looks good to me.

@gaurav.kapoor: Thanks for working on this.

Thank you all for picking this one up. It is needed.

1. +++ b/sites/default/default.settings.php
   @@ -193,6 +193,35 @@
   + * Advanced users can also specify MySQL database layer connection security
   + * using the'pdo' attribute for array as follows:
   

   "the'pdo'"

   I am not sure what "database layer" means in this context. It just seems confusing. Are we not talking about "network connection security"?

2. +++ b/sites/default/default.settings.php
   @@ -193,6 +193,35 @@
   + * For requiring a SSL connection to a PostgreSQL database add:
   

   a/an

3. +++ b/sites/default/default.settings.php
   @@ -193,6 +193,35 @@
   + * SQLite databases do not support a SSL connection, because they only
   + * live on the localhost.
   

   a/an, some text wrapping issues. I would suggest "...do not support SSL connections."

4. +++ b/sites/default/default.settings.php
   @@ -193,6 +193,35 @@
   + * Any features supported by the PDO driver of the database can be put into
   + * the 'pdo' attribute array, as defined in the php documentation.
   + * @url http://php.net/manual/en/pdo.drivers.php
   

   "of the database" is not needed.

All in all, this patch needs work. It starts off by explaining that advanced users can do something with PDO for MySQL, then moves on to (seemingly) non-PDO instructions for Postgre, then discusses SQLite, then back to a general statement about PDO. So, the organization could be much improved.

Fixed some of the points suggested in 28 and rearranged documentation contents.

I'm seeing the same issues as #7. Because of this, I'm marking this as `needs work`. As far as I can tell, the constants are written incorrectly and with the right ones, there are issues.

I'm using Drupal 8.3.7 core by itself with MySQL 5.7.19 on RHEL.

Namely, the patches point to constants

MYSQL_ATTR_SSL_KEY
MYSQL_ATTR_SSL_CERT
MYSQL_ATTR_SSL_CA

These don't seem to exist. The following constants do exist and are mentioned in some of the comments. See http://php.net/manual/en/ref.pdo-mysql.php for these constants as well.

PDO::MYSQL_ATTR_SSL_KEY
PDO::MYSQL_ATTR_SSL_CERT
PDO::MYSQL_ATTR_SSL_CA

If the problematic constants are used, they seem to be ignored. See drush sql-connect and you get something like

mysql --user=myuser --password='mypassword' --database=mydatabase --host=localhost --port=3306

You'll see in the following that there should be several additional parameters that start with --ssl.

If you use the available ones (with PDO::), you get the variables reported, but Drupal cannot fully connect, sql-connect,

mysql --user=myuser --password='mypassword' --database=mydatabase --host=localhost --port=3306 --ssl-ca=/path/to/cert/ca-cert.pem --ssl-cert=/path/to/cert/client-cert.pem --ssl-key=/path/to/cert/client-key.pem

If I add the `PDO::` constants, drush sqlc as well as the string from drush sql-connect work, but drush status produces the error

PDOException: SQLSTATE[HY000] [2002] in /[path-to-docroot]/core/lib/Drupal/Component/DependencyInjection/PhpArrayContainer.php on line 79 #0 /[path-to-docroot]/core/lib/Drupal/Core/Database/Driver/mysql/Connection.php(149): PDO->__construct('mysql:host=loca...', 'myuser', 'mypassword...', Array)

Similarly, the site will not load.

same issue with #7.

environment:
OS: RHEL 6
MySQL: 5.7.20
Drupal: 8.4.2

Can anyone share how it works ?

Here is the solution: https://www.kamenov.biz/php-application-cannot-connect-to-mysql-over-ssl/

    'pdo' => [
          \PDO::MYSQL_ATTR_SSL_KEY    =>'/var/www/site/keys/client-key.pem',
          \PDO::MYSQL_ATTR_SSL_CERT   =>'/var/www/site/keys/client-cert.pem',
          \PDO::MYSQL_ATTR_SSL_CA     =>'/var/www/site/keys/server-ca.pem',
          \PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false
    ]

There is another use case that I can't seem to work around - the requirement to use SSL options during Drupal installation. Tested with 8.6.13.

Steps to reproduce:
1) Create mysql/mariadb instance with empty database table per INSTALL.mysql.txt, on a SQL instance that requires SSL connections.
2) Start the Drupal installation process via install.php.
3) Arrive at the database configuration screen, notice that there are no options to input PDO parameters.

OK, nothing in the UI. Let's edit the settings.php file directly and just insert the PDO statements.

1) cp default.settings.php settings.php
2) Edit settings.php, add the PDO parameters to the database array in the file per the instructions on this thread (and I know they are correct, tested with a D7 install using the same SQL instance)
3) Visit the install.php installer, and now notice that "Drupal already installed" - the installer won't proceed.

Unless I'm missing something, there is a check in the installer that would also need to be changed so it does not think Drupal is already installed when you add the PDO statements to an otherwise vanilla settings.php file.

I have issues with the initial installation process when using a REQUIRED SSL connection.
I had the following database configuration on a fresh settings.php (copied from default.settings.php)

$databases['default']['default'] = [
	  'database' => 'my_database_name',
	  'username' => 'my_database_username@my_azure_database_instance',
	  'password' => 'my_database_user_password',
	  'prefix' => '',
	  'host' => 'my_database_instance.mariadb.database.azure.com',
	  'port' => '',
	  'driver' => 'mysql',
	  'pdo' => [
		PDO::MYSQL_ATTR_SSL_CA => '/etc/pki/ca-trust/extracted/pem/tls-ca-bundle.pem',
		PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false
		]
];

The installation process goes through the following steps:
1/ language selection (2 tables created in the database -> yippy connection is working)
2/ requirement checked (OPcache is not installed)
3/ database information form which displays the information present in settings.php, but without the pdo details ...
continue -> Drupal already installed (apache error logs shows SSL connection is required, even though few more tables have been created in the process)

I would suggest that only PDO::MYSQL_ATTR_SSL_CA be marked as required.

There is also something in the install process (maybe in SiteSettingsForm.php) that is not compatible with pdo details.

Thanks

I want to stress that various examples and suggestions that using \PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false is a solution is actually wrong. This attribute turns off server host name verification against CN from certificate which is crucial part of establishing a secure connection.

PHP has a good defaults in this regard , e.g. it's enabled by default when PDO::MYSQL_ATTR_SSL_CA attribute is supplied (and yes , PDO::MYSQL_ATTR_SSL_CA is the only required attribute to instruct PDO to establish secure connection) , but, in some cases, falls short when this verification fails returning SQLSTATE[HY000] [2002] without any message. Check that your secure connection is actually secure with the MySQL client by adding --ssl-ca parameter specifying path to your CA certificate and --ssl-mode=VERIFY_IDENTITY, like mysql --user=user --database=db_name --host=db-host --port=3306 --ssl-ca=/mnt/secrets/ca-cert -A --ssl-mode=VERIFY_IDENTITY - it has more details in the output.

On a related note, I have opened an issue to make the site status report show MySQL TLS/SSL info: #3268481: Include database SSL/TLS info on status report.

Added reroll of patch #29 on Drupal 9.4.x.

I am a little confused here. In #850600: PostgreSQL driver doesn't support SSL option is there indication that there is no SSL support but in this issue we have

+ * For requiring an SSL connection to a PostgreSQL database add:
+ * @code
+ * $databases['default']['default'] = array(
+ *   'init_commands' => array(
+ *     'sslmode' => 'require',
+ *   ),
+ * );