MySQL + SSL + Doctrine

Enabling and enforcing SSL connection on MySQL is easy: Just generate the certificates and configure the server to require secure transport. And it’s even easier on the Azure Database for MySQL.

Just go to the Azure portal and enable “Enforce SSL connection” in the “Connection security” of the database server. Then, download the certificates and stuff them in your application. No problem — just two simple steps.

On a hackathon, we’d just download the certificate, put it in a random folder, and move on. In reality, we’re in a bit different situation then. We’d have three (so far) services connecting to the server (using independent databases), and this setup repeats itself in several independent stacks and some of those stacks are Azure stacks and some are AWS stacks. The services themselves run in Kubernetes. If we simplify (i.e.; omit a hundred other services and resources), it looks like this:

The rest of the story is all about Service 1 :)

Step 1 — Store the certificate somewhere

Given the above, it’s out of the question to store the certificate with the application, because the certificate is not constant. The service application itself doesn’t care whether it’s running in AWS or Azure or what server it is connecting to. Also the application itself is completely unaware of all the stacks it runs on. This means that the certificate must be supplied to the application from outside.

All our deployments are fully automated with Azure pipelines and happen several times a day. One option is to store the certificate in the pipeline variables and then pass it around in environment variables. This is possible but can be somewhat error-prone. The certificate contains special characters, so it needs to be encoded and decoded and if you mess up, debugging it is hard, because all you get is “Invalid certificate”.

To avoid this we took advantage of the fact that the services run in a Kubernetes cluster. An so we went with the option of mounting the certificate as a config map. This requires:

• downloading the certificate,
• using kubectl to generate a config map from file,
• applying the config map.

Condensed, it’s the following script:

# Root SSL cert for Azure MySQL
curl https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt.pem 
--location --output /tmp/BaltimoreCyberTrustRoot.crt.pem
kubectl create configmap mysql-root-ca-cert 
--from-file=MySQLRootCACert.pem=/tmp/BaltimoreCyberTrustRoot.crt.pem  --dry-run=client -o yaml | kubectl apply -n default -f -

All of the above happens in our infrastructure deploy pipeline, which makes sure that all the shared resources for the applications are initialized properly.

Step 2 — Get the certificate to the application

This one is very straightforward. We just mount the config map as a volume:

volumes:
- name: mysql-root-ca-cert
  configMap:
    name: mysql-root-ca-cert
    items:
    - key: MySQLRootCACert.pem
      path: MySQLRootCACert.pem

Then mount the volume in the application container:

volumeMounts:
- name: mysql-root-ca-cert
  mountPath: /code/var/mysql

The application will now have the proper certificate available in /code/var/mysql/MySQLRootCACert.pem .

Step 3 — Configure the application to use the certificate

Now we’re getting to the interesting part. Our applications are written in PHP and use Doctrine DBAL to connect to the database. The doc is a bit skimpy about setting the SSL options, it just says that you should use the options field to supply the PDO driver options. Luckily, there is a Stack overflow post that gives a hint — and we confirmed that this works:

dbal:
        url: 'mysql://user:password@dbhost:3306/dbname'
        server_version: '8.0'
        options:
            1012: '/code/var/mysql/MySQLRootCACert.pem'

The magic value of 1012 represents the constant PDO::MYSQL_ATTR_SSL_CA , which is quite crazy, because constants were invented for a reason. And yes, the value is actually different for different PHP versions. Luckily, we also use Symfony, which comes to the rescue by supporting PHP constants in YAML configurations; therefore I can write:

dbal:
        url: 'mysql://user:password@dbhost:3306/dbname'
        server_version: '8.0'
        options:
            !php/const:PDO::MYSQL_ATTR_SSL_CA: 'path/file.pem'

That’s great. The supplied values are of course variables, so the real doctrine.yaml looks like this:

dbal:
        url: 'mysql://%env(DATABASE_USER)%:%env(DATABASE_PASSWORD)%@%env(DATABASE_HOST)%:%env(DATABASE_PORT)%/%env(DATABASE_NAME)%?serverVersion=8.0'
        server_version: '8.0'
        options:
            !php/const:PDO::MYSQL_ATTR_SSL_CA: '%env(DATABASE_SSL_CA_PATH)%'

