Cannot connect with mariaDB and SSL with pdo_mysql

[signmeuptwice]

Q	A
DBAL 4.0.4

Hello, I tried asking on slack but no answer

I have DBAL 4.04 and ORM 3

since then I noticed there is no ssl reference for the pdo_mysql driver in https://doctrine-dbal.readthedocs.io/en/4.0.x/reference/configuration.html

I cannot connect my symfony application running on docker to mariadb using ssl.
It Works fine without SSL (using user app instead of app_ssl)
Also used to work fine before upgrading from symfony 6 to 7

the certs are fine since they used to work before updating from SF6 to 7

symfony is based on php8.3

symfony error:

An exception occurred in the driver: SQLSTATE[HY000] [1045] Access denied for user 'app_ssl'@'172.27.0.3' (using password: YES)

database log error:

2024-07-02 12:25:19 424 [Warning] Access denied for user 'app_ssl'@'172.27.0.3' (using password: YES)

docker compose

database:
    container_name: mariadb
    image: "mariadb:10.9.2"
    restart: always
    env_file: .env
    volumes:
      - "${SQL_INIT}:/docker-entrypoint-initdb.d"
      - type: bind
        source: ${MARIADB_DATA_DIR}
        target: /var/lib/mysql
      - type: bind
        source: ${MARIADB_LOG_DIR}
        target: /var/logs/mysql
      - type: bind
        source: ${MARIADB_CERTS_DIR}
        target: /etc/certs/
      - type: bind
        source: /etc/localtime
        target: /etc/localtime:ro
      - type: bind
        source: ${MARIADB_BACKUP_DIR}
        target: /opt/mysql/backup
      - type: bind
        source: ${MARIADB_BACKUP_SCRIPT_DIR}
        target: /etc/periodic/daily
    ports:
      - "3306:3306"
    networks:
      app_network:
    command:
      [
        "--require_secure_transport=OFF",
        "--ssl-key=/etc/certs/server-key.pem",
        "--ssl-cert=/etc/certs/server-cert.pem",
        "--ssl-ca=/etc/certs/ca-cert.pem",
      ]

.env

DATABASE_VERSION=mariadb-10.9.2
DATABASE_NAME=db_dev
DATABASE_PORT=3306
DATABASE_DRIVER=pdo_mysql
DATABASE_HOST=database
DATABASE_USER=app_ssl
DATABASE_PASSWORD=pass
DATABASE_PUB_KEY="/etc/certs/client-key.pem"
DATABASE_PRIV_KEY="/etc/certs/client-cert.pem"
DATABASE_CA_CERT="/etc/certs/ca-cert.pem"

users

 select User, ssl_type, ssl_cipher, host from mysql.user;
+-------------+----------+------------+-----------+
| User        | ssl_type | ssl_cipher | Host      |
+-------------+----------+------------+-----------+
| mariadb.sys |          |            | localhost |
| root        |          |            | localhost |
| root        |          |            | %         |
| app         |          |            | %         |
| app_ssl     | ANY      |            | %         |
+-------------+----------+------------+-----------+
5 rows in set (0.016 sec)

SHOW VARIABLES LIKE '%ssl%';
+---------------------+----------------------------+
| Variable_name       | Value                      |
+---------------------+----------------------------+
| have_openssl        | YES                        |
| have_ssl            | YES                        |
| ssl_ca              | /etc/certs/ca-cert.pem     |
| ssl_capath          |                            |
| ssl_cert            | /etc/certs/server-cert.pem |
| ssl_cipher          |                            |
| ssl_crl             |                            |
| ssl_crlpath         |                            |
| ssl_key             | /etc/certs/server-key.pem  |
| version_ssl_library | OpenSSL 3.0.2 15 Mar 2022  |
+---------------------+----------------------------+

MariaDB [(none)]> \s
--------------
mysql  Ver 15.1 Distrib 10.9.2-MariaDB, for debian-linux-gnu (aarch64) using  EditLine wrapper

Connection id:      15
Current database:
Current user:       root@localhost
SSL:            Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server:         MariaDB
Server version:     10.9.2-MariaDB-1:10.9.2+maria~ubu2204 mariadb.org binary distribution
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:        /run/mysqld/mysqld.sock
Uptime:         1 hour 33 min 32 sec

