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.

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 .