Using SSL with MariaDB (MySQL)

# Create clean environment
shell> rm -rf newcerts
shell> mkdir newcerts && cd newcerts

# Create CA certificate
shell> openssl genrsa 2048 > ca-key.pem
shell> openssl req -new -x509 -nodes -days 3600 \
         -key ca-key.pem -out ca.pem

# Create server certificate, remove passphrase, and sign it
# server-cert.pem = public key, server-key.pem = private key
shell> openssl req -newkey rsa:2048 -days 3600 \
         -nodes -keyout server-key.pem -out server-req.pem
shell> openssl rsa -in server-key.pem -out server-key.pem
shell> openssl x509 -req -in server-req.pem -days 3600 \
         -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# Create client certificate, remove passphrase, and sign it
# client-cert.pem = public key, client-key.pem = private key
shell> openssl req -newkey rsa:2048 -days 3600 \
         -nodes -keyout client-key.pem -out client-req.pem
shell> openssl rsa -in client-key.pem -out client-key.pem
shell> openssl x509 -req -in client-req.pem -days 3600 \
         -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

mysql> show global variables like '%ssl%'; 
| Variable_name | Value                                 |
| have_openssl  | YES                                   |
| have_ssl      | YES                                   |
| ssl_ca        | /etc/pki/mysql/root/ca-bundle.pem     |
| ssl_capath    |                                       |
| ssl_cert      | /etc/pki/mysql/server/cert.pem        |
| ssl_cipher    |                                       |
| ssl_key       | /etc/pki/mysql/server/key.pem         |
7 rows in set (0.00 sec)

MariaDB [(none)]> status;
mysql  Ver 15.1 Distrib 5.5.41-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:		5
Current database:	
Current user:
SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		5.5.41-MariaDB MariaDB Server
Protocol version:	10
Connection: via TCP/IP
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		3306
Uptime:			8 min 50 sec

Threads: 1  Questions: 10  Slow queries: 0  Opens: 0  Flush tables: 2  Open tables: 26  Queries per second avg: 0.018

GRANT ALL PRIVILEGES ON test.* TO 'someuser'@'somehost'
      ISSUER '/C=US/ST=RedState/L=Thistown/O=MySQL Trust Authority 99/CN=The Man/'
  AND SUBJECT '/C=US/ST=BlueState/L=Thattown/O=ACME Widgets/CN=John Doe/';

SOLVED: POST fails in SSL with ‘request body exceeds maximum size (131072) for SSL buffer’


  1. Turn KeepAlive to On
  2. Set SSLRenegBufferSize to a very large value


  • Large media uploads fail in WordPress
  • Where “large” is over 128K.



The apache log files indicate this problem with lines of the form

request body exceeds maximum size (131072) for SSL buffer, referer:
could not buffer message body to allow SSL renegotiation to proceed, referer:


  • Ensure that a GET occurs before the POST such that SSL renegotiation does not occur in the same SSL session.
  • Thus KeepAlive is required..
  • Ensure that the KeepAlive interval is respected between the GET and the POST.
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 30



Archaeological order … more original materials deeper down the stack…

SOLVED[fail]: Android WebView does not support Client Certificates at all

Problem Statement



Partial success…

  • Webware => just works
    • desktop officework browser
      i.e. Firefox 29+
    • mobile browser => “just works”
      i.e. Chrome 40, Blink 537.36, Android 4.4.4 (what is that, Jelly Bean, Key Lime Pie?, Lollipop?)
  • Appware => FAIL
    • Android does not work, cannot be made to work.
    • WordPress Android is unuseable in this mode.

Environment: Webby Officework Desktop

Outcome: just works


  • Firefox (Fedora) “just works”
  • Chrome (Android) “just works”


  • User receives the certificate as a PKCS #12 (a .p12 file)
  • Install fhe certificate
    • … in the browser (Firefox, Linux)
    • … in the operating system (Android)

Environment: WordPress Android

Outcome: FAIL

  • Do not use Android WordPress on these blogs
  • Use the webby interface with Chrome.
  • WordPress Android uses an embedded WebView which does not implement client certificates at all.


Others have tried … but Android does not yet support this concept

HOWTO: Secure A Website With Client SSL Certificates

HOWTO: Securing A Website With Client SSL Certificates; Staff; In Some Blog; 2006-09-06.


Require valid certificates from FakeBasicAuth so if the certificate is good, then authorization can be granted in the filtering step.

SSLVerifyClient require
SSLOptions +FakeBasicAuth +ExportCertData +CompatEnvVars

The authorization filtering step itself:

SSLRequire ( %{SSL_CIPHER} !~ m/^(EXP|NULL)-/ and %{SSL_CLIENT_S_DN_O} eq "Example LLC" and %{SSL_CLIENT_S_DN_OU } in {"Department of Department Management", "Department of Oversight Management", "Department of Administration Adequacy"} )
  • The cipher must not be null or the lowgrade export-level
  • The Distinguished Name’s Organization must be exactly Example LLC
  • The Distinguished Name’s Organizational Unit must equal to one of
    • Department of Department Management
    • Department of Oversight Management
    • Department of Administration Adequacy

A list of the certificates to deny.

SSLCARevocationFile /etc/ssl/private/ca.crl


Environment Variables