symfony doctrine.yaml

doctrine:
    dbal:
        server_version:    '%env(DATABASE_VERSION)%'
        dbname:            '%env(DATABASE_NAME)%'
        host:              '%env(DATABASE_HOST)%'
        port:              '%env(DATABASE_PORT)%'
        user:              '%env(DATABASE_USER)%'
        password:          '%env(DATABASE_PASSWORD)%'
        driver:            '%env(DATABASE_DRIVER)%'
        charset:            UTF8
        options:
            !php/const PDO::MYSQL_ATTR_SSL_KEY: '%env(DATABASE_PUB_KEY)%'
            !php/const PDO::MYSQL_ATTR_SSL_CERT: '%env(DATABASE_PRIV_KEY)%'
            !php/const PDO::MYSQL_ATTR_SSL_CA: '%env(DATABASE_CA_CERT)%'
            !php/const PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT: false

my user has correct permissions and was created like this:

GRANT ALL ON db_dev.* TO 'app_ssl'@'%' IDENTIFIED BY 'pass' REQUIRE SSL;
GRANT USAGE ON *.* TO `app_ssl`@`%`;
GRANT ALL PRIVILEGES ON `db_dev`.* TO `app_ssl`@`%`;
FLUSH PRIVILEGES;

[derrabus]

since then I noticed there is no ssl reference for the pdo_mysql driver in https://doctrine-dbal.readthedocs.io/en/4.0.x/reference/configuration.html

Please find our documentation here: https://www.doctrine-project.org/projects/doctrine-dbal/en/4.0/reference/configuration.html

---

Are you able to connect to that database using PDO directly?

[signmeuptwice]

Are you able to connect to that database using PDO directly?

Not sure what you mean by PDO directly sorry but it used to work prior of updating (doctrine 2 and dbal 3) so Im thinking something must have changed with the new version (doctrine 3 and dbal 4). I have always used pdo_mysql driver with doctrine.
I can connect if I do not use SSL with app user instead of app_ssl

Thank you for the doc link but it seems to be the same I posted. I cannot see any SSL argument for ssl configuration like mysqli for example

[signmeuptwice]

Not sure if that helps but I noticed that in my doctrine.yaml

if I have this syntax

        options:
            PDO::MYSQL_ATTR_SSL_KEY: '%DATABASE_PUB_KEY%'
            PDO::MYSQL_ATTR_SSL_CERT: '%DATABASE_PRIV_KEY%'
            PDO::MYSQL_ATTR_SSL_CA: '%DATABASE_CA_CERT%'
            PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT: false

I have this error

SQLSTATE[HY000] [1045] Access denied for user 'app_ssl'@'192.168.240.3'

and with this syntax

        options:
            !php/const PDO::MYSQL_ATTR_SSL_KEY: '%DATABASE_PUB_KEY%'
            !php/const PDO::MYSQL_ATTR_SSL_CERT: '%DATABASE_PRIV_KEY%'
            !php/const PDO::MYSQL_ATTR_SSL_CA: '%DATABASE_CA_CERT%'
            !php/const PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT: false

I have this error

SQLSTATE[HY000] [2002] Cannot connect to MySQL using SSL

So Im thinking the correct syntax is without !php/const ?

[derrabus]

Are you able to connect to that database using PDO directly?

Not sure what you mean by PDO directly

This is PDO: https://www.php.net/manual/en/book.pdo.php

Are you able to connect to your database using only PDO?

sorry but it used to work

You said that already. But right now, there are many moving parts involved. I'm trying to narrow down the problem which is why I'm asking questions.

prior of updating (doctrine 2 and dbal 3)

Now I'm confused. In your initial post, you said, the problem appeared after upgrading Symfony from 6 to 7, now it's the ORM and DBAL that you've upgraded.

Thank you for the doc link but it seems to be the same I posted.

No, it is a different link. I'm pretty sure about that. 🙂

I cannot see any SSL argument for ssl configuration like mysqli for example

This is because we officially don't support SSL connections through PDO and we never have. I realize that it used to work through setting custom PDO driver options, but we don't have any tests for that scenario. That being said, I don't see any reason why this should have stopped working.

