Akeneo PIM is a PHP/Symfony web application that uses MySQL for persistence, and Elasticsearch for search capability. From an abstract perspective it consists of three major components:
- · MySQL, a relational database for storing data
- · Elasticsearch, a search engine for indexing
- · PHP/Symfony backend served by Apache2
Accordingly, you may find yourself in the position of hosting MySQL on a different machine from PHP/Symfony+Apache2; either a host you maintain for MySQL, or a MySQL database service in the cloud. Once you move the database portion of the app to external host, you’ll need to secure it with SSL. But it already uses SSL you say? Well, yes and no.
The MySQL command line interface (CLI), will establish an SSL connection if:
- · When MySQL was installed, the installation software automatically generated certificates
- · You don’t care about a man-in-the-middle attack
If you execute the MySQL CLI with the –ssl-mode flag [‘DISABLED’,’PREFERRED’,’REQUIRED’,’VERIFY_CA’,’VERIFY_IDENTITY’] set to REQUIRED, and it is not possible to establish an SSL connection, it will error out.
If you then execute the CLI with the –ssl-mode flag set to VERIFY_CA, and the Certificate Authority (CA) certificate you have specified on the command line does not match the CA certificate that was used to generate the mysqld server’s certificate on the mysql database host, it will error out.
If you then execute the CLI with the –ssl-mode flag set to VERIFY_IDENTITY, and the CA certificate you have specified on the command line does match the CA certificate that was used to generate the mysqld server’s certificate on the mysql database host, but the server’s certificate does not match the host’s name, it will error out.
This last level of SSL identity verification eliminates the possibility of the man-in-the-middle attack.
Guess what? The PHP MySQL database driver, extension mysqld, doesn’t work the same way. It’s not the MySQL CLI. It’s software that has its own behaviors.
Like the CLI, if it is not required to establish an SSL connection, it will try, but if it fails, give you an unsecured connection. That’s unacceptable if you require a secure connection to an external host.
With PHP you need to require and verify that you have an SSL connection. To do that, you must pass a copy of the database server’s (host) CA certificate to the driver, and a flag to force it to verify the server’s key, against that CA certificate.
So, follow along as I explain each step of the process of requiring and verifying SSL.
On the MySQL Host
To be able to verify the server’s SSL certificate from PHP, you need to start by generating new certificate on your MySQL server’s host. You can generate a certificate request for a public certificate authority or create a self-signed CA certificate. Here, we will be using a self-signed certificate, because doing so helps me explain some of the more intricate details that matter.
NOTE: When generating the server’s certificate, the certificate’s common name (CN) must match the database server’s host plus the fully qualified domain name.
Rather than type sudo over and over, I like to become the root user by doing:
$ sudo -u root mysql -p
Now, the rest of the commands I execute will be as the root user, thus prefixed with #, until I exit.
Check the Host Name
First, let’s see what the current hostname is:
# hostname
ubuntu
If I need to change the host name, I can edit the file /etc/hostname, or use hostnamectl. Here, I’m going to set the host name to rpi4-8g-mysql:
# hostnamectl set-hostname rpi4-8g-mysql
Check the Domain Name
Now that I have the host name set, let’s check the domain name. I’ll use the hostname command again, but add a flag to include the fully qualified domain name:
# hostname --fqdn
rpi4-8g-mysql
Since a domain name is not the suffix of the host name above, I know that the domain name has not been set. On this machine, with O.S. Ubuntu 20, I can set the domain name by editing /etc/systemd/resolved.conf, specifying the domain after the Domains keyword and then restarting resolved:
# vim /etc/systemd/resolved.conf
[Resolve]
DNS=192.168.0.1
FallbackDNS=8.8.8.8
Domains=donaldbales.com
# systemctl restart systemd-resolved.service
Next, let’s verify the host and domain name are correct:
# hostname --fqdnrpi4-8g-mysql.donaldbales.com
Generate the Self-signed Certificate
Now that the machine has its permanent host and domain name, I can proceed to generating a self-signed certificate for SSL.
First, I want to be sure openssl is installed, because I’m going to use that to generate the certificate:
# which openssl
/usr/bin/openssl
# openssl version
OpenSSL 1.1.1f 31 Mar 2020
On this machine, O.S. Ubuntu 20, the mysql data directory is: /var/lib/mysql. So, I’m going to change to that directory, stop MySQL and generate new certificates:
# cd /var/lib/mysql
# systemctl stop mysqld
# mkdir newcerts
# cd newcerts/
# export HOSTANDFQDNAME=`hostname --fqdn`
# export SUBJPREFIX='/C=US/ST=Arizona/L=Sedona/O=Donald Bales'
# openssl genrsa 2048 > ca-key.pem
Generating RSA private key, 2048 bit long modulus (2 primes)
..............................+++++
..............+++++ e is 65537 (0x010001)
# openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem \
-subj "${SUBJPREFIX}/CN=${HOSTANDFQDNAME}-ca-cert"
# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem \
-subj "${SUBJPREFIX}/CN=${HOSTANDFQDNAME}"
Ignoring -days; not generating a certificateGenerating a RSA private key
...............+++++
.................................................................
...........................................................+++++
writing new private key to 'server-key.pem'
# openssl rsa -in server-key.pem -out server-key.pem writing RSA key
# openssl x509 -req -in server-req.pem -days 3600 \
-CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Signature ok
subject=C = US, ST = Arizona, L = Sedona, O = Donald Bales, CN = rpi4-8g-mysql.donaldbales.com
Getting CA Private Key
# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem \
-subj "${SUBJPREFIX}/CN=${HOSTANDFQDNAME}"
Ignoring -days; not generating a certificateGenerating a RSA private key
......................................................+++++
.........................................................+++++
writing new private key to 'client-key.pem'
# openssl rsa -in client-key.pem -out client-key.pem
writing RSA key
# openssl x509 -req -in client-req.pem -days 3600 \
-CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
Signature ok
subject=C = US, ST = Arizona, L = Sedona, O = Donald Bales, CN = rpi4-8g-mysql.donaldbales.com
Getting CA Private Key
# openssl verify -CAfile ca.pem server-cert.pem client-cert.pem
server-cert.pem: OK
client-cert.pem: OK
# openssl x509 -text -in ca.pem
Certificate:
Data:
Version: 3 (0x2)
Serial Number:
3c:df:eb:ed:7b:cc:3f:64:aa:2c:5b:7b:6f:8e:06:e8:39:2b:b3:de
Signature Algorithm: sha256WithRSAEncryption
Issuer: C = US, ST = Arizona, L = Sedona, O = Donald Bales, CN = rpi4-8g-mysql.donaldbales.com-ca-cert
Validity
Not Before: Aug 14 00:59:16 2020 GMT
Not After : Jun 23 00:59:16 2030 GMT
Subject: C = US, ST = Arizona, L = Sedona, O = Donald Bales, CN = rpi4-8g-mysql.donaldbales.com-ca-cert
Subject Public Key Info:
Public Key Algorithm: rsaEncryption
RSA Public-Key: (2048 bit)
Modulus:
00:df:db:d4:65:ec:19:1d:53:dd:67:7a:11:e8:1c:
c0:ff:c7:87:6a:ae:36:56:56:f2:9e:9f:e3:9d:c8:
16:cd:a1:05:80:83:7f:7b:7e:51:fd:97:4d:da:d4:
3f:76:ce:65:d9:d8:9d:b3:e1:6f:23:a7:70:79:97:
22:8f:21:99:f4:a4:14:f6:3b:ef:fb:65:dd:26:75:
7a:d6:e5:2d:cb:86:bc:1b:11:c7:1b:08:e4:08:f9:
cd:f1:55:9a:16:31:a3:52:36:4b:ec:8c:e2:3a:7d:
e5:32:5f:36:b7:79:26:8d:23:4d:4f:c3:02:70:92:
9b:f5:30:4a:bd:ef:07:b9:ba:49:ca:93:e9:53:a1:
df:94:d7:80:92:f0:c9:10:8a:39:48:3a:be:de:5f:
bc:b1:a8:7d:21:a9:b3:b3:15:99:07:7c:d8:b5:75:
f9:78:66:b9:06:6e:dc:e2:35:70:7f:3c:50:ea:67:
ce:92:0e:d2:22:be:4e:34:9e:60:c7:bd:1d:48:03:
15:bc:f3:93:5b:76:36:20:81:c7:90:5a:06:d1:90:
60:81:66:cd:32:d0:6e:f0:b5:6b:5c:33:0c:f0:60:
98:0b:6d:6d:cc:f4:5e:fa:a1:a0:13:a9:43:dd:32:
0d:4a:c8:ad:67:7b:87:da:a6:55:73:7a:f3:61:13:
9c:a1
Exponent: 65537 (0x10001)
X509v3 extensions:
X509v3 Subject Key Identifier:
82:5A:BD:09:78:6E:31:AF:87:22:DA:43:AE:6E:59:D6:B3:E0:6F:C7
X509v3 Authority Key Identifier:
keyid:82:5A:BD:09:78:6E:31:AF:87:22:DA:43:AE:6E:59:D6:B3:E0:6F:C7
X509v3 Basic Constraints: critical
CA:TRUE
Signature Algorithm: sha256WithRSAEncryption
75:bf:81:65:3b:3b:91:76:e2:d5:b3:08:30:6d:a3:79:06:1f:
64:d4:25:78:21:ae:2d:89:54:1a:37:2c:5a:94:56:f3:9e:29:
d7:6b:94:30:b2:9b:0a:59:34:c0:f4:0a:5c:cf:68:44:e7:67:
14:a9:ee:bd:6f:88:27:93:0d:a8:29:16:70:e2:90:fb:be:36:
c6:d2:d7:cd:51:2b:e4:4a:67:5a:f8:f7:62:fc:8b:bf:7a:2a:
e8:a6:c9:1e:e5:7f:14:d0:67:05:7a:70:56:01:5b:7b:0d:06:
e8:de:bf:4c:88:51:c1:77:9c:9c:48:92:ea:6c:5a:a4:99:15:
0d:55:16:05:09:9d:7e:3a:24:18:88:56:fc:88:04:f7:82:c7:
3b:0c:a6:62:63:13:2e:ec:29:01:d4:76:62:8e:fb:df:96:a7:
4b:d3:d6:34:59:68:9d:16:84:c6:43:20:48:6c:1b:0c:d5:af:
3e:a5:39:3b:78:84:b5:b8:79:8d:44:d7:33:d1:0f:0f:53:f3:
db:6f:87:de:c4:66:4d:f8:13:f7:04:7a:9e:57:95:d2:cd:55:
64:7f:e0:d0:6e:9b:9c:41:53:71:0a:9c:78:66:07:38:4b:1e:
90:6b:63:cd:dd:47:79:2c:98:08:af:87:13:f1:1c:a3:de:98:
19:d5:f6:bf
-----BEGIN CERTIFICATE-----
MIIDrzCCApegAwIBAgIUPN/r7XvMP2SqLFt7b44G6Dkrs94wDQYJKoZIhvcNAQEL
BQAwZzELMAkGA1UEBhMCVVMxEDAOBgNVBAgMB0FyaXpvbmExDzANBgNVBAcMBlNl
ZG9uYTEVMBMGA1UECgwMRG9uYWxkIEJhbGVzMR4wHAYDVQQDDBVycGk0LThnLW15
c3FsLWNhLWNlcnQwHhcNMjAwODE0MDA1OTE2WhcNMzAwNjIzMDA1OTE2WjBnMQsw
CQYDVQQGEwJVUzEQMA4GA1UECAwHQXJpem9uYTEPMA0GA1UEBwwGU2Vkb25hMRUw
EwYDVQQKDAxEb25hbGQgQmFsZXMxHjAcBgNVBAMMFXJwaTQtOGctbXlzcWwtY2Et
Y2VydDCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAN/b1GXsGR1T3Wd6
EegcwP/Hh2quNlZW8p6f453IFs2hBYCDf3t+Uf2XTdrUP3bOZdnYnbPhbyOncHmX
Io8hmfSkFPY77/tl3SZ1etblLcuGvBsRxxsI5Aj5zfFVmhYxo1I2S+yM4jp95TJf
Nrd5Jo0jTU/DAnCSm/UwSr3vB7m6ScqT6VOh35TXgJLwyRCKOUg6vt5fvLGofSGp
s7MVmQd82LV1+XhmuQZu3OI1cH88UOpnzpIO0iK+TjSeYMe9HUgDFbzzk1t2NiCB
x5BaBtGQYIFmzTLQbvC1a1wzDPBgmAttbcz0XvqhoBOpQ90yDUrIrWd7h9qmVXN6
82ETnKECAwEAAaNTMFEwHQYDVR0OBBYEFIJavQl4bjGvhyLaQ65uWdaz4G/HMB8G
A1UdIwQYMBaAFIJavQl4bjGvhyLaQ65uWdaz4G/HMA8GA1UdEwEB/wQFMAMBAf8w
DQYJKoZIhvcNAQELBQADggEBAHW/gWU7O5F24tWzCDBto3kGH2TUJXghri2JVBo3
LFqUVvOeKddrlDCymwpZNMD0ClzPaETnZxSp7r1viCeTDagpFnDikPu+NsbS181R
K+RKZ1r492L8i796KuimyR7lfxTQZwV6cFYBW3sNBujev0yIUcF3nJxIkupsWqSZ
FQ1VFgUJnX46JBiIVvyIBPeCxzsMpmJjEy7sKQHUdmKO+9+Wp0vT1jRZaJ0WhMZD
IEhsGwzVrz6lOTt4hLW4eY1E1zPRDw9T89tvh97EZk34E/cEep5XldLNVWR/4NBu
m5xBU3EKnHhmBzhLHpBrY83dR3ksmAivhxPxHKPemBnV9r8=
-----END CERTIFICATE-----
# openssl x509 -text -in server-cert.pem
Certificate:
Data:
Version: 1 (0x0)
Serial Number: 1 (0x1)
Signature Algorithm: sha256WithRSAEncryption
Issuer: C = US, ST = Arizona, L = Sedona, O = Donald Bales, CN = rpi4-8g-mysql.donaldbales.com-ca-cert
Validity
Not Before: Aug 14 00:59:35 2020 GMT
Not After : Jun 23 00:59:35 2030 GMT
Subject: C = US, ST = Arizona, L = Sedona, O = Donald Bales, CN = rpi4-8g-mysql.donaldbales.com
Subject Public Key Info:
Public Key Algorithm: rsaEncryption
RSA Public-Key: (2048 bit)
Modulus:
00:cc:a9:bf:c9:23:57:97:e1:27:bd:43:38:5c:a9:
a9:d3:7e:27:65:f9:2e:4f:32:9f:a5:dc:59:a9:23:
21:45:bf:68:80:9b:97:e5:d7:f8:cd:e2:d3:cf:8b:
6e:ad:e4:13:3e:f0:18:25:95:4a:4c:28:7f:ab:26:
43:c9:64:6f:07:f9:12:83:53:47:ef:f9:db:9e:00:
f6:af:e3:af:b4:18:58:0c:8b:3f:fd:1f:fe:75:65:
02:f0:ff:a1:96:eb:5e:8f:f4:db:d0:8f:0b:34:fc:
f5:b9:b1:8a:27:ee:fa:9d:59:54:7a:c3:0f:d5:6a:
51:63:90:cf:48:2d:41:54:09:f8:c1:53:d8:19:a4:
30:99:83:43:8a:e0:c1:f2:07:40:29:47:29:49:35:
a9:6d:0e:63:96:a3:24:9e:2b:2d:f4:43:0b:2f:b0:
ec:a2:7c:95:42:9f:09:50:72:01:95:98:ac:b6:3e:
31:b8:d7:99:d6:cf:b1:07:cf:5a:32:c0:7f:e0:b0:
3a:9e:65:4d:28:af:51:31:5c:66:f3:8e:9c:a9:13:
18:6b:8e:c1:e4:7b:a3:ad:16:f8:6d:ab:2a:ee:f9:
05:85:19:f0:e2:f8:7a:7c:94:3a:25:11:6a:0b:2f:
2a:c7:eb:5c:56:e9:79:e8:30:85:8e:d9:fd:62:5f:
ca:51
Exponent: 65537 (0x10001)
Signature Algorithm: sha256WithRSAEncryption
85:87:4d:5a:c1:60:01:dd:c0:81:65:28:19:29:0e:c7:4c:12:
9c:01:32:e1:31:df:f6:bc:0c:9c:78:d4:db:1c:9c:4d:2d:09:
1d:04:f6:d3:e6:86:9e:13:52:48:8c:3c:b7:a1:45:4e:bb:40:
3f:ea:25:33:2e:f8:55:fc:61:ac:64:18:15:bd:e3:97:9f:b5:
ee:17:57:6c:62:45:bf:e0:f7:14:43:d5:1c:9f:3e:46:b8:d1:
3d:6b:c0:2b:97:60:4a:f6:dc:59:83:74:95:09:04:bd:7d:fd:
c0:dd:0e:e9:b8:09:93:0b:5e:2a:99:e3:89:9c:ad:29:d1:be:
f8:be:93:14:24:80:52:2c:bc:a8:3c:cc:f3:83:d8:44:66:2f:
17:54:18:3c:b9:2b:ca:49:22:a0:6d:70:08:c4:c7:ed:91:89:
fe:90:0a:d6:a2:32:6b:84:00:41:89:d0:00:7a:0a:f1:24:ae:
cc:57:6c:74:b1:7a:4b:27:89:c5:bb:8a:6f:b8:71:f5:78:4a:
50:6c:44:e9:77:ee:f7:f5:13:70:54:33:ad:e1:d2:5b:24:e2:
f9:57:79:61:cb:b2:38:8a:88:7f:2c:29:5b:cd:9a:3b:98:0e:
a3:93:57:1f:63:60:29:09:f8:13:36:25:10:0e:d1:7b:d9:2d:
39:a5:5c:f6
-----BEGIN CERTIFICATE-----
MIIDOjCCAiICAQEwDQYJKoZIhvcNAQELBQAwZzELMAkGA1UEBhMCVVMxEDAOBgNV
BAgMB0FyaXpvbmExDzANBgNVBAcMBlNlZG9uYTEVMBMGA1UECgwMRG9uYWxkIEJh
bGVzMR4wHAYDVQQDDBVycGk0LThnLW15c3FsLWNhLWNlcnQwHhcNMjAwODE0MDA1
OTM1WhcNMzAwNjIzMDA1OTM1WjBfMQswCQYDVQQGEwJVUzEQMA4GA1UECAwHQXJp
em9uYTEPMA0GA1UEBwwGU2Vkb25hMRUwEwYDVQQKDAxEb25hbGQgQmFsZXMxFjAU
BgNVBAMMDXJwaTQtOGctbXlzcWwwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEK
AoIBAQDMqb/JI1eX4Se9QzhcqanTfidl+S5PMp+l3FmpIyFFv2iAm5fl1/jN4tPP
i26t5BM+8BgllUpMKH+rJkPJZG8H+RKDU0fv+dueAPav46+0GFgMiz/9H/51ZQLw
/6GW616P9NvQjws0/PW5sYon7vqdWVR6ww/ValFjkM9ILUFUCfjBU9gZpDCZg0OK
4MHyB0ApRylJNaltDmOWoySeKy30QwsvsOyifJVCnwlQcgGVmKy2PjG415nWz7EH
z1oywH/gsDqeZU0or1ExXGbzjpypExhrjsHke6OtFvhtqyru+QWFGfDi+Hp8lDol
EWoLLyrH61xW6XnoMIWO2f1iX8pRAgMBAAEwDQYJKoZIhvcNAQELBQADggEBAIWH
TVrBYAHdwIFlKBkpDsdMEpwBMuEx3/a8DJx41NscnE0tCR0E9tPmhp4TUkiMPLeh
RU67QD/qJTMu+FX8YaxkGBW945efte4XV2xiRb/g9xRD1RyfPka40T1rwCuXYEr2
3FmDdJUJBL19/cDdDum4CZMLXiqZ44mcrSnRvvi+kxQkgFIsvKg8zPOD2ERmLxdU
GDy5K8pJIqBtcAjEx+2Rif6QCtaiMmuEAEGJ0AB6CvEkrsxXbHSxeksnicW7im+4
cfV4SlBsROl37vf1E3BUM63h0lsk4vlXeWHLsjiKiH8sKVvNmjuYDqOTVx9jYCkJ
+BM2JRAO0XvZLTmlXPY=
-----END CERTIFICATE-----
# openssl x509 -text -in client-cert.pem
Certificate:
Data:
Version: 1 (0x0)
Serial Number: 1 (0x1)
Signature Algorithm: sha256WithRSAEncryption
Issuer: C = US, ST = Arizona, L = Sedona, O = Donald Bales, CN = rpi4-8g-mysql.donaldbales.com-ca-cert
Validity
Not Before: Aug 14 00:59:47 2020 GMT
Not After : Jun 23 00:59:47 2030 GMT
Subject: C = US, ST = Arizona, L = Sedona, O = Donald Bales, CN = rpi4-8g-mysql.donaldbales.com
Subject Public Key Info:
Public Key Algorithm: rsaEncryption
RSA Public-Key: (2048 bit)
Modulus:
00:bf:5b:e4:b2:41:09:c9:0b:1b:c5:58:1a:b3:d0:
5b:d0:a6:47:bc:39:23:4f:a8:1e:6d:5c:41:4b:45:
af:10:29:13:d2:c6:4e:5f:00:dc:b1:d3:cb:f3:4d:
d6:94:05:3c:71:4a:ee:b7:e2:84:5d:ed:df:dc:87:
2a:1e:48:64:99:a6:05:52:0a:e1:f3:55:29:75:55:
4e:c9:03:2f:61:a6:f5:65:51:f7:54:f1:32:50:d5:
11:61:df:53:f3:1a:9c:1b:aa:a4:5f:ea:5a:fc:4e:
77:69:69:b0:42:32:4f:87:6d:81:1a:35:1b:1b:36:
48:28:b1:e8:d7:34:90:00:09:f6:10:8a:c5:36:92:
93:81:95:7a:78:0b:7a:ac:85:01:bd:42:9b:2d:34:
6d:ff:0b:24:c0:f2:f6:d4:aa:b0:51:9d:55:39:65:
1c:b6:91:8f:5e:7e:e8:b2:52:bc:cb:72:34:d8:69:
de:77:4e:8d:4c:ce:d4:c3:ba:5b:ef:17:37:90:61:
d4:0d:20:00:1e:3e:a0:2a:a9:c2:f3:56:ad:78:29:
dd:f8:7c:3d:0b:c0:7b:9d:4b:07:51:b7:bf:c8:f4:
d1:a2:72:d8:2e:8a:2e:09:a1:b9:9f:29:3a:dc:96:
fa:f8:7c:5e:66:a5:ba:64:95:9a:aa:09:a7:ef:f9:
ef:31
Exponent: 65537 (0x10001)
Signature Algorithm: sha256WithRSAEncryption
5a:ec:b0:c7:95:a4:15:65:f1:e0:90:1b:bf:b6:73:68:6e:20:
64:92:dd:07:1f:39:62:6b:06:67:9f:37:87:8f:18:60:66:df:
b3:c2:5f:bf:9f:64:0c:b7:8d:df:8c:ac:58:51:9f:d2:81:2d:
ee:c7:83:41:dc:e2:99:a8:12:a7:d5:82:74:cb:30:81:24:2a:
1a:d0:ea:d0:56:f6:49:40:fe:dc:13:08:d2:09:03:28:7a:46:
ce:00:c6:be:9b:56:a5:ea:32:40:3d:8a:2e:be:2c:03:62:fb:
2e:54:bc:9f:b5:d1:77:c6:e5:67:6f:06:6c:6b:45:15:7e:d6:
93:44:be:bd:28:f7:44:51:8e:99:b0:26:1b:d4:c5:d1:93:72:
8b:64:2b:40:80:5b:a9:da:f5:7e:6b:8c:55:93:20:ac:4b:35:
a8:19:01:d4:df:c4:fd:78:61:9c:3c:f8:38:a1:44:f3:a4:4f:
4c:24:c8:8b:9c:77:c4:9b:98:43:92:f5:6b:43:41:c2:1c:8f:
00:e2:61:c1:ed:1a:cd:ff:46:88:26:ae:65:c3:a5:0b:2a:9f:
d0:9e:5e:21:a3:0c:18:c9:43:f1:cf:be:dc:11:25:09:8e:d4:
39:d6:e5:60:0d:b4:7d:8d:83:e5:98:38:73:b8:70:f5:46:ac:
f3:61:77:9f
-----BEGIN CERTIFICATE-----
MIIDOjCCAiICAQEwDQYJKoZIhvcNAQELBQAwZzELMAkGA1UEBhMCVVMxEDAOBgNV
BAgMB0FyaXpvbmExDzANBgNVBAcMBlNlZG9uYTEVMBMGA1UECgwMRG9uYWxkIEJh
bGVzMR4wHAYDVQQDDBVycGk0LThnLW15c3FsLWNhLWNlcnQwHhcNMjAwODE0MDA1
OTQ3WhcNMzAwNjIzMDA1OTQ3WjBfMQswCQYDVQQGEwJVUzEQMA4GA1UECAwHQXJp
em9uYTEPMA0GA1UEBwwGU2Vkb25hMRUwEwYDVQQKDAxEb25hbGQgQmFsZXMxFjAU
BgNVBAMMDXJwaTQtOGctbXlzcWwwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEK
AoIBAQC/W+SyQQnJCxvFWBqz0FvQpke8OSNPqB5tXEFLRa8QKRPSxk5fANyx08vz
TdaUBTxxSu634oRd7d/chyoeSGSZpgVSCuHzVSl1VU7JAy9hpvVlUfdU8TJQ1RFh
31PzGpwbqqRf6lr8TndpabBCMk+HbYEaNRsbNkgosejXNJAACfYQisU2kpOBlXp4
C3qshQG9QpstNG3/CyTA8vbUqrBRnVU5ZRy2kY9efuiyUrzLcjTYad53To1MztTD
ulvvFzeQYdQNIAAePqAqqcLzVq14Kd34fD0LwHudSwdRt7/I9NGictguii4Jobmf
KTrclvr4fF5mpbpklZqqCafv+e8xAgMBAAEwDQYJKoZIhvcNAQELBQADggEBAFrs
sMeVpBVl8eCQG7+2c2huIGSS3QcfOWJrBmefN4ePGGBm37PCX7+fZAy3jd+MrFhR
n9KBLe7Hg0Hc4pmoEqfVgnTLMIEkKhrQ6tBW9klA/twTCNIJAyh6Rs4Axr6bVqXq
MkA9ii6+LANi+y5UvJ+10XfG5WdvBmxrRRV+1pNEvr0o90RRjpmwJhvUxdGTcotk
K0CAW6na9X5rjFWTIKxLNagZAdTfxP14YZw8+DihRPOkT0wkyIucd8SbmEOS9WtD
QcIcjwDiYcHtGs3/RogmrmXDpQsqn9CeXiGjDBjJQ/HPvtwRJQmO1DnW5WANtH2N
g+WYOHO4cPVGrPNhd58=
-----END CERTIFICATE-----
# cd ..
# mkdir oldcerts
# cp *.pem oldcerts
# chown -R mysql:mysql oldcerts
# chown -R mysql:mysql newcerts
# cp newcerts/*.pem .
# chown mysql:mysql *.pem
# ls -lap
total 336844
...
-rw------- 1 mysql mysql 1675 Aug 14 01:44 ca-key.pem
-rw-r--r-- 1 mysql mysql 1338 Aug 14 01:44 ca.pem
-rw-r--r-- 1 mysql mysql 1180 Aug 14 01:44 client-cert.pem
-rw------- 1 mysql mysql 1675 Aug 14 01:44 client-key.pem
-rw-r--r-- 1 mysql mysql 993 Aug 14 01:44 client-req.pem
...
drwxr-xr-x 2 mysql mysql 4096 Aug 14 00:59 newcerts/
drwxr-xr-x 2 mysql mysql 4096 Aug 14 01:42 oldcerts/
...
-rw------- 1 mysql mysql 1679 Aug 13 02:06 private_key.pem
-rw-r--r-- 1 mysql mysql 451 Aug 13 02:06 public_key.pem
...
-rw-r--r-- 1 mysql mysql 1180 Aug 14 01:44 server-cert.pem
-rw------- 1 mysql mysql 1675 Aug 14 01:44 server-key.pem
-rw-r--r-- 1 mysql mysql 993 Aug 14 01:44 server-req.pem
...
# systemctl start mysqld
Now that I have the new certs in place, Let’s copy the new self-signed Certificate Authority (CA) certificate to the user’s home directory, so we can scp it to the PHP client machine:
# cp ca.pem /home/ubuntu/rpi4-8g-mysql.donaldbales.com-ca.pem
# chown ubuntu:ubuntu /home/ubuntu/rpi4-8g-mysql.donaldbales.com-ca.pem
# exit
logout
$ ls -lap
total 152
drwxr-xr-x 5 ubuntu ubuntu 4096 Aug 14 01:46 ./
drwxr-xr-x 4 root root 4096 Jul 28 17:49 ../
-rw------- 1 ubuntu ubuntu 7237 Aug 14 00:56 .bash_history
-rw-r--r-- 1 ubuntu ubuntu 220 Feb 25 12:03 .bash_logout
-rw-r--r-- 1 ubuntu ubuntu 3771 Feb 25 12:03 .bashrc
drwx------ 2 ubuntu ubuntu 4096 Jul 28 04:37 .cache/
-rw------- 1 ubuntu ubuntu 1371 Aug 14 00:47 .mysql_history
-rw-r--r-- 1 ubuntu ubuntu 807 Feb 25 12:03 .profile
drwx------ 2 ubuntu ubuntu 4096 Aug 12 05:09 .ssh/
-rw-r--r-- 1 ubuntu ubuntu 0 Jul 28 04:37 .sudo_as_admin_successful
-rw------- 1 ubuntu ubuntu 5537 Aug 13 23:27 .viminfo
drwxrwxr-x 2 ubuntu ubuntu 4096 Aug 13 23:27 bin/
-rw-r--r-- 1 ubuntu ubuntu 1338 Aug 14 01:46 rpi4-8g-mysql.donaldbales.com-ca.pem
Last, let’s log into MySQL as root, and turn on server key verification:
$ mysql -u root -p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10 Server version: 8.0.21-0ubuntu0.20.04.4 (Ubuntu)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set global require_secure_transport=1;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Above, we turn on SSL identity verification by setting require_secure_transport=1.
On the Web Host
On the machine where PHP, Apache2, and the Akeneo PIM are installed, the web host, we need to make changes to the Akeneo PIM configuration in order to support validating against the database server’s certificate. Akeneo PIM has three different instances of creating a connection to the database:
- · Doctrine ORM
- · Managing user sessions in the database
- · Checking PIM requirements
This is where the decision by Akeneo to use PHP/Symfony to build its application shines. We can override the configuration for the Doctrine ORM and session management to activate SSL identity verification. However, we can’t do that for PIM requirements. So, we will override the first two, and hack pim requirements.
Let’s start by downloading the new CA certificate from our database server:
$ cd pim-community-standard
$ scp ubuntu@rpi4-8g-mysql:/home/ubuntu/rpi4-8g-mysql.donaldbales.com-ca.pem .
Test SSL Using the MySQL CLI
NOTE: If your hosts are not set up with DNS, you may need to add a host entry to your /etc/hosts file in order to resolve the database server’s IP address. For example, here I’m adding an entry for rpi4-8g-mysql.donaldbales.com, the database server’s host + fqdn, which is at IP address 10.0.0.11:
$ sudo vim /etc/hosts
The following are for Akeneo hosts
10.0.0.11 rpi4-8g-mysql.donaldbales.com
Now let’s test that the CA certificate can validate the database server’s host certificate:
$ mysql -h rpi4-8g-mysql.donaldbales.com -D akeneo_pimce -u akeneo_pimce -pakeneo_pimce \
--ssl-mode=VERIFY_IDENTITY --ssl_ca=rpi4-8g-mysql.donaldbales.com-ca.pem
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 32
Server version: 8.0.21-0ubuntu0.20.04.4 (Ubuntu)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> status
-------------- mysql Ver 8.0.21-0ubuntu0.20.04.4 for Linux on aarch64 ((Ubuntu))
Connection id: 32
Current database: akeneo_pimce
Current user: [email protected]
SSL: Cipher in use is TLS_AES_256_GCM_SHA384
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.21-0ubuntu0.20.04.4 (Ubuntu)
Protocol version: 10
Connection: rpi4-8g-mysql.donaldbales.com via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
TCP port: 3306
Binary data as: Hexadecimal
Uptime: 13 hours 52 min 26 sec
Threads: 2 Questions: 112 Slow queries: 0 Opens: 233 Flush tables: 3 Open tables: 154 Queries per second avg: 0.002
-------------- From the status output above, you can see that we indeed have a valid SSL connection.
Configure the Akeneo Environment
Now that we have the CA Certificate in our PIM’s root directory, let’s add the cert to, and change the host name for mysql in the .env file:
$ vim .env
APP_DATABASE_HOST=rpi4-8g-mysql.donaldbales.com
APP_DATABASE_SSL_CA=/home/ubuntu/pim-community-standard/rpi4-8g-mysql.donaldbales.com-ca.pem
APP_DATABASE_SSL_VERIFY_SERVER_CERT=1
The two new environment variables: APP_DATABASE_SSL_CA and APP_DATABASE_SSL_VERIFY_SERVER_CERT do not exist in Akeneo by default. We are adding them here, and later, will use these when we override the Akeneo PIM configuration.
APP_DATABASE_SSL_CA specifies the location of the database server's CA certificate.
APP_DATABASE_SSL_VERIFY_SERVER_CERT set to 1, turns on server certificate verification.
Overriding the Doctrine ORM Configuration
By using the find and grep commands together against the vendor directory, we're able to find the Doctrine configuration in file vendor/akeneo/pim-community-dev/config/packages directory.
$ cat vendor/akeneo/pim-community-dev/config/packages/doctrine.yml
# Please do not remove placeholder, to keep the listener getting registered
doctrine:
dbal:
default_connection: default
connections:
default:
driver: 'pdo_mysql'
dbname: '%env(APP_DATABASE_NAME)%'
host: '%env(APP_DATABASE_HOST)%'
port: '%env(APP_DATABASE_PORT)%'
user: '%env(APP_DATABASE_USER)%'
password: '%env(APP_DATABASE_PASSWORD)%'
charset: utf8mb4
default_table_options:
charset: utf8mb4
collate: utf8mb4_unicode_ci
row_format: DYNAMIC
server_version: '8.0'
mapping_types:
json: string
types:
datetime: Akeneo\Tool\Bundle\StorageUtilsBundle\Doctrine\DBAL\Types\UTCDateTimeType
...
To override this configuration, adding the two new environment variables from above, we will create a new file in the config/packages directory:
$ vim config/packages/doctrine.yml
# Override the Doctrine setting adding support for SSL
#
# PDO::MYSQL_ATTR_SSL_CA 1009
# PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT 1014
#
doctrine:
dbal:
connections:
default:
options:
1009: '%env(APP_DATABASE_SSL_CA)%'
1014: '%env(APP_DATABASE_SSL_VERIFY_SERVER_CERT)%'
You can see from the new, additional, configuration information, that I’ve added options 1009 and 1014. These are numeric constants provided by the PHP mysqld driver to support options. I’ve even added a comment at the top of the file to document their purpose.
Overriding the Session Management Configuration
Now that we have Doctrine configured for SSL identity verification, let’s configure session management. Session management in Akeneo is handled by class PdoSessionHandler. Once again, by using the find and grep commands together against the vendor directory, we’re able to find the PdoSessionHandler configuration in vendor/akeneo/pim-community-dev/config/services/pim.yml
$ head -n 60 vendor/akeneo/pim-community-dev/config/services/pim.yml
# To have more details on the effect of this "pim_job_product_batch_size", on performances
# memory leak, please have a look at this documentation part:
# https://docs.akeneo.com/latest/technical_architecture/performances_guide/batch_page_size.html
#
# Note to the core developers:
# The pim_job_product_batch_size has been changed from 100 to 10 at the beginning of 2018
# already, because of fear of generating memory leak.
# Now that we have more feedback, it seems to create more problem than real gain in term
# of performances. Moreover this value was forced at 100 for our cloud customers, so this
# was creating inconsistency in our installed base. So we are reverting it back to 100.
#
# So if you want to change it, do that only for major version and check with the tech team.
#
parameters:
pim_job_product_batch_size: 100
services:
oro.cache.abstract:
abstract: true
class: Doctrine\Common\Cache\PhpFileCache
arguments: ['%kernel.cache_dir%']
twig.extension.intl:
class: Twig_Extensions_Extension_Intl
tags:
- { name: twig.extension }
session.handler.pdo:
class: Symfony\Component\HttpFoundation\Session\Storage\Handler\PdoSessionHandler
arguments:
- 'mysql:dbname=%env(APP_DATABASE_NAME)%; host=%env(APP_DATABASE_HOST)%; port=%env(APP_DATABASE_PORT)%'
- { db_table: 'pim_session', db_username: '%env(APP_DATABASE_USER)%', db_password: '%env(APP_DATABASE_PASSWORD)%' }
After examining the PdoSessionHandler source code, I discovered that we need to add the SSL options in a new file: config/services/pim.yml as follows:
$ vim config/services/pim.yml
services:
session.handler.pdo:
class: Symfony\Component\HttpFoundation\Session\Storage\Handler\PdoSessionHandler
arguments:
- 'mysql:dbname=%env(APP_DATABASE_NAME)%; host=%env(APP_DATABASE_HOST)%; port=%env(APP_DATABASE_PORT)%'
- { db_table: 'pim_session',
db_username: '%env(APP_DATABASE_USER)%',
db_password: '%env(APP_DATABASE_PASSWORD)%',
db_connection_options: {
1009: '%env(APP_DATABASE_SSL_CA)%',
1014: '%env(APP_DATABASE_SSL_VERIFY_SERVER_CERT)%' } }
If you compare the two listings above, you can see that I added:
db_connection_options: { 1009: '%env(APP_DATABASE_SSL_CA)%', 1014: '%env(APP_DATABASE_SSL_VERIFY_SERVER_CERT)%' }
Hacking PIM Requirements
Now that session management is configure for SSL identity verification, let’s fix PIM requirements. Fix? you say.
When you want to check that all of the PIM’s infrastructure requirements are met, you run:
$ bin/console pim:installer:check-requirements
Since this console app is a stand-alone utility that does not use Doctrine for establishing a connection, and since it is not part of a bundle, there is no way to override its configuration like we did for Doctrine and PdoSessionHandler. Instead, you can turn off SSL identity verification in the database by setting require_secure_transport=0, or you can hack the function in the source-code so it supports the SSL options.
I submitted a contribution, https://github.com/akeneo/pim-community-dev/pull/12587, Added support for an SSL connection to PimRequirements, with a modified version of the PimRequirements.getConnection() function that supports our two new SSL options:
From: src/Akeneo/Platform/PimRequirements.php
protected function getConnection() : PDO
{
$dsn = sprintf(
'mysql:port=%s;host=%s',
getenv('APP_DATABASE_PORT'),
getenv('APP_DATABASE_HOST')
);
$username = getenv('APP_DATABASE_USER');
$passwd = getenv('APP_DATABASE_PASSWORD');
$sslCa = getenv('APP_DATABASE_SSL_CA');
$sslVerifyServerCert = getenv('APP_DATABASE_SSL_VERIFY_SERVER_CERT');
if (isset($sslCa) && isset($sslVerifyServerCert)) {
$options = array(
PDO::MYSQL_ATTR_SSL_CA => $sslCa,
PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => $sslVerifyServerCert
);
return new PDO($dsn, $username, $passwd, $options);
} else {
return new PDO($dsn, $username, $passwd);
}
}
Post Configuration Testing
Now that we have all three database connection sources supporting our new SSL options, we are ready to test that they work.
First, let’s test PIM Requirements:
$ bin/console pim:installer:check-requirements
Next, let’s check Doctrine:
$ bin/console doctrine:query:sql "select 'OK' status"
And last, try logging into the application. That will exercise PdoSessionHandler.
Verifying SSL Connections in MySQL
To verify that all users are connecting with SSL identity verification, you can run the following query while logged into your MySQL instance as root:
SELECT sbt.variable_value AS tls_version,
t2.variable_value AS cipher,
processlist_user AS user,
processlist_host AS host
FROM performance_schema.status_by_thread AS sbt
JOIN performance_schema.threads AS t ON t.thread_id = sbt.thread_id
JOIN performance_schema.status_by_thread AS t2 ON t2.thread_id = t.thread_id
WHERE sbt.variable_name = 'Ssl_version'
AND t2.variable_name = 'Ssl_cipher'
ORDER BY tls_version;
mysql> SELECT sbt.variable_value AS tls_version,
-> t2.variable_value AS cipher,
-> processlist_user AS user,
-> processlist_host AS host
-> FROM performance_schema.status_by_thread AS sbt
-> JOIN performance_schema.threads AS t ON t.thread_id = sbt.thread_id
-> JOIN performance_schema.status_by_thread AS t2 ON t2.thread_id = t.thread_id
-> WHERE sbt.variable_name = 'Ssl_version'
-> AND t2.variable_name = 'Ssl_cipher'
-> ORDER BY tls_version;
+-------------+------------------------+--------------+--------------+
| tls_version | cipher | user | host |
+-------------+------------------------+--------------+--------------+
| | | root | localhost |
| TLSv1.3 | TLS_AES_256_GCM_SHA384 | akeneo_pimce | 192.168.0.46 |
+-------------+------------------------+--------------+--------------+
2 rows in set (0.02 sec)
Now you know how to configure your Akeneo PIM to use SSL identity verification.
Good skill!
