[#27348] - [4.0] Add TLS transfer encryption for database connections at installation - 2nd version
- Fixed in Code Base
- 11 Jan 2020
- Medium
- Build: 4.0-dev
- # 27348
- Diff
- richard67:4.0-dev-tls-encrypt-db-connections-at-install-v2
- Pending Hound Hound is busy sniffing around... Details
User tests: Successful: Unsuccessful:
Pull Request for Issue # .
Follow-up to PR #26375 and PR #26889.
Replaces PR #26888 .
Summary of Changes
This Pull Request (PR) adds the possibility to use an encrypted database connection like it was added with PR #26375 to the CMS already during the installation.
The installation form is extended by new fields for the encryption parameters in the same way as the server section of the Global Configuration form was extended with PR #26375 and later modified with PR #26889.
In addition it contains changes coming from review PR #26888 and reviews here, which should be implemented in the same or a similar way for Global Configuration with another, future PR. One of these changes is to not use field "Path to CA Folder" anymore, see discussion below for details.
Finally this PR changes the message type from "warning" to "error" for all database validation error messages.
With this PR here applied, UI and validation of fields in the installation form which are related to database connection encryption will work in the same way as they will work for global configuration in backend when PR #27351 is applied.
Testing Instructions
Pre-conditions
Have a database server which supports encrypted connections.
If you have a standard installation of MySQL 5.7 or later or comparable MariaDB server, or a PostgreSQL 10 or later, the database server comes with a self signed server certificate and enabled encryption support, so one-way (i.e. server side) authentication without certificate verification works out of the box.
For a webspaces with databases provided by a hosting provider it may be different, especially with shared hosting. But on my shared hosting space one-way authentication without certificate verification works, too.
For certificate verification it is required that the common name (CN) in the server certificate is equal to the server name used by the DB client. This is not the case with the default certificates used by the standard database setup mentioned above.
Some links to documentation of connection encryption for the particular kinds of databases:
- MySQL 8.0 https://dev.mysql.com/doc/refman/8.0/en/encrypted-connections.html
- MariaDB https://mariadb.com/kb/en/library/data-in-transit-encryption/ and https://mariadb.com/kb/en/library/securing-connections-for-client-and-server/
- PostgreSQL 11 https://www.postgresql.org/docs/11/ssl-tcp.html
But for testing this PR it is enough to have one-way encryption without certificate verification working.
Instructions
-
Start a new installation of a 4.0-dev branch plus changes of this PR applied, i.e. either apply it with some git tool on a clean 4.0-dev branch, or apply it on an existing 4.0-dev installation e.g. with patchtester and then remove configuration.php and delete the database tables.
-
When on the section for database configuration, check the available options.
Result: At the bottom a drop-down "Connection Encryption" is shown.
Note: The screenshot is not up to date, the description below the field has been removed. -
Check the available values in drop-down "Connection Encryption".
Result: 3 possible values "Default (server controlled)", "One-way authentication" and "Two-way authentication". -
If necessary select value "Default (server controlled)".
Result: Below the drop-down "Connection Encryption" no more options for connection encryption are are shown, see screenshot of step 2. -
Change the value of drop-down "Connection Encryption" to "One-way authentication".
Result: Below the drop-down a switcher "Verify Server Certificate" is shown.
Note: The screenshot is not up to date, the descriptions below the fields have been removed. -
Toggle the value of the "Verify Server Certificate" switcher.
Result: If the switcher is off, no more options for connection encryption are shown below the switcher, see screenshot of step 5 above.
If the switcher is on, a text field "Path to CA File" is shown below the switcher.
Note: The screenshot is not up to date, the descriptions below the fields have been removed. -
Switch "Verify Server Certificate" to "No" and change the value of drop-down "Connection Encryption" to "Two-way authentication".
Then toggle toggle the value of the database type drop-down at the top and check which fields are shown below the "Verify Server Certificate" switcher.
Result:
- MySQLi and MySQL (PDO): Fields "Path to Private Key File", "Path to Certificate File" and "Supported Cipher Stuite (optional)" are shown.
- PostgreSQL (PDO): Only fields "Path to Private Key File" and "Path to Certificate File" are shown.
Note: The screenshots are not up to date, the descriptions below the fields have been removed.
- Switch "Verify Server Certificate" to "Yes". Then toggle toggle the value of the database type drop-down at the top and check which fields are shown below the "Verify Server Certificate" switcher.
Result:
- MySQLi and MySQL (PDO): Fields "Path to Private Key File", "Path to Certificate File", "Path to CA File" and "Supported Cipher Stuite (optional)" are shown.
- PostgreSQL (PDO): Only fields "Path to Private Key File", "Path to Certificate File" and "Path to CA File" are shown.
Note: The screenshots are not up to date, the descriptions below the fields have been removed.
-
Fill in some text field and then change an option so the text field is not shown anymore, and then change the option back. E.g. swich server certificate verification on and fill in the CA file field and then toggle server certificate verification off and on again, or do the same with text fields for 2-way authentication and toggle the value of drop-down "Connection Encryption".
Result: Text fields which are not used for a particular combination of options are not cleared when the options are changed. Unneccessary text fields will later be cleared on form validation instead. -
Change the value of drop-down "Connection Encryption" to "One-way authentication" or "Two-way authentication", select correct database type, enter "localhost" as host name, enter username, password and database name and if neccessary table prefix and click the "Install Joomla" button at the bottom.
Result: The installation doesn't start. An alert at the top shows an error about using "localhost" and database connection encryption.
-
Change the host name to the real hostname for your installation, using either "127.0.0.1" or "::1" or a real host name instead of "localhost".
-
Change the value of drop-down "Connection Encryption" to "Two-way authentication", switch "Verify Server Certificate" to "No" and make sure that field "Path to Private Key File" is empty. Then click the "Install Joomla" button at the bottom.
Result: The installation doesn't start. An alert at the top shows an error about "Path to Private Key File" being empty or not a valid path.
-
Change the value of field "Path to Private Key File" so it contains invalid characters for a path, e.g. colons. Then click the "Install Joomla" button at the bottom.
Result: See step 12. -
Change the value of field "Path to Private Key File" so it contains a valid path which does not exist or is not accessible. Then click the "Install Joomla" button at the bottom.
Result: The installation doesn't start. An alert at the top shows an error about the file in field "Path to Private Key File" not existing or not being accesible.
-
Change the value of field "Path to Private Key File" so it contains a valid path which exists and is accesible, and make sure that field "Path to Certificate File" is empty. Then click the "Install Joomla" button at the bottom.
Result: The installation doesn't start. An alert at the top shows an error about "Path to Certificate File" being empty or not a valid path.
-
Change the value of field "Path to Certificate File" so it contains invalid characters for a path, e.g. colons. Then click the "Install Joomla" button at the bottom.
Result: See step 15. -
Change the value of field "Path to Certificate File" so it contains a valid path which does not exist or is not accessible. Then click the "Install Joomla" button at the bottom.
Result: The installation doesn't start. An alert at the top shows an error about the file
in field "Path to Certificate File" not existing or not being accesible.
-
Change the value of field "Path to Private Key File" so it contains a valid path which exists and is accesible, switch "Verify Server Certificate" to "Yes" and make sure that field "Path to CA File" is empty. Then click the "Install Joomla" button at the bottom.
Result: The installation doesn't start. An alert at the top shows an error about field "Path to CA File" being empty or not a valid path.
-
Change the value of field "Path to CA File" so it contains invalid characters for a path, e.g. colons. Then click the "Install Joomla" button at the bottom.
Result: See step 18. -
Change the value of field "Path to CA File" so it contains a valid path which does not exist or is not accessible. Then click the "Install Joomla" button at the bottom.
Result: The installation doesn't start. An alert at the top shows an error about the file
in field "Path to CA File" not existing or not being accesible.
-
Change the value of field "Path to CA File" so it contains a valid path which exists and is accessible but not a valid certificate, e.g. a text file, so that database connection should fail. Then click the "Install Joomla" button at the bottom.
Result: The result depends on server configuration and configuration of the database user (if allowed to connect unencrypted) in case of errors when connecting.
In case of connection error, the result depends on the database driver.
- MySQLi: It seems the error message is lost or missing. This could be an issue in the database driver.
- MySQL (PDO): The error message is cryptic, but a search in Google will tell what it means.
- PostgreSQL (PDO): The error message contains information in English words about the reason for the error.
If due to some strange configuration the connection is successful but not using encryption, a validation check added by this PR will fail. There will be 2 cases of error messages: - Server doesn't support connection encryption
- Other reason
Which of these error messages you will see in this test step depends on your server and database user and client configuration and on whether you use an existing database or create a new one during installation. But validation should fail in any case.
-
Select correct database type, enter a valid host name (not "localhost") or "127.0.0.1" or "::1" as host name, enter username, password and database name and if neccessary table prefix and adjust connection encryption parameters to something which should work, e.g. one-way authentication without server certificate verification, which should work out of the box in most cases.
Then continue to the next step of the installation.
Result: Installation finish with success. -
Verify in configuration.php that the adjusted options have been saved and unneeded options have been cleared (empty string in case of text and false in case of boolean).
Expected result
Connection encryption options in database configuration section of the installation form are available, and their values are validated as described in testing instructions.
Actual result
No connection encryption options in database configuration section of the installation form.
Additional information
The installation form does not show the fields in the same order as they are specified in the form's XML file. This issue is not solved by this PR because not related, it only becomes obvious with this PR. The problem is the switcher.
The MySQLi driver seems not to forward error messages, so if there is a connection error for some reason, it doesn't tell the reason. The PostgreSQL (PDO) seems to be better in this. MySQL (PDO) I don't remember right now. This is also not related to this PR here. I will see if I can find to investigate and if possible solve it.
Documentation Changes Required
Same as PR #26375 : New feature has to be documented. At least installation documentation has to be extended for this PR here. For PR #26375 it would need to extend documentation of the server section of Global Configuration, the Privacy Dashboard Module and the System Information view.
| Status | New | ⇒ | Pending |
joomla-cms-bot
- | Category | ⇒ | Installation Language & Strings JavaScript |
| Labels |
Added:
?
?
|
||
@brianteeman Are you using web server on Windows or Linux? Am asking because I could test only on Linux. Would be good to know if validation of file or folder paths works on Windows, too.
For testing I use Windows (as very few do)
I tested the following scenarios:
Mysql: 10.3.18-MariaDB-0+deb10u1 / PHP 7.3.11
Test 1 ALL NO - Tested Successful ✔️
- Server has Certificate configured
- Server does not required client certificate
- No TLS activated
- No Server Certificate authentication activated
- No Client Certificate authentication activated
Summary
Test successful without annotations.
Test 2 Activate TLS - Tested Successful ✔️
- Server has Certificate configured
- Server does not required client certificate
- TLS activated
- No Server Certificate authentication activated
- No Client Certificate authentication activated
Summary
Test successful without annotations.
Test 3 Authenticate Server (Invalid ca file path) - Tested Successful ✔️
- Server has Certificate configured
- Server does not required client certificate
- TLS activated
- Server Certificate authentication activated
- CA file path: /pathtoca/sql.caX
- No Client Certificate authentication activated
Summary
- Warning: The file entered in field "Path to CA File" does not exist or is not accessible.
Maybe this should be an error?
Test 4 Authenticate Server (valid ca file path) - Tested Successful ✔️
- Server has Certificate configured
- Server does not required client certificate
- TLS activated
- Server Certificate authentication activated
- CA file path: /pathtoca/sql.ca
- No Client Certificate authentication activated
Summary
- Test successful
It's not possible to see the successful server validation on system information page
Summary
Test successful without annotations.
Test 5 Authenticate Server and Client (Invalid client file path) - Tested Successful ✔️
- Server has Certificate configured
- Server does not required client certificate
- TLS activated
- Server Certificate authentication activated
- CA file path: /pathtoca/sql.ca
- Client Certificate authentication activated
- Private key file path: /pathtoca/sql.keyX
- Private crt file path: /pathtoca/sql.crtX
Summary
Expect fail - Test successful
- Warning: The file entered in field "Path to Private Key File" does not exist or is not accessible.
Maybe this should be an error? - Setting the Private Key File correct but not the Certificate:
Warning: The file entered in field "Path to Certificate File" does not exist or is not accessible.
Maybe this should be an error?
Test 6 Authenticate Server and Client (Valid client file path) - Tested Successful ✔️
- Server has Certificate configured
- Server does not required client certificate
- TLS activated
- Server Certificate authentication activated
- CA file path: /pathtoca/sql.ca
- Client Certificate authentication activated
- Private key file path: /pathtoca/sql.key
- Private crt file path: /pathtoca/sql.crt
Summary
- Test successful
It's not possible to see the successful server and client validation on system information page
Server ignores client certificate if it is not required (at least if it's not invalid - more in test 10)
Test 7 Authenticate Server (Server require client certificate) - Tested Successful ✔️
- Server has Certificate configured
- Server does required client certificate
- TLS activated
- Server Certificate authentication activated
- CA file path: /pathtoca/sql.ca
- No Client Certificate authentication activated
Summary
Expect fail - Test successful
- Error: Could not connect to the database. Connector returned error message: Could not connect to database: Access denied for user 'j4'@'hostname' (using password: YES)
Test 8 Authenticate Server and Client (Server require client certificate) - Tested Successful ✔️
- Server has Certificate configured
- Server does required client certificate
- TLS activated
- Server Certificate authentication activated
- CA file path: /pathtoca/sql.ca
- Client Certificate authentication activated
- Private key file path: /pathtoca/sql.key
- Private crt file path: /pathtoca/sql.crt
Summary
Test successful
Test 9 Authenticate Server and Client (Invalid client certificate) - Tested Successful ✔️
- Server has Certificate configured
- Server does required client certificate
- TLS activated
- Server Certificate authentication activated
- CA file path: /pathtoca/sql.ca
- Client Certificate authentication activated
- Private key file path: /pathtoWORNGca/sql.key
- Private crt file path: /pathtoWORNGca/sql.crt
Summary
Expect fail - Test successful
- Error
Could not connect to the database. Connector returned error message: Could not connect to database:
Error message is not really helpful but I think we can't do anything against this
Test 10 Authenticate Server and Client (Invalid client certificate, Server doesn't require client certificate ) - Tested Successful ✔️
- Server has Certificate configured
- Server does not required client certificate
- TLS activated
- Server Certificate authentication activated
- CA file path: /pathtoca/sql.ca
- Client Certificate authentication activated
- Private key file path: /pathtoWORNGca/sql.key
- Private crt file path: /pathtoWORNGca/sql.crt
Summary
Possible expect fail - Test successful
- Error
Could not connect to the database. Connector returned error message: Could not connect to database:
Error message is not really helpful but I think we can't do anything against this - If a client certificate is provided it must be valid
Summary for mariadb 5.7
Works as expected. The only thing is we have too less information in the info page but I'm not sure if we get them from the db driver.
Additional
I was unable to use capath, the client didn't found my sql.pem file in the directory... I'm not sure if this setting is needed anyway because there are many other options that we done expose to the user.
I was unable to use capath, the client didn't found my sql.pem file in the directory...
CP path wortks only if the client has been compiled using the openssl library and not any other, and if the folder has been processed with the openssl_rehash command so it contains links with names equal to the hash of the CA file to which it links to, this for each present CA file. See https://mariadb.com/kb/en/ssltls-system-variables/#ssl_capath.
database: Error message is not really helpful but I think we can't do anything against this
See section "Additional comments" in the description.
Warning: The file entered in field "Path to Certificate File" does not exist or is not accessible. Maybe this should be an error?
See section "Additional comments" in the description. Validation messages right now are all of type warning. From my point of view they should all be of type error. But that's not an issue introduced by this PR.
Regarding enforcing encrypted connection for particular users: Maybe I will make a PR to the framework to provide such function and then a future PR for the CMS to use it.
Finally: Is your test successful all in all? If so, please mark the test result on the issue tracker.
@HLeithner P.S.: What I could do in addition is to check in connection validation at the end after connection to the db if 1-way or 2-way is selected and the connection is not encrypted (e.g. if on the server encryption is not enabled at all), and issue a validation error (warning message up to now like the others) if that is the case. Should I add it to this PR?
Edit: I would prefer to do such enhancement in a future PR for having an easier test, doing it in this PR here would make test too complicated here.
I think it would make sense to validate the success
And I would remove the capath, it adds complexity and I don't see any benefit.
If you mean to just remove that field but use one field for both and check if file or folder and then set the corresponding parameter, like you had proposed in a comment in PR #26888 : I've tested that here and it worked, but it made validation even more complicated, so I abandoned this solution.
If you mean not to use the ca_path parameter at all, i.e. only have 1 field for CA file in all cases: Yes, that would make sense and simplify UI and docs to be done.
The CA path can only be used if the client has been compiled with openssl and not any other ssl library, and if the folder has been prepared with the openssl_rehash command. The only benefit I see would be if using the same set of connection parameters for more than one connection to different servers, so the client can chose the right CA file in the folder due to the checksum link created by openssl_rehash depending on the server certificate received when connecting to a particular server, a scneario which we don't have and don't need.
The only reason why I included the ca_path is because the database drivers support to use it and I did not want to reduce possible functionality in the CMS due to the decision of one single person (= me).
@brianteeman If you find time for reading before tomorrow: Could you check my comment above regarding the "Path to CA Folder" field ("ca_path" parameter for MySQL db drivers)? What do you think? Should we just remove it? Benefit of that parameter is close to zero but it complicates UI and validation a lot.
i dont really know enough about this to comment technically. But based on your comments I would agree to remove it. Simpler is always better
It can be that I'll make a new PR with a 3rd version which will replace this one if I see that there will be too many changes in testing instructions and that code diff will look too complicated.
I think I'll remove the "Path to CA Folder" field completely because is close to useless, and I will see if I can add additional validation after successful database connection to see if an encrypted connection is used or if the client has silently fallen back to an unencrypted connection because the server allows that. Not sure if this is easy to test because in my environment connection fails if some parameter is wrong, it does not fall back to unencrypted.
I also think about changing type of all database validation errors (also those which are there without this PR) from "warning" to "error", because if one validation check fails, validation returns with false. Maybe in past those messages were queued so at the end one could see all failures, then the warning type made sense, but now after the first validation error the validation ends, and so you can always see only the first error found, and for this behavior the error type is the right one, like it is for the connection error message shown when all validation was successful but the connection failed for other reasons (like db server stopped ;-) ).
Finally I'll see if I can find the reason why in case of MySQLi (and maybe MySQL PDO, too) message texts of exceptions are incomplete, while on PostgreSQL PDO I see the real reason when connection fails, like e.g. if some certificate file parameter for connection encryption points to a text file which is not a certificate ;-).
Not sure about all that yet, but if I do that it would require new testing instructions with new screen shots, which would be more comfortable for me to do with a new PR.
I've decided to do the other suggested changes here, too:
- Removal of nearly useless "Path to CA Folder" (ca_path) parameter
- Change of validation messages from warning to error
- Added final validation for connection encryption after successful connection.
I'll update testing instructions now. Stay tuned for that.
@HLeithner Code and testing instructions ready for testing again.
This description serves no benefit. It will just confuse the 99% users who will not use this feature
Especially as the error message will explain the problem
This string is also not needed. It should be in the docs but not on the screen
@brianteeman Done.
Drone failure seems not to be related to this PR.
I tested the following scenarios:
PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1) / PHP 7.3.11
Test 1 ALL NO - Tested Successful ✔️
- Server has Certificate configured
- Server does not required client certificate
- No TLS activated (Server controlled)
- No Server Certificate authentication activated
- No Client Certificate authentication activated
Summary
Test successful.
- Connection was automatically created with TLSv1.2 (ECDHE-ECDSA-AES256-GCM-SHA384)
Test 2 Activate TLS - Tested Successful ✔️
- Server has Certificate configured
- Server does not required client certificate
- TLS activated
- No Server Certificate authentication activated
- No Client Certificate authentication activated
Summary
Test successful without annotations.
Test 3 Authenticate Server (Invalid ca file path and wrong ca) - Tested Successful ✔️
- Server has Certificate configured
- Server does not required client certificate
- TLS activated
- Server Certificate authentication activated
- CA file path: /pathtoca/sql.caX
- No Client Certificate authentication activated
Summary
Possible expect fail - Test successful
- Error: The file entered in field "Path to CA File" does not exist or is not accessible.
Test 4 Authenticate Server (valid ca file path) - Tested Successful ✔️
- Server has Certificate configured
- Server does not required client certificate
- TLS activated
- Server Certificate authentication activated
- CA file path: /pathtoca/sql.ca
- No Client Certificate authentication activated
Summary
- Test successful
- It seams postgresql doesn't support SAN (unrelated to Joomla or this PR)
Test 5 Authenticate Server and Client (Invalid client file path) - Tested Successful ✔️
- Server has Certificate configured
- Server does not required client certificate
- TLS activated
- Server Certificate authentication activated
- CA file path: /pathtoca/sql.ca
- Client Certificate authentication activated
- Private key file path: /pathtoca/sql.keyX
- Private crt file path: /pathtoca/sql.crtX
Summary
Expect fail - Test successful
- Error: The file entered in field "Path to Private Key File" does not exist or is not accessible.
- Setting the Private Key File correct but not the Certificate:
Error: The file entered in field "Path to Certificate File" does not exist or is not accessible. - Wrong file permission for the private key:
**Error: Could not connect to the database. Connector returned error message: Could not connect to PDO: SQLSTATE[08006] [7] private key file "/pathtokey/sql.key" has group or world access; permissions should be u=rw (0600) or less
Test 6 Authenticate Server and Client (Valid client file path) - Tested Successful ✔️
- Server has Certificate configured
- Server does not required client certificate
- TLS activated
- Server Certificate authentication activated
- CA file path: /pathtoca/sql.ca
- Client Certificate authentication activated
- Private key file path: /pathtoca/sql.pem
- Private crt file path: /pathtoca/sql.pem
Summary
- Test successful
It's not possible to see the successful server and client validation on system information page
Server ignores client certificate if it is not required
Test 7 Authenticate Server (Server require client certificate) - Tested Successful ✔️
- Server has Certificate configured
- Server does required client certificate
- TLS activated
- Server Certificate authentication activated
- CA file path: /pathtoca/sql.ca
- No Client Certificate authentication activated
Summary
Expect fail - Test successful
- Wrong certifcate:
Error: Could not connect to the database. Connector returned error message: Could not connect to PDO: SQLSTATE[08006] [7] FATAL: certificate authentication failed for user "dev" - No Certificate:
- Error: Could not connect to the database. Connector returned error message: Could not connect to PDO: SQLSTATE[08006] [7] FATAL: connection requires a valid client certificate
Test 8 Authenticate Server and Client (Server require client certificate) - Tested Successful ✔️
- Server has Certificate configured
- Server does required client certificate
- TLS activated
- Server Certificate authentication activated
- CA file path: /pathtoca/sql.ca
- Client Certificate authentication activated
- Private key file path: /pathtoca/sql.pem
- Private crt file path: /pathtoca/sql.pem
Summary
Test successful
Test 9 Authenticate Server and Client (Invalid client certificate) - Tested Successful ✔️
- Server has Certificate configured
- Server does required client certificate
- TLS activated
- Server Certificate authentication activated
- CA file path: /pathtoca/sql.ca
- Client Certificate authentication activated
- Private key file path: /pathtoWORNGca/sql.key
- Private crt file path: /pathtoWORNGca/sql.crt
Summary
Expect fail - Test successful
- Could not connect to the database. Connector returned error message: Could not connect to PDO: SQLSTATE[08006] [7] SSL error: tlsv1 alert unknown ca
Summary for postgresql 12.1
Works as expected.
Annotations
This test also confirms the changed parts for mysql from the last change.
I have tested this item
MariaDB 10.3 and Postgresql 12.1 ok
This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/27348.
I've clicked the "Update branch" button to see if drone fails this time again or not. Previous test result is still valid.
@brianteeman I'd be happy if you could find some time for testing this.
Conflicts please
| Status | Pending | ⇒ | Fixed in Code Base |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2020-01-11 00:15:57 |
| Closed_By | ⇒ | wilsonge |
Thanks!
Thanks, too!
Thanks @richard67 I will check this on Friday when I get back from the conference