Symfony Doctrine cannot to MariaDB via SSL

Viewed 38

Been trying for days but I get 0 results trying to connect my Symfony 6.1.4 app client to MariaDB 10.9 via SSL

Please note that I CAN connect with MySQL Workbench using SSL and the user app_ssl so I think my certs are fine. Funny thing Sequel Pro does NOT want to connect either using same certs. I don't know why; it gives same error as symfony.

I checked that my user app_ssl has all the permissions on my database mr_dev. I can confirm this in workbench too. here is the user script

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

I have two error types depending on syntax:

PDO:MYSQL_ATTR_SSL_KEY : '%DATABASE_PUB_KEY%'

gives

SQLSTATE[HY000] [1045] Access denied for user 'app_ssl'@'192.168.0.3' (using password: YES)

docker log is

2022-09-13 14:28:30 6 [Warning] Access denied for user 'app_ssl'@'192.168.16.3' (using password: YES)

or

!php/const PDO::MYSQL_ATTR_SSL_KEY : '%DATABASE_PUB_KEY%'

gives

SQLSTATE[HY000] [2002] (trying to connect via (null))

docker log is

2022-09-13 14:26:34 3 [Warning] Aborted connection 3 to db: 'unconnected' user: 'unauthenticated' host: '192.168.16.3' (This connection closed normally without authentication)

here is my current doctrine.yaml file; please not all the commented syntax I tried

doctrine:
    dbal:
        server_version:    'mariadb-10.9.2'
        dbname:            'mr_dev'
        host:              'database'
        port:              '3306'
        user:              'app_ssl'
        password:          'password'
        driver:            'pdo_mysql'
        charset:            UTF8
        options:
            # !php/const PDO::MYSQL_ATTR_SSL_KEY : '%kernel.project_dir%/client-key.pem'
            # !php/const PDO::MYSQL_ATTR_SSL_CERT : '%kernel.project_dir%/client-cert.pem'
            # !php/const PDO::MYSQL_ATTR_SSL_CA : '%kernel.project_dir%/ca-cert.pem'

            # !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)%'

            # PDO:MYSQL_ATTR_SSL_KEY : '%kernel.project_dir%/client-key.pem'
            # PDO:MYSQL_ATTR_SSL_CERT : '%kernel.project_dir%/client-cert.pem'
            # PDO:MYSQL_ATTR_SSL_CA : '%kernel.project_dir%/ca-cert.pem'

            # PDO:MYSQL_ATTR_SSL_KEY : '%env(DATABASE_PUB_KEY)%'
            # PDO:MYSQL_ATTR_SSL_CERT : '%env(DATABASE_PRIV_KEY)%'
            # PDO:MYSQL_ATTR_SSL_CA : '%env(DATABASE_CA_CERT)%'

            !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%'

            # PDO:MYSQL_ATTR_SSL_KEY : '%DATABASE_PUB_KEY%'
            # PDO:MYSQL_ATTR_SSL_CERT : '%DATABASE_PRIV_KEY%'
            # PDO:MYSQL_ATTR_SSL_CA : '%DATABASE_CA_CERT%'

my current %DATABASE_PUB_KEY% is referenced in services.yaml like this

parameters:

    DATABASE_PUB_KEY: '%kernel.project_dir%/client-key.pem'
    DATABASE_PRIV_KEY: '%kernel.project_dir%/client-cert.pem'
    DATABASE_CA_CERT: '%kernel.project_dir%/ca-cert.pem'

when I dump my env with php bin/console debug:container --parameters --env=dev I get correct path like this:

/Users/u/documents/projects/mr/app/ca-cert.pem

So I know the path is correct and symfony should find the file

I don't know what to do to debug this further. I tried everything I could find everywhere so far

1 Answers

I had to add a new parameter to make it work. Not solving the root of the issue but a workaround for now. If someone can provide a real solution instead. Here is my bad fix :

            !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
Related