I’m trying to set up a separate database server and then connect to it from a separate server running User Frosting on LEMP. I was able to get it all set this up nicely following the Digitalocean tutorial at https://www.digitalocean.com/community/tutorials/how-to-set-up-a-remote-database-to-optimize-site-performance-with-mysql-on-ubuntu-16-04, but I’m struggling with the last step, which is getting my UF installation to connect to the new remote database with secure transport enabled / require_secure_transport = on.
I’m using local IP addresses (i.e. within Digital Ocean’s network) and have successfully connected from my web server via mysql on the command line and have verified that this connection is using SSL.
When I first tried this, I got this error “SQLSTATE[HY000]  Connections using insecure transport are prohibited while --require_secure_transport=ON.”
I couldn’t see anything in the UF documentation about this but was able to deduce the config values that are needed for Laravel to pass to PDO_MYSQL:
‘sslmode’ => true,
‘options’ => array(
PDO::MYSQL_ATTR_SSL_KEY => ‘path to privkey.pem’,
PDO::MYSQL_ATTR_SSL_CERT => ‘path to fullchain.pem’,
PDO::MYSQL_ATTR_SSL_CA => '‘path to ca.pem’
Here are the things I’ve attempted (without success!) to do to fix this:
I tried referencing the paths to the SSL certificate on my web server. The CA cert that Certbot provides is apparently called chain.pem. This gave me the error ‘PDOException
Message: failed loading cafile stream: …path’ (the path did exist)
I looked file permissions on those files and tried referencing the unsymlinked files directly. I got:
PDO::__construct(): SSL operation failed with code 1. OpenSSL Error messages: error:14090086:SSL routines:ssl3_get_server_certificate:certificate verify failed
I read another article at https://www.digitalocean.com/community/tutorials/how-to-configure-ssl-tls-for-mysql-on-ubuntu-16-04 that made me think (realize?) that the certificate files I need are the ones generated by mysql on the database server, not the ones on the client server. So I copied these, but got This gave me the error ‘PDOException
Message: failed loading cafile stream: …path’ . I then referenced them in my mysqld.cnf file under the [client] directive, but when I restarted I got “ERROR 2026 (HY000): SSL connection error: SSL is required but the server doesn’t support it”. From what I could find, this implies a path error but the path seemed to be correct.
I’m sure I’m doing something daft here (as you’ll have gathered, I’m somewhat out of my comfort zone), but I wondered if anyone could tell me:
- is this the right approach overall to doing this in UF or is there another method I’m unaware of?
- is it the certificate files from the database server that I need to be referencing?
- what should the permissions on those files be?
Hope this is clear. Thanks for any help!