dbal
dbal copied to clipboard
doctrine
Cannot connect with mariaDB and SSL with pdo_mysql
| 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;
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?
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
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 ?
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
mysqlifor 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: falseI 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.
@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.
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.
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