We then supply the environment variables through the Kubernetes template of the application; the abbreviated template looks like this:

---
kind: Deployment
apiVersion: apps/v1
metadata:
  name: app-name
  namespace: default
  labels:
    app: app-name
spec:
  replicas: 2
  selector:
    matchLabels:
      app: app-name
  template:
    metadata:
      labels:
        app: app-name
   spec:
      containers:
      - name: app-name
        image: app-repository:app-image
        ports:
        - containerPort: 80
        env:
        - name: DATABASE_HOST
          valueFrom:
            configMapKeyRef:
              name: app-name
              key: dbHost
        - name: DATABASE_PORT
          valueFrom:
            configMapKeyRef:
              name: app-name
              key: dbPort
        - name: DATABASE_USER
          valueFrom:
           configMapKeyRef:
             name: app-name
             key: dbUser
        - name: DATABASE_PASSWORD
          valueFrom:
            secretKeyRef:
              name: app-name-credentials
              key: dbPassword
        - name: DATABASE_NAME
          valueFrom:
            configMapKeyRef:
              name: app-name
              key: dbName
        - name: DATABASE_SSL_CA_PATH
          valueFrom:
            configMapKeyRef:
              name: app-name
              key: dbCertificatePath
       
        volumeMounts:
        - name: mysql-root-ca-cert
          mountPath: /code/var/mysql
      volumes:
        - name: mysql-root-ca-cert
          configMap:
            name: mysql-root-ca-cert
            items:
            - key: MySQLRootCACert.pem
              path: MySQLRootCACert.pem
---

This is accompanied with the corresponding config map and secret created at the deploy time.

Step 4 — Configure the application for local development

Once the application is working in production, it’s time to fix it so that the local development is as easy as possible. This also includes running the CI pipelines of the application. The pipeline contains functional tests that require a working database connection and these are run on every push to the application repository. There are several main options:

• Have an Azure MySQL server running for development purposes (this costs money).
• Spin up a new Azure MySQL server each time for development (this takes time — the whole pipeline runs for 5 minutes and spinning up a new MySQL server can take even more than that).
• Spin up a local MySQL server for the purpose of development or the CI pipeline (this needs some thinking).

Not liking to waste time or money we went with the third approach some time ago. The tests run in containers, so both the CI pipeline and the developer can run them the same way using:

docker-compose run --rm tests composer ci

The docker-compose.yml makes sure that a MySQL server exists:

version: "3"
services:
tests: &tests
    image: keboola/job-queue-internal-api
    build:
      context: .
      dockerfile: Dockerfile
    ports:
      - "81:80"
    environment:
      - DATABASE_USER=root
      - DATABASE_PASSWORD
      - DATABASE_HOST=mysql
      - DATABASE_PORT=3306
      - DATABASE_NAME=jobs
    depends_on:
      - mysql
mysql:
    image: mysql:8
    command: --default-authentication-plugin=mysql_native_password
    ports:
      - "3308:3306"
    environment:
      - MYSQL_DATABASE=jobs
      - MYSQL_ROOT_PASSWORD=${DATABASE_PASSWORD}

All the developer (or the CI pipeline) needs to do is set the DATABASE_PASSWORD environment to some random password which is then supplied to both containers. Simple & Effective™. Also it has the benefit of the tests being stateless.

The SSL requirement disturbs this. Once the MYSQL_ATTR_SSL_CA option is set, there must be a certificate and it must be valid. If it is not, you get:

Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[HY000] [2002]

That’s basically the equivalent of “go fuck yourself!” However, if you were really good last year and Santa likes you, you might get something similar to:

PDOException: PDO::__construct(): SSL operation failed with code 1. OpenSSL Error messages:
 error:0407008A:rsa routines:RSA_padding_check_PKCS1_type_1:invalid padding
 error:04067072:rsa routines:rsa_ossl_public_decrypt:padding check failed
 error:0D0C5006:asn1 encoding routines:ASN1_item_verify:EVP lib
 error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed

