Implementing SSL MySQL Backend

RT Users
1

Greetings, RT Users:
Some internal systems changes have prompted me to migrate my database
backend from its present location on the same server as the RT web
frontend to a separate database server elsewhere. To comply with our
security standards, the database traffic should be encrypted, so I
implemented MySQL’s builtin SSL encryption for the database traffic.

I took inspiration for the implementation from a rt-users mailing list
post by Mike Friedman
([rt-users] RT at the University of West Bohemia).
In patch syntax, the precise change I made to RT::Handle was

— /opt/rt3/lib/RT/Handle.pm 2008-11-18 17:23:10.000000000 -0500
+++ /opt/rt3/local/lib/RT/Handle.pm 2008-11-18 17:40:30.000000000 -0500
@@ -123,11 +123,15 @@
if ($RT::DatabaseType eq ‘SQLite’) and
not File::Spec->file_name_is_absolute($RT::DatabaseName);

+my $DBDriver = $RT::DatabaseType;
+$DBDriver .= “:mysql_read_default_file=$RT::MySQLClientConfigFile” if

  • (defined $RT::MySQLClientConfigFile);

    $self->SUPER::BuildDSN(Host => $RT::DatabaseHost,
    Database => $RT::DatabaseName,
    Port => $RT::DatabasePort,

  •   	 Driver => $RT::DatabaseType,

  •   	 Driver => $DBDriver,
  	 RequireSSL => $RT::DatabaseRequireSSL,
        DisconnectHandleOnDestroy => 1
  	);

This also requires placing

Set($MySQLClientConfigFile,‘/etc/my.cnf’);

In RT_SiteConfig.pm, replacing /etc/my.cnf with whatever your mySQL
global configuration defaults file is. This is necessary because the
mySQL client doesn’t even attempt to use SSL encryption unless you give
it the path of a trusted CA certificate file. Using a global
configuration file like this is an easy way to ensure those variables
are passed to the mySQL libraries at run time. You can, I think, specify
them in the actual DSN but it’s quite cumbersome and would require more
text munging than really necessary.

My initial tests have shown that an SSL-encrypted MySQL backend provides
little to no functional slowdown, and appears to be stable, although
others have apparently in the past suggested otherwise.

I am currently using RT 3.6.7 and will likely upgrade to RT 3.8.x in the
relatively near future. Will that upgrade–or future ones–provide a
‘sanctioned’ facility to implement SSL mySQL encryption, and will my
current hack in Handle.pm result in unintended consequences elsewhere in
RT when I upgrade?

Thanks!
Guy

Guy Dickinson, Network Security Analyst
NYU ITS Technology Security Services
guy.dickinson@nyu.edu
(212) 998-3052

2

Greetings, RT Users:
Some internal systems changes have prompted me to migrate my database
backend from its present location on the same server as the RT web
frontend to a separate database server elsewhere. To comply with our
security standards, the database traffic should be encrypted, so I
implemented MySQL’s builtin SSL encryption for the database traffic.

I took inspiration for the implementation from a rt-users mailing list
post by Mike Friedman
([rt-users] RT at the University of West Bohemia).
In patch syntax, the precise change I made to RT::Handle was

So, isn’t there an environment variable one can set to have DBD::mysql
pick this up?

I am currently using RT 3.6.7 and will likely upgrade to RT 3.8.x in the
relatively near future. Will that upgrade–or future ones–provide a
‘sanctioned’ facility to implement SSL mySQL encryption, and will my
current hack in Handle.pm result in unintended consequences elsewhere in
RT when I upgrade?

Your hack looks pretty reasonable. I’d like to hear some feedback on
whether there’s a cleaner way to do this before canonizing it in the
core :wink:

3

Jesse Vincent wrote:

I took inspiration for the implementation from a rt-users mailing list
post by Mike Friedman
([rt-users] RT at the University of West Bohemia).
In patch syntax, the precise change I made to RT::Handle was

So, isn’t there an environment variable one can set to have DBD::mysql
pick this up?

To my knowledge, there are two ways to get DBD::mysql to issue the
appropriate C calls to initiate an SSL-encrypted connection:

  1. Define the relevant parameters in the DSN, a la:

my $dsn =
“DBI:mysql:database=$database;host=$hostname;port=$port;mysql_ssl_ca_file
=/etc/mysqlcerts/ca.pem”;

my $dbh = DBI->connect($dsn, $user, $password);

and so on…

  1. Define the SSL CA file in the [client] portion of /etc/my.cnf and
    then explicitly tell DBI::mysql to read the config file. From the
    DBD::mysql documentation
    (http://search.cpan.org/dist/DBD-mysql/lib/DBD/mysql.pm):

By default MySQL’s C client library doesn’t use any config files unlike
the client programs (mysql, mysqladmin, …) that do, but outside of the
C client library. Thus you need to explicitly request reading a config
file, as in

$dsn = "DBI:mysql:test;mysql_read_default_file=/home/joe/my.cnf";
$dbh = DBI->connect($dsn, $user, $password)

Cheers,
Guy