<quoe>When this option is enabled, the Subject Distinguished Name (DN) of the Client X509 Certificate is translated into a HTTP Basic Authorization username. This means that the standard Apache authentication methods can be used for access control. The user name is just the Subject of the Client’s X509 Certificate (can be determined by running OpenSSL’s openssl x509 command: openssl x509 -noout -subject -in certificate.crt). Note that no password is obtained from the user. Every entry in the user file needs this password: “xxj31ZMTZzkVA”, which is the DES-encrypted version of the word `password”. Those who live under MD5-based encryption (for instance under FreeBSD or BSD/OS, etc.) should use the following MD5 hash of the same word: “$1$OXLyS...$Owx8s2/m9/gfkcRVXzgoE/”.</quoe>


Via: backfill.

SSL, TLS & Perfect Forward Secrecy


  • CRL
  • OSCP
  • Perfect Forward Secrecy (PFS)
  • Elliptic Curve Cryptography (ECC)


  • SSL
  • TLS



  • AES128-SHA

Perfect Forward Secrecy (PFS)


Cipher Suites

    • Optional
    • Required


  • RFC 6090 Fundamental Elliptic Curve Cryptography Algorithms; D. McGrew (Cisco), K. Igoe, M. Salter (NSA); 2011-02.
  • RFC 5246 The Transport Layer Security (TLS) Protocol, Version 1.2; T. Dierks (self), E. Rescoria (RTFM); 2008-08.
  • RFC 5077 Transport Layer Security (TLS) Session Resumption without Server-Side State; J. Salowey (Cisco), H. Zhou (Cisco), P. Eronen (Nokia), H. Tschofenig (Nokia Siemens); 2008-01.
  • RFC 4492 Elliptic Curve Cryptography (ECC) Cipher Suites for Transport Layer Security (TLS); S. Blake-Wilson (SafeNet), N. Bolyard (Sun), V. Gupta (Sun), C. Hawk (Corriente), B. Moeller (Ruhr-Uni Bochum), 2006-05.
  • NIST P-256
  • NIST P-521
  • NIST P-224



  • Bodo Möller, Emilia Käsper  (Google), Adam Langley (Google) => 64bit optimized versions of NIST P-224, P-256 and P-521 for OpenSSL
  • Emilia Käsper (Google)

Package Support


Yet Fedora does not have ECC in OpenSSL

$ openssl ciphers ECDH
Error in cipher list
139915857282912:error:1410D0B9:SSL routines:SSL_CTX_set_cipher_list:no cipher match:ssl_lib.c:1314:
$ rpm -q openssl
$ cat /etc/fedora-release 
Fedora release 18 (Spherical Cow)

Mozilla Network Security Services (NSS)

  • Version?

Client Support

Support for NIST P-256, P-384 and P-521

  • “Recent” versions of Firefox and Chrome (circa 2011-11) “should”
  • “Most” versions, Internet Explorer do not support

Server Support

Apache httpd

  • httpd-2.3.3
  • ensure the order of cipher suites is respected.
    • SSLHonorCipherOrder on
  • Curve is what?
    • Specify with what?


  • nginx-1.0.6.
  • nginx-1.1.0.
  • ensure the order of cipher suites is respected.
    • ssl_prefer_server_ciphers on.
  • Curve is NIST P-256
    • Specify with ssl_ecdh_curve


  • pull/61; Adding support for ECDHE in stud

Cited & Referenced





Cited in Cryptographic Key Length Recommendation

Via & transitively via: backfill, backfill. backfill

On the Security of RC4 in TLS and WPA | AlFardan, Bernstein, Patterson, Poettering, Schuldt

Nadhem AlFardan, Dan Bernstein, Kenny Paterson, Bertram Poettering and Jacob Schuldt; On the Security of RC4 in TLS and WPA; At Their Shop; 2013-03-13, updated 2013-07-08.


  • Single-byte bias attack on TLS.
  • To be presented at USENIX Security 2013, Washington DC, USA, 2013-08-14.
  • Claims <quote>
    • The most effective countermeasure against our attack is to stop using RC4 in TLS. [there are other countermeasures]
    • One of the attacks also applies to WPA/TKIP, the IEEE’s successor protocol to WEP. The most effective countermeasure against our attack against WPA/TKIP is to stop using WPA/TKIP and upgrade to WPA2.


  • Nadhem J. AlFardhan, Daniel J. Bernstein, Kenneth G. Paterson, Bertram Poettering, Jacob C. N. Schuldt; On the Security of RC4 in TLS and WPA; In Proceedings of the USENIX Security Symposium 2013; 2013-07-08; 31 pages.
    Data & Evidence

  • CVE-2013-2566; National Vulnerability Database, National Institute of Standards & Technology, U.S.

    • Description: The RC4 algorithm, as used in the TLS protocol and SSL protocol, has many single-byte biases, which makes it easier for remote attackers to conduct plaintext-recovery attacks via statistical analysis of ciphertext in a large number of sessions that use the same plaintext.
    • Overview: The RC4 algorithm, as used in the TLS protocol and SSL protocol, has many single-byte biases, which makes it easier for remote attackers to conduct plaintext-recovery attacks via statistical analysis of ciphertext in a large number of sessions that use the same plaintext.


Via: backfill.

Aldo Cortesi mitmproxy Announcements through mitmproxy 0.8