Potential testers please wait with testing until title does not contain "[WiP]" anymore and status on GitHub is not draft anymore.

PR is ready for test now.

@wilsonge Do you think this needs to be checked by the SST, too? Note that these parameters for file paths and folder paths are just connection parameters passed to the db client. What these parameters do is described in MySQL/MariaDB/PostgreSQL documentation for which I've linked the start pages in the description. Joomla itself does nothing with these files or folders, and I think it should not do anything with it, not even check if they exist. It's on the db client (PDO or MySQLi) to do that. So I don't think it needs to add files or folders validations for these fields.

I tested this with mysqli and mysql pdo on a mariadb 10.3 installation both with server verification on and off incl. two way encryption

2 things (unrelated to this pr)

• two way encryption is the wrong description, this should be something like "client authentication" or something like this
• for verify server we should allow to have the path to ca file and (untested) "path to ca folder". The reason for this is that you can add the ca file independent from the server CA store. That's how I tested it.

We should do the 2 changes mentioned in the previous comment by @HLeithner in a future PR consistently for both the installation and the backend together. I can prepare PR(s), but input is welcome for the naming of the encryption methods.

#18736 needs to be resolved before this can be properly tested and merged

@brianteeman #18736 is not really required to be solved to test this PR, it also works without, and database connection encryption also can be set up with database host and client being on the same computer. But you may be right that for safe use of this function with 2 hosts it would be safer to have the safety checks activated again, as mentioned in #18736.

You can not test 100% this code without the non-localhost security process in place.

@brianteeman In fact I have tested 100% of this code with all 3 kinds of database drivers with 2 hosts.

but not with the !localhost security measures in place which is an important part of this pr

@HLeithner Because this PR here seems to need much time for finding testers and maybe will be delayed for reasons mentioned by @brianteeman , I've made a PR to implement your suggestions in your comment above at least for the (existing) functionality in Global Configuration: See #27320 .

@HLeithner I've applied the changes you have suggested in your comment above tho this PR here in the same way as my PR #27320 proposes for the backend. I've updated testing instructions above so they reflect these changes.

but not with the !localhost security measures in place which is an important part of this pr