if I have this syntax

        options:
            PDO::MYSQL_ATTR_SSL_KEY: '%DATABASE_PUB_KEY%'
            PDO::MYSQL_ATTR_SSL_CERT: '%DATABASE_PRIV_KEY%'
            PDO::MYSQL_ATTR_SSL_CA: '%DATABASE_CA_CERT%'
            PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT: false

I'm pretty sure that this has never worked with any Symfony version. This is also not what you wrote in your initial post.

and with this syntax

        options:
            !php/const PDO::MYSQL_ATTR_SSL_KEY: '%DATABASE_PUB_KEY%'
            !php/const PDO::MYSQL_ATTR_SSL_CERT: '%DATABASE_PRIV_KEY%'
            !php/const PDO::MYSQL_ATTR_SSL_CA: '%DATABASE_CA_CERT%'
            !php/const PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT: false

I have this error

SQLSTATE[HY000] [2002] Cannot connect to MySQL using SSL

Which is again a completely different error message than in your initial post. But this looks more like the error message that I would've expected.

---

Okay, you apparently changed a lot of things at the same time, including various dependencies and config files. And I have no idea what else has changed in your app or setup because your answers are inconsitent.

My recommendation would be to take a step back, roll back to the point where your application still worked, repeat your changes in baby steps and test your app after each step.

[signmeuptwice]

@derrabus Thank you for taking the time trying to help me out
Yes I know I got a lot of change here. I updated SF from 6 to 7 and doctrine from 2 to 4 and DBAL from 3 to 4

I assure you that PDO was working with ssl on SF 6 with doctrine 2 and dbal 3

sorry for the options change but that was me just trying to rule out a bad ssl connection.

You say that PDO is not officially supported so I just installed mysqli and tried to go this route but honestly Im so confused
just reading the doc and thinking it is supported I changed my config like this:

    dbal:
        server_version:    '%env(DATABASE_VERSION)%'
        dbname:            '%env(DATABASE_NAME)%'
        host:              '%env(DATABASE_HOST)%'
        port:              3306
        user:              '%env(DATABASE_USER)%'
        password:          '%env(DATABASE_PASSWORD)%'
        driver:            'mysqli'
        charset:            UTF8
        ssl_key: '%DATABASE_PUB_KEY%'
        ssl_cert: '%DATABASE_PRIV_KEY%'
        ssl_ca: '%DATABASE_CA_CERT%'

but Im now getting

Unrecognized options "ssl_key, ssl_cert, ssl_ca" under "doctrine.dbal.connections.default". Did you mean "sslkey", "sslcert"?

the doc says ssl_key and ssl_cert

nermind I changed it to sslcert and sslkey but then I get

Unrecognized option "ssl_ca" under "doctrine.dbal.connections.default". Available options are "MultipleActiveResultSets", "application_name", "auto_commit", "charset", "connectstring", "dbname", "dbname_suffix", "default_dbname", "default_table_options", "disable_type_comments", "driver", "driver_class", "host", "instancename", "keep_replica", "keep_slave", "logging", "mapping_types", "memory", "options", "override_url", "password", "path", "persistent", "platform_service", "pooled", "port", "profiling", "profiling_collect_backtrace", "profiling_collect_schema_errors", "protocol", "replicas", "result_cache", "schema_filter", "schema_manager_factory", "server", "server_version", "service", "servicename", "sessionMode", "slaves", "sslcert", "sslcrl", "sslkey", "sslmode", "sslrootcert", "unix_socket", "url", "use_savepoints", "user", "wrapper_class".

as you can see that does not match the doc

tried changing ssl_ca for sslcrl or sslrootcert but no luck at all..

is the doc wrong ?

on a side note I also had to input the port in the conf file as it was not recognized as an INT from the env file.

[derrabus]

is the doc wrong ?

No. You're reading the documentation of DBAL, but you're writing configuration for DoctrineBundle. The bundle might use different naming for some settings or not support certain settings. I'm on a phone right now and can't look this up for you though.

[signmeuptwice]

Ho I see you are right; they are postgre specific anyway
need to find a configuration reference with mysqli and SSL for the doctrine bundle then