Add ssl to Mysql and Postgresql

Adding ssl support to a relational database like mysql or postgresql is a standard task.

First we need to have our certificates ready.
We can either use mysql workbench which has a nice wizard.
Or we can create them using openssl.

In the end we will end up with three files

ssl-ca=ca.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem

We can also check that everything is ok by making a basic test.
Start an open ssl server

/usr/bin/openssl s_server -cert server-cert.pem -key server-key.pem

and a client to connect

openssl s_client -CAfile ca.pem -connect 127.0.0.1:4433

In case of no errors you are good to go.

In case of mysql we shall create a directory and put our certificates in it

mkdir /etc/mysql-ssl
mv ca.pem /etc/mysql-ssl
mv server-cert.pem /etc/mysql-ssl
mv server-key.pem /etc/mysql-ssl
chown -R mysql mysql-ssl

Now we shall edit /etc/my.cnf and on the [mysqld] section add

[mysqld]
ssl-ca=/etc/mysql-ssl/ca.pem
ssl-cert=/etc/mysql-ssl/server-cert.pem
ssl-key=/etc/mysql-ssl/server-key.pem

Now when we login to mysql by issuing show global variables like ‘%ssl%’ we get

mysql> show global variables like '%ssl%';
+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| have_openssl  | YES                            |
| have_ssl      | YES                            |
| ssl_ca        | /etc/mysql-ssl/ca.pem          |
| ssl_capath    |                                |
| ssl_cert      | /etc/mysql-ssl/server-cert.pem |
| ssl_cipher    |                                |
| ssl_crl       |                                |
| ssl_crlpath   |                                |
| ssl_key       | /etc/mysql-ssl/server-key.pem  |
+---------------+--------------------------------+

Suppose we have a database called tutorial_database, we will create a user that will have access to it only through ssl

create user 'tutorial_user'@'%' identified by 'yourpass';
grant all privileges on tutorial_database.* to 'tutorial_user'@'%' REQUIRE SSL;;

It order to connect with this user for example by using mysql client you need

mysql --ssl-ca=ca.pem -u tutorial_user -h yourhost -p

Using the ca.pem created previously

Now on postgresql things are easy too

Place your server certificate and your server key to your postgres data directory

cp server-cert.pem $PGDATA/server.crt
cp server-key.pem $PGDATA/server.key

Also change your server key properties or else postgresql will not start

chmod og-rwx server.key

Next step is to edit postgresql.conf and add

ssl=on

After restarting we will be able to connect through ssl to postgres. Just add the ssl setting.

psql "sslmode=require host=yourhost dbname=tutorial_database" tutorial_user

However if we want a specific user to connect to a database with ssl then we should edit pg_hba.conf

# TYPE  DATABASE          USER            ADDRESS  METHOD
hostssl tutorial_database tutorial_user   all      md5