Conquering the “Access Denied” Beast: A Step-by-Step Guide to MariaDB, SSL, Symfony 7, and Docker

Are you tired of encountering the frustrating “Access denied for user ‘app_ssl’@’172.27.0.3’ (using password: YES)” error when trying to connect to your MariaDB database using Symfony 7 and Docker? Well, fear not, dear developer! In this comprehensive guide, we’ll take you by the hand and walk you through the process of setting up a secure MariaDB connection using SSL, Symfony 7, and Docker.

What You’ll Need

Before we dive into the nitty-gritty, make sure you have the following ingredients in your toolbox:

• Docker installed on your system
• Symfony 7 project set up and running
• MariaDB installed and running (either locally or on a remote server)
• A basic understanding of Docker, Symfony, and MariaDB (don’t worry, we’ll cover the basics as we go)

Step 1: Generate SSL Certificates for MariaDB

In this step, we’ll generate the SSL certificates required for encrypting the connection between your Symfony 7 application and the MariaDB database.

openssl req -x509 -newkey rsa:4096 -nodes -keyout mariadb-ssl-key.pem -out mariadb-ssl-cert.pem -days 365 -subj "/C=US/ST=State/L=Locality/O=Organization/CN=localhost"

This command generates a self-signed certificate and private key for MariaDB. You can adjust the parameters as needed (e.g., changing the country code or organization name).

Step 2: Configure MariaDB for SSL

Now that we have our SSL certificates, let’s configure MariaDB to use them.

[mysqld]
# Enable SSL/TLS connection
ssl-ca=/path/to/mariadb-ssl-cert.pem
ssl-cert=/path/to/mariadb-ssl-cert.pem
ssl-key=/path/to/mariadb-ssl-key.pem

[client]
# Enable SSL/TLS connection for clients
ssl-ca=/path/to/mariadb-ssl-cert.pem
ssl-cert=/path/to/mariadb-ssl-cert.pem
ssl-key=/path/to/mariadb-ssl-key.pem

Update your MariaDB configuration file (usually my.cnf or my.ini) with the above settings, specifying the paths to your generated SSL certificates.

Step 3: Create a Docker Compose File

Create a new file named docker-compose.yml in the root of your Symfony 7 project:

version: '3'
services:
  mariadb:
    image: mariadb:10.6
    environment:
      - MYSQL_ROOT_PASSWORD=password
      - MYSQL_DATABASE=app_database
      - MYSQL_USER=app_ssl
      - MYSQL_PASSWORD=password
    volumes:
      - ./mariadb-ssl-key.pem:/etc/mysql/ssl/mariadb-ssl-key.pem
      - ./mariadb-ssl-cert.pem:/etc/mysql/ssl/mariadb-ssl-cert.pem
    ports:
      - "3306:3306"

This Docker Compose file sets up a MariaDB container with the required environment variables and volumes for SSL certificates.

Step 4: Update Your Symfony 7 Configuration

In your Symfony 7 project, update the config/packages/doctrine.yaml file:

doctrine:
  dbal:
    url: '%env(DB_URL)%'
    driver: 'pdo_mysql'
    server_version: 'mariadb-10.6.4'
    charset: utf8mb4
    default_table_options:
      charset: utf8mb4
      collate: utf8mb4_unicode_ci
  orm:
    auto_generate_proxy_classes: true
    naming_strategy: doctrine.orm.naming_strategy.underscore_number_aware
    mappings:
      App:
        is_bundle: false
        type: annotation
        dir: '%kernel.project_dir%/src/Entity'
        prefix: 'App\Entity'
        alias: App

env:
  DB_URL: 'mysql://app_ssl:[email protected]:3306/app_database?sslmode=REQUIRED'

docker-compose up -d

php bin/console doctrine:database:create