Setup SSL with client authentication to Mysql and establish SSL connection form PHP


Apache and Mysql were working in same server. But I decided to move mysql to other server which is connectable from the apache.

Followings are the security risks to allow remote access.
  • unauthorized access to db
  • packet sniffing on network
In order to prepare in these threats. I set up SSL connection on mysql.

Step1 Generate keys and certificate used in SSL.

Generate CA key without encryption.
openssl genrsa -out ca-key.pem 20488
Generate certificate of CA.
openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca-cert.pem
Generate server key and csr.
openssl req -newkey rsa:2018 -days 3650 -nodes -keyout server-key.pem -out server-csr.pem
Convert format from PKCS#8 to PKCS#1 (Because it may be mysql does not work well in PKCS#8 key, just in case)
openssl rsa -in server-key.pem -out server-key.pem
Generate server x509 form certificate.
openssl x509 -req -in server-csr.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Generate client key and csr.
openssl req -newkey rsa:2018 -days 3650 -nodes -keyout client-key.pem -out client-csr.pem
Convert format from PKCS#8 to PKCS#1
openssl rsa -in client-key.pem -out client-key.pem
Generate client certificate.
openssl x509 -req -in client-csr.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

Step2 Set up mysql

Add certificate and key file to configuration file for MySQL.
sed -i '/[mysqld]/a ssl-ca=/etc/.../ca-cert.pem' /etc/my.cnf
sed -i '/[mysqld]/a ssl-cert=/etc/.../server-cert.pem' /etc/my.cnf
sed -i '/[mysqld]/a ssl-key=/etc/.../server-key.pem' /etc/my.cnf
And restart MySQL daemon.
systemctl restart mysqld
Login on MySQL to confirm SSL configuration.
mysql -u root -p
confirm following configuration with show command
>>show variables like '%ssl%'
variable_namevalue
have_opensslYES
have_opensslYES
ssl_ca/etc/.../ca-cert.pem
ssl_cert/etc/.../server-cert.pem
ssl_key/etc/.../server-key.pem
Create MySQL user that is required client certificate authentication.
>>create user ssluser;
>>grant all on *.* to ssluser requre x509;

step3 Connect to mysql wtih SSL from console

Connect to MySQL with SSL from command line.
mysql -u ssluser --ssl-ca=/etc/.../ca-cert.pem --ssl-cert=/etc/.../client-cert.pem --ssl-key=/etc/.../client-key.pem

step4 Connect to mysql with SSL from PHP

This is PHP code that connects to mysql with SSL.
class MyPDO{
	protected static $rdbserver = 'mysql.example.com';
	protected static $rdbport = 3306;
	protected static $user = 'dbuser';
	protected static $password = 'password';
	protected static $option = array(
		PDO::MYSQL_ATTR_SSL_KEY => '/etc/.../client-key.pem',
		PDO::MYSQL_ATTR_SSL_CERT => '/etc/.../client-cert.pem',
		PDO::MYSQL_ATTR_SSL_CA => '/etc/.../ca-cert.pem',
		PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
	);
	public static function get($db){
		$dsn = ('mysql:host=' . MyPDO::$rdbserver . ':' . strval(MyPDO::$rdbport) . ';dbname=' . $db . ';charset=utf8');
		$dbh = new PDO($dsn, MyPDO::$user, MyPDO::$password, MyPDO::$option);
		return $dbh;
	}
}

Profile
I have technical job experience in enbedded software development and server side infrastructure/application engineering. I'm interested in programming and computer security.
Objective
To write down my technical knowledge in the place where I can access from anywhere. To share my program source code. To train my writing skill.
Link
  • LinkedIn (preparing)

  • Twitter

  • Facebook (preparing)

  • GitHub

  • StackOverFlow (preparing)

Archives