That at least gives some idea that the problem is in the certificate.

First I went with the idea that I will generate the certificates mys(QL)elf. This was not difficult, but I ended up with self-signed certificates. These are ok for MySQL, but are not ok for PHP, because it uses strict checking by default (which is a good thing actually!). The option is to turn off the strict checking for local development and the CI pipeline. Remember that the test server is short lived — it exists only during the tests — so this doesn’t pose a security issue.

The option to disable certificate checking luckily exists and is available using the MYSQL_ATTR_SSL_VERIFY_SERVER_CERT constant. I modified the doctrine.yml to:

dbal:
        url: 'mysql://%env(DATABASE_USER)%:%env(DATABASE_PASSWORD)%@%env(DATABASE_HOST)%:%env(DATABASE_PORT)%/%env(DATABASE_NAME)%?serverVersion=8.0'
        server_version: '8.0'
        options:
            !php/const:PDO::MYSQL_ATTR_SSL_CA: '%env(DATABASE_SSL_CA_PATH)%'
            !php/const:PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT: '%env(DATABASE_SSL_VERIFY_ENABLED)%'

However that raises a bit of a red flag.

Having to set an option to enable certificate verification looks like a bad idea, because it implies that by default it is disabled. Luckily this is not entirely true, because the SSL connection is enforced by the configuration of the production MySQL server.

But doppelt hält besser. I would like to have an environment variable DATABASE_SSL_VERIFY_ENABLED that is enabled by default, but can be overridden to be disabled. Luckily Symfony comes to the rescue again with the possibility to set default values for environment variables. All it needs is you to set the corresponding environment in services.yml in the parameters section:

parameters:
    locale: en
    app_name: my-app
    env(DATABASE_SSL_VERIFY_ENABLED): '1'
services:
    ...

This is a nice little bit of magic that makes sure that if the environment DATABASE_SSL_VERIFY_ENABLED is not set, Symfony will set it to the value 1 . While, if the variable is set, Symfony will leave it alone. With this — I leave DATABASE_SSL_VERIFY_ENABLED undefined and only configure it locally or in a CI pipeline to successfully connect to the ephemeral MySQL server. It’s worth noting that in that case, the value of MYSQL_ATTR_SSL_CA becomes completely irrelevant — the referenced file does not even have to exist.

Done. Problem Solved. I drew the owl.

P.S. Alternative solution

The obvious question with disabling SSL is — why we didn’t use the Symfony environments. The answer to that is — we are managing the application environments on a higher level — in Kubernetes. The application runs in several different production environments as well as in several different test and development environments none of which care about the application and all of which are unknown to the application. For example, we use the same production Kubernetes template to run service application A within the tests of application B. Application A mimics the production environment (and is configured so) and application B runs its test suite. That means that switching the application environment via APP_ENV makes this actually more complicated, because it applies to one application within the CI pipeline and not to the other. Plus, we’d have to generate the .env files. Contrary to that, the environment variable DATABASE_SSL_VERIFY_ENABLED is application agnostic — it’s on the same level as the database credentials and says that the given MySQL server does not take SSL too seriously.

P.S. Additional craziness

In an attack of euphoria, I also set the client certificate connection options:

options:
 !php/const PDO::MYSQL_ATTR_SSL_CA : 'MySQLRootCACert.pem'
 !php/const PDO::MYSQL_ATTR_SSL_KEY : 'client-key.pem'
 !php/const PDO::MYSQL_ATTR_SSL_CERT : 'client-cert.pem'

With the SSL on Azure MySQL server, you shouldn’t set those, because you have neither the client key nor the certificate. The thing is, if you do set these, nothing happens. The client will see them as invalid and simply ignore the value. That is, unless you manage to set them so that the referenced files actually exist! In that case, you will get:

Which is a completely off-track error. If you’re lucky, you might also get a:

SSL operation failed with code 1. OpenSSL Error messages:error:14094418:SSL routines:ssl3_read_bytes:tlsv1 alert unknown ca

Which is slightly better, but points at the wrong offender. The CA is OK, it’s the client certificate that does not match in this case. The resolution is simple — follow the docs and configure only the CA certificate.