This is correct but as we have been told by @wilsonge (#18736 (comment)) the rewrite of the installer needs to be done anyway. I personally think that for now this can be tested without the checks in place. After merging this we should add this point to the issue: #18736 as this needs to be done anyway.

Up to @wilsonge and @HLeithner to decide on for sure.

You can not test 100% this code without the non-localhost security process in place.

@brianteeman I don't see where this PR is related to the localhost issue.

The localhost check have to be addressed, thats true but doesn't change anything in this pr.

This PR involves installing when the sql server is not on the localhost.
We have specific code that needs to be implemented to address a security issue when the sql server is not on the localhost. (No idea why it hasnt been yet)
Therefore it is not possible to 100% test this code until that security code is re-implemented. For example the workflow for passing the security check might/(probably)will impact the workflow here to enable TLS transfer etc

Sometimes I have the feeling someone just wants to block everything I do.

I'm a bit confused as some of this is not working the same as the setting in global configuration

The option to "verify certificate" in the installation exposes extra fields for the path to the CA file and folder.

The same option in global configuration is a simple boolean yes/no

installation

https://github.com/richard67/joomla-cms/blob/98dfc563ec3e48f010c7ec019bc07fb4a197f2da/installation/forms/setup.xml#L181-L196

global config

sorry I see thats fixed in another pr and that its correct here

It is regularly pointed out that if an option isn't visible then no one will know it is even possible see #20749 for a discussion on this.

This PR falls into the same scenario because if you never change from localhost you would never even know that this stuff is available.

@brianteeman Well this PR behaves like other existing stuff using showon. As the discussion in #20749 shows we still have such hidden functionality in 4.0-dev.

Possible ways out: If there was a "enableon" like there is a "showon" we could use that for enablind/disabling fields, or we always show fields in disabled status when their showon results in false. Then such fields would not be hidden anymore. Or there could be a label or text beside the field which decides which other fields are shown (here in this PR the "Host" field) telling that changing its value may show additional options.

What in fact is a problem and maybe a difference to other hidden features is that the database connection encryption (whether at inslallation with this PR here or in Global Config after installation as already implemented in 4.0-dev) are not documented anywhere, also not in the help screens for the backend, so the functionality is really completely hidden. I plan to work on that soon to have some documentation, but I need to know what to do to update or extend the help screens.

I just did the following test - it seems that it should result in the installation not proceeding

change localhost for 127.0.0.1 to enable the TLS options
selected 2 way
then did nothing else but clicked install

I expected it to fail with authentication error or path error or something
actual result was that joomla was installed and the following values were written in the config. Is this correct?

public $db = 'joomla_db';
public $dbprefix = 'l7gf1_';
public $dbencryption = 2;
public $dbsslverifyservercert = false;
public $dbsslkey = '';
public $dbsslcert = '';
public $dbsslca = '';
public $dbsslcapath = '';
public $dbsslcipher = '';

this PR behaves like other existing stuff using showon

Not really as the showon is usually triggered by a user selecting something first which has a related name ie install languages and then you can see the associations
Here its just hidden and that would be a shame as it would negate your time spent writing this.

@brianteeman So installation was successful? What does System Information in backend say about encryption? Is it enabled? And how does Global Configuration behave, i.e. you do the same in backend, change to 2-way and leave all fields related to it empty?

The desired behavior of the installation and the backend is that connection fails in this case. But I've noticed hasndling of database errors is slightly different between installation and the installed CMS.

It might depend on database settings because I haven't experienced this, but I will test again with all DB's I have.

But you are right, according to my testing instructions it is definitely wrong that the database connection and so the installation succeed.

Note it did fail when i enabled verification but not with verification disabled.

After the "successful install" the global config looks like this (as expected from the configuration.php)

and the system information shows the following (which i believe is the out of the box defaults)

In which case then to me it sounds like all the other fields are (optional) and should be marked as such else its too confusing trying to work out what should go there.

Hmm, on MySQL backend behaves the same, no error if 2-way and nothing entered for client certificate and key. Maybe MySQL falls silently back to 1-way? I have to check what PostgreSQL does.

@brianteeman Ah stupid me, I should have remembered that. The 1-way and 2-way is just a toggle to enable or disable the fields, there is no way to tell the database clients to use this or that. The client decides by the absence or not absence of the parameters if it uses client authentication. I.e. if the fields are empty, 1-way is used in fact, and if client key and cert are specified, 2-way is used. This is true for both MySQL and PostgreSQL. Not sure what's a good way out regarding good UI.

with postgres in the same scenario I got

@brianteeman For PostgreSQL it might be necessary to enable ssl by uncommenting line "ssl = on" in the postgresql.conf file and then restarting the server.

UI suggestions then would be

or maybe adding a note that indicates the following fields are only required if the server settins are not the default

The error message when verify was enabled in mysql suggests that there is a missing mart of the message as highlighted here

@brianteeman Maybe a note telling that 1-way authentication is used if not both of the fields "Path to Private Key File" and "Path to Certificate File" are filled?

If you fill in both fields with invalid paths, 2-way should fail.

honestly I don't know. But if I select option b and then silently get option a then I would call that a bug and as this is a security feature I would say that is not acceptable

@brianteeman

The error message when verify was enabled in mysql suggests that there is a missing mart of the message as highlighted here

This seems to be a general problem of the installation (or the db driver?) with error messages on MySQL and so not related to this PR. On PostgreSQL I get meaning ful error messages at this place.

If you fill in both fields with invalid paths, 2-way should fail.

No it just ignores them as before

Ok, I will investigate what I can do to fix the issues related to db connection encryption here and for the CMS. But the problem with missing details in error messages on MySQL seems to be more general to me.

the only time it fails is when you put garbage in the Supported Cipher Suite field although the error message doesn't indiucate anything useful

I still don't understand how @HLeithner was able to do a successful test

Server certificate verification and 2-way is a bit tricky because of the requirements of the db servers and clients regarding ownership and file system permissions for the key files, and in case of Linux with AppArmous also regarding the location of the key and certificate files on the server. But I have tested all with MySQL and PostgreSQL using separate servers for Joomla (= db client) and the db server, and I was able to make all work, 1-way and 2-way with and without server cert verification. I assume he has done it in the same way, at least that was what he ewas discussing with me on Glip. So I assume he has made a real test.

But it seems we both haven't tested the scenario with 2-way without server cert verification and missing values for client cert and key files.

Its an important part of all testing to see what happens when things don't work as well as when they do ;)

Sure. But nobody is perfect, or are you?

you misunderstood the meaning of my sentence

Anyway thanks for the findings ... I have to think and investigate how to fix it ... maybe there is not really a way to find out if finally 1 or 2 way is used ... stay tuned.

@andrepereiradasilva Since you had the original idea and implemented the feautre in the framework db package: Do you have an idea how to solve the issues found by @brianteeman above? If so, all suggestions are welcome.

@brianteeman For the UI issue "hidden feature if localhost" it could maybe help to show a field or a message when localhost is chosen, telling that localhost will mean what a socket connection is used for which encryption is not possible, and so the encryption related options are not shown, and that this is safe when the local host is safe and that it has performance advantages, but if someone explicitely wants to encrypt also a connection to localhost he/she could use 127.0.0.1 or ::1 to enforce a TCP connection. This would make the feature not be hidden anymore when localhost is chosen. What do you think? And do you think it is possible to tell that in an as short as possible sentence? If so, suggestions are welcome. And what is better: A field (if yes of which type, label or text) with a showon in case of localhost, or a message alert?

The other issues with 1-way and 2-way I have to think about still how to solve that.

Sorry but that idea doesnt make things any clearer to me

@brianteeman As far as I understood one of the issues is that nothing is shown about availability of connection encryption if one has chosen localhost. My idea was to show some hint in that case so one can see there is such feature, and to explain a bit that it is not unsafe or a problem when not using it for localhost. The question was how or where to show that information in the best way.

For the 2-way authentication we can only check if the private key exists, but the server have to enforce the 2-way (client side authentication). That's what I have tested.

You have to required a certificate from the client in the mysql config to let 2-way fail.

And yes you are right we should check if a private key file exists when 2-way is selected, on the code site the database driver doesn't care it is happy if it connects.

About the ui topic, maybe it's better so always show the TLS button and prevent the user from entering localhost (auto convert to 127.0.0.1) if he/she activates it.

For the 2-way authentication we can only check if the private key exists, but the server have to enforce the 2-way (client side authentication). That's what I have tested.

But were you connected as 1 way or 2 way

You have to required a certificate from the client in the mysql config to let 2-way fail.

and thats not something we can test for ?

And yes you are right we should check if a private key file exists when 2-way is selected, on the code site the database driver doesn't care it is happy if it connects.

but only as 1 way?

So should the "verify certificate" option always be checked? Sorry not had time to check if that is a verify client or verify server check.

About the ui topic, maybe it's better so always show the TLS button and prevent the user from entering localhost (auto convert to 127.0.0.1) if he/she activates it.

I agree to always show the tls button - not sure about autoconverting the localhost. maybe that should be just part of the validation test.

@brianteeman

So should the "verify certificate" option always be checked?

No, it should not always be checked because e.g. on my shared hosting (1&1) 1-way authentication works, but not with certificate verification because I don't have any access to the CA certificate.

Sorry not had time to check if that is a verify client or verify server check.

It's a check if the CA of the server certificate fits to the CA certificate . On MySQL the verification can be done with or without an additional check if the host name used for the database connection is equal to the common name (CN) in the server certificate. It could be good switch off the server name check because the certificates coming with a standard installation of MySQL or PostgreSQL have something else than the hostname as CN. But as far as I know up to now, on PostgreSQL the server name and CN are always compared, so currently there is no toggle button to switch that off. Maybe it could make sense to add such button? But this would be one more field being shown only for MySQLi and MySQL (PODO), and it maybe would make things more complicated. Your opinion on this point is welcome. Meanwhile I will check again deeper if PostgreSQL allows to switch off server name check so we could use such new button for all databases.

@brianteeman and @HLeithner Thanks a lot for feedback and findings and ideas.

Maybe it would be easier if I implement changes in my PR #27320 for Global Config, i.e. after installation, or maybe make new draft PRs for that to test some UI changes, and then if that is ok make a new PR for the installation to replace this one here. What do you think? Would this make testing easier? Or shall we continue in this PR here to have all discussions at one place? Please share your opinion.

And yes you are right we should check if a private key file exists when 2-way is selected, on the code site the database driver doesn't care it is happy if it connects.

I will work on implementing the checks if the files (client private key file and client certificate file and in case of certificate verification on also the CA file or folder, if specified) exist. But any help is welcome, e.g. advise if we shall limit the files location to folders below the Joomla root or not. But maybe such things can be easily added later.

About the ui topic, maybe it's better so always show the TLS button and prevent the user from entering localhost (auto convert to 127.0.0.1) if he/she activates it.

Maybe we could clear the "Host" field and show an alert when someone changes from "Default" to "One-way" or "Two-way"? The alert could tell in brief words that encryption will not work with localhost because of socket connection and that 127.0.01 or ::1 (depending on IP version) can be used instead to enforce a TCP connection?

We should not automatically set it to 127.0.0.1 because this will not work on a pure IP v6 system (ok, very unlikely case but theoretically possible).

Sorry I can't really help as I'm now away until the new year at a conference with limited time and internet access

Sorry I can't really help as I'm now away until the new year at a conference with limited time and internet access

@brianteeman No problem, thanks so far, have a merry x-mas and a happy new year and a good conference, and maybe the works on that database connection encryption stuff will take a while so you can continue to help and advise next year.

@HLeithner Regarding check if files or folders exist: We currently have that nowhere in Global Config, neither for log folder nor temp folder nor cache folder or whatever folders and files can be specified there. Not sure if it would be good to have that at one place (db encryption) but nowhere else, and not sure if there are maybe security reasons why we don't check these. And it seems there are no ready validation rules for that. What do you think?

For the 2-way authentication we can only check if the private key exists, but the server have to enforce the 2-way (client side authentication). That's what I have tested.

But were you connected as 1 way or 2 way

Connected 2-way and checked for fail if private key was not supplied

You have to required a certificate from the client in the mysql config to let 2-way fail.

and thats not something we can test for ?

Can't think of, It's a flag in the creation of the database user.

GRANT ALL PRIVILEGES ON joomla.* to 'joomlal'@'%' IDENTIFIED BY '<joomla-password>'
  REQUIRE SUBJECT '/C=AT/ST=Vienna/L=Vienna/O=ITronic/OU=Production/CN=joomla-website';

This would require a certificate on login with an exact subject. But it's also possible just require a certificate with the same CA as the server

GRANT ALL PRIVILEGES ON joomla.* to 'joomla'@'%' IDENTIFIED BY '<joomla-password>' REQUIRE X509;

And yes you are right we should check if a private key file exists when 2-way is selected, on the code site the database driver doesn't care it is happy if it connects.

but only as 1 way?

I'm not sure if the server simple ignores the certificate or not. I would expect that it's just ignored but should be tested.

So should the "verify certificate" option always be checked? Sorry not had time to check if that is a verify client or verify server check.

That's the exact point, it's both, 1-way is "Verify Server" and 2-way is "Verify Server and verify Client", but you can't do only "Verify Client".

About the ui topic, maybe it's better so always show the TLS button and prevent the user from entering localhost (auto convert to 127.0.0.1) if he/she activates it.

I agree to always show the tls button - not sure about autoconverting the localhost. maybe that should be just part of the validation test.

Warning message that it's not possible to use TLS over UDS (which is automatically tried if using "localhost" as name)...

In this case I'm not sure if "Verify Client" is possible at all...

(That's all about MySQL, Pgsql should be similar but I don't know if the pgsql driver does the same magic for UDS with name "localhost")

@HLeithner Regarding check if files or folders exist: We currently have that nowhere in Global Config, neither for log folder nor temp folder nor cache folder or whatever folders and files can be specified there. Not sure if it would be good to have that at one place (db encryption) but nowhere else, and not sure if there are maybe security reasons why we don't check these. And it seems there are no ready validation rules for that. What do you think?

I'm not sure if we really need directory and file for certificate. Automatically decide if "path" is a file or dir should do the same with less confusion...

Automatically decide if "path" is a file or dir should do the same with less confusion...
@HLeithner So or so we don't have any check yet in Global Configuration for existance for other folders or files we specify.

Will that path even be in a space that the web user can access?

Will that path even be in a space that the web user can access?

@brianteeman Yes. If you mean the user of Joomla via browser: No, the file or folder is located on the Joomla web server which is equal to the db client. It is not accesible from the web client = the user's PC.

@HLeithner If the connections fails or not for particular options can be determined by the client using the ssl-mode variable on MySQL (not sure yet if MariaDB supports that, too) or sslmode on PostgreSQL. As far as I could see up to now, only the PostgreSQL (PDO) driver uses this possibility. So I think that's why currently on MySQL things depend on server settings.

The ssl-mode/sslmode variable allows to specify if the host name of the server shall be checked in the server's certificate, too (verify CA), or if not (verify full). For this we don't have an option yet, but it could be useful to control that, too, because the certificates generated by MySQL and PostgreSQL for a standard installation use common names (CN) different to the host name.

See here for a description of the MySQL SSL modes: https://dev.mysql.com/doc/refman/8.0/en/using-encrypted-connections.html, just scroll down to section "Client-Side Configuration for Encrypted Connections".

See here for a description of the PostgreSQL SSL modes: https://ankane.org/postgres-sslmode-explained.

So maybe it could make sense to enhance some of the DB drivers to set that variable in the right way so the 1-way or 2-way value in Global Configuration (or here in the installation form) really controls the behavior when connecting.

Am still investigating.

Update: It seems that MariaDB does not support ssl-mode.

I think I've found a way to solve the issues. Say tuned and wait with testing until I report back here that it is ready. Maybe I will make a new PR to replace this one, maybe I'll modify this one, not sure yet.

Closing in favour of #27348 .