Using stunnel to Encrypt Database Connections

Most RDBMS such as PostgreSQL and MySQL support TLS connections to encrypt the data on the wire between the application and the database. However there also seems to be applications here and there that don’t support TLS connections to the database server.  Not the end of the world when your app is on the same server as the database server, but who does that? ????

That’s where stunnel comes in to play.  stunnel is open source software that creates an encrypted tunnel  that is very easy to install (most major OS distros have it in their package manager) and there are even binaries available for Android and Windows!  But enough about that, as there is plenty of information on the stunnel site.  So how does stunnel work?  Here is a simple diagram that shows an overview of stunnel in action:

stunnel overview

  1. On the database server, PostgreSQL is listening on its standard port on 127.0.0.1:5432 (localhost) and stunnel will listen on an external interface, eg. 192.168.12.34 and on a different port of your choosing (we will use 6543 in this example).
  2. Through the configuration of stunnel, traffic coming in to the interface 192.168.12.34:6543 will then pass to PostgreSQL listening on 127.0.0.1:5432.
  3. On the application server, stunnel is listening on 127.0.0.1:5432, so the application can be configured to talk to 127.0.0.1:5432, just as if PostgreSQL was installed on the application server instead of across the network.

Stunnel Configuration

The configuration file format is pretty much the same on the different OSes, you just need to find where your favorite distro stores the configuration file(s) by default.  Another benefit of stunnel is that you can run multiple instances for different applications, etc. as long as they are listening on different ports, of course.  You can also “multiplex” the connections to one endpoint.  For example, you could have multiple application servers using stunnel to connect to the same ip address / port on the database server.  So, on to the configuration examples.

We need to setup the keys/certificates for the TLS encryption.  For stunnel, there are two ways to do this:

  1. PKS (shared keys) – This is the simplest way to setup stunnel and recommended if you don’t have an in-house certificate authority or do not want to setup one.
  2. PKI (public key) – If you have your own CA setup you can use  the CA root certificate and client key/certificate pair.  stunnel can use certificates from a commercial CA as well.

See the stunnel authentication page for more information on these methods.

PSK Example:

Say we are are running PowerDNS for our DNS needs and we are using PowerDNS’s pgsql backend.  For this example, we will use PKS.  On the database server, we will use this stunnel config:

; setup stunnel for PostgreSQL server
fips = no

; create a dedicated user/group (optional) 
setuid = stunnel
setgid = stunnel

; make sure the directory has the necessary perms for stunnel to create the pid file
pid = /var/run/stunnel/stunnel.pid

; Log level. WARN=4, DEBUG=7
debug = 4

[pgsql]
client = no
accept = 192.168.12.34:6543
connect = 127.0.0.1:5432
sslVersion = TLSv1.2
ciphers = PSK
PSKsecrets = /etc/stunnel/psk.txt

The line “client = no” indicates that this is an stunnel server.  The PSKsecrets file contains a secret for each client that will connect:

app01:GyU6K4d7z7l68OdJ1yYzzltFpnd7wTxWNw8cshYiDd9OZfShmy9nR6bUEfmyjLR
app02:UqvhC7LegFjDDYGWl9LFmh6M7ccNs6v7NEQCQzOyfkY50jNcWriscpDAvXzkk8a

Each client connecting to an stunnel server should have its own PSK.  Here is the stunnel configuration for the client, in this case app01:

; setup stunnel for PowerDNS
fips = no

setuid = stunnel
setgid = stunnel
pid = /var/run/stunnel_pgsql.pid

; Log level. WARN=4, DEBUG=7
debug = 4

[pgsql]
client = yes
accept = 127.0.0.1:5432
connect = 192.168.12.34:6543
sslVersion = TLSv1.2
ciphers = PSK
PSKsecrets = /etc/stunnel/app01_psk.txt

Here we set the line “client = yes” to indicate that this is an stunnel client.  The PSKsecrets file will contain just one line, the key from the app01 line on the server’s PSKsecrets file:

