9.10. Securing Database Connections

Problem

You're using a database backend in your application, and you want to ensure that network traffic between your application and the database server is secured with SSL.

Solution

MySQL 4.00, PostgreSQL 7.1, and newer versions of each of these servers support SSL-enabled connections between clients and servers. If you're using older versions or another server that's not covered here that does not support SSL natively, you may wish to use Stunnel (see Recipe 9.5) to secure connections to the server.

Discussion

In the following subsections we'll look at the different issues for MySQL and PostgreSQL.

MySQL

By default, SSL support is disabled when you are building MySQL. To build MySQL with OpenSSL support enabled, you must specify the --with-vio and --with-openssl options on the command line to the configuration script. Once you have an SSL-enabled version of MySQL built, installed, and running, you can verify that SSL is supported with the following SQL command:

SHOW VARIABLES LIKE 'have_openssl'

If the result of the command is yes, SSL support is enabled.

With an SSL-enabled version of MySQL running, you can use the GRANT command to designate SSL requirements for accessing a particular database or table by user. Any client can specify that it wants to connect to the server using SSL, but with the GRANT options, it can be required.

When writing code using the MySQL C API, use the following mysql_real_connect( ) function to establish a connection to the server instead of using mysql_connect( ), which has been deprecated. All that is actually required to establish an SSL connection from the client to the server is to specify the CLIENT_SSL flag to mysql_real_connect( ).

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql.h>
   
int spc_mysql_real_connect(MYSQL *mysql, const char *host, const char *pw, 
                           const char *db, unsigned int flags) {
  int         port = 0, result = 0;
  char        *host_copy = 0, *p;
  const char  *socket = 0, *user = 0;
   
  if (host) {
    if (!(host_copy = strdup(host))) return 0;
    if ((p = strchr(host_copy, '@')) != 0) {
      user = host_copy;
      *p++ = 0;
      host = p;
    }
    if ((p = strchr((p ? p : host_copy), ':')) != 0) {
      *p++ = 0;
      port = atoi(p);
    }
    if (*host =  = '/') {
      socket = host;
      host = 0;
    }
  }
   
  /* this bit of magic is all it takes to enable SSL connections */
  flags |= CLIENT_SSL;
   
  if (mysql_real_connect(mysql, host, user, pw, db, port, socket, flags))
    result = 1;
  if (host_copy) free(host_copy);
  return result;
}

If the server is configured to require a peer certificate, the certificate and key to use can be specified in my.cnf, and you should use mysql_options( ) with the MYSQL_READ_DEFAULT_GROUP option to read the appropriate configuration group for your application. The options for the certificate and key to use are ssl-cert and ssl-key, respectively. In addition, use ssl-ca and ssl-capath to set a file or directory containing trusted certificates that are to be used when verifying the peer's certificate. The final option is ssl-cipher, which can be used to specify a specific cipher or cipher set to be used. All of these keys also apply for server configuration.

Alternately, you can use the undocumented mysql_ssl_set( ) function to set the key, certificate, trusted certificate file, trusted certificate directory, and cipher. Because this function is undocumented, it is possible that it will go away or change at any point without warning.^[1]

The prototype for this function is in mysql.h and is as follows:

int STDCALL mysql_ssl_set(MYSQL *mysql, const char *key, const char *cert,
                          const char *ca, const char *capath, const char *cipher);

Finally, note that examination of the MySQL-4.0.10-gamma source code (the latest available at the time of this writing) reveals that if you set a certificate using either configuration file options or the undocumented mysql_ssl_set( ) API, the client will attempt to connect to the server using SSL regardless of whether you specify CLIENT_SSL in the flag passed to mysql_real_connect( ).

PostgreSQL

By default, SSL support is disabled when you are building PostgreSQL. To build PostgreSQL with OpenSSL support enabled, you must specify the --with-openssl option on the command line to the configuration script. Even with a PostgreSQL server build that has OpenSSL support compiled in, the default is still to have SSL support disabled. To enable it, you'll need to set the ssl parameter to on in your postgresql.conf configuration file. When SSL support is enabled, make sure that the files server.key and server.crt contain the server's private key and certificate, respectively. PostgreSQL will look for the two files in the data directory, and they must be present for the server to start.

In a default configuration, PostgreSQL does not require clients to connect to the server with SSL; the use of SSL is strictly a client option. However, clients can be required to use SSL using the hostssl record format in the pg_hba.conf file.

The PostgreSQL C API function PQconnectdb( ) requires that a conninfo object be filled in and passed to it to establish a connection to the server. One of the fields in the conninfo structure is an integer field called requiressl, which allows the client to decide whether SSL should or should not be required for the connection. If this field is set to 1, the connection will fail if the server does not support SSL; otherwise, the use of SSL will be negotiated as part of the connection handshake. In the latter case, SSL will only be used if a hostssl record exists in pg_hba.conf requiring the use of SSL by clients.