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
| Reference: | Add ssl to Mysql and Postgresql from our SCG partner Emmanouil Gkatziouras at the gkatzioura blog. |