app01:GyU6K4d7z7l68OdJ1yYzzltFpnd7wTxWNw8cshYiDd9OZfShmy9nR6bUEfmyjLR

PKI Example

For the PKI example, we use most of the same config, with the exception that we configure the server and client to use certificates and private keys.  The setup is very similar to how you would setup an HTTP server to serve TLS.  For this we will use an in-house certificate authority.  Here is the server configuration:

; setup stunnel for PostgreSQL server

fips = no

; create a dedicated user/group (optional)
setuid = stunnel
setgid = stunnel

; make sure the directory has the necessary perms for stunnel to create the pid file
pid = /var/run/stunnel/stunnel.pid

; Log level. WARN=4, DEBUG=7
debug = 4

[pgsql]
client = no
accept = 192.168.12.34:6543
connect = 127.0.0.1:5432
sslVersion = TLSv1.2

; the db_server.pem contains the private key, server certificate, and the CA root certificate
cert = /etc/ssl/private/db_server.pem

The .pem file contains the entire chain, e.g.:

-----BEGIN PRIVATE KEY-----
MI3f8AM0nsdanraZxJw4T8XMuhdnaDIpLgUaYylqC77533wz2HasAwzeL2HoRekn
 ... private key data
-----END PRIVATE KEY-----
-----BEGIN CERTIFICATE-----
Md3i1dJf8IXfhod7bOzfanBuVlQVrGzEMxvqTXnIpInM8NKlRrLs5T59Zlm8vYUl
 ... server certificate data
-----END CERTIFICATE-----
-----BEGIN CERTIFICATE-----
VQE9qMQkeM3tb7WWLOJfqMD9vRo419xGOySX8h7PLPKYSGGm8ufXKQxj6ok3WTPw
 ... CA certificate
-----END CERTIFICATE-----

For the client, the configuration would be:

; setup stunnel for PostgreSQL
fips = no
setuid = www-data
setgid = www-data
pid = /var/run/stunnel_pgsql.pid

; Log level. WARN=4, DEBUG=7
debug = 4

[mysql]
client = yes
accept = 127.0.0.1:5432
connect = 192.168.12.34:6543
sslVersion = TLSv1.2

; the client only needs the CA root certificate
CAfile = /etc/ssl/certs/CA_Root.crt

While maintaining a certificate authority is not the most fun, it has the advantage that there are no secrets necessary on the client(s), which can also mean it is easier for configuration management tools (Ansible, Puppet, etc.).

0 (0)
Article Rating (No Votes)
Rate this article
Attachments
There are no attachments for this article.
Comments
There are no comments for this article. Be the first to post a comment.
Full Name
Email Address
Security Code Security Code
Related Articles RSS Feed
How to disable SSH cipher/ MAC algorithms for Linux and Unix
Viewed 46679 times since Fri, Aug 21, 2020
RHEL : How to deal with “CLOSE_WAIT” and “TIME_WAIT” connection
Viewed 26125 times since Thu, Feb 14, 2019
BIND for the Small LAN
Viewed 3433 times since Sun, May 20, 2018
Używanie rsync poprzez Secure Shell
Viewed 41256 times since Thu, May 24, 2018
Extending Linux LVM partitions script
Viewed 6432 times since Wed, Feb 6, 2019
Linux: how to monitor the nofile limit
Viewed 10710 times since Wed, Jul 25, 2018
Linux Proxy Server Settings – Set Proxy For Command Line
Viewed 3549 times since Mon, Feb 18, 2019
Linux - Cannot login from remote console but can access via ssh
Viewed 5214 times since Fri, Jun 8, 2018
How to find the largest files and directories in Linux?
Viewed 3340 times since Sun, May 20, 2018
LVM: Reduce SWAP size by shrinking existing Logical Volume
Viewed 6238 times since Sat, Jun 2, 2018