Securing access to Azure PostgresDB with Stunnel

By default new the [Azure Postgres PaaS offering](https://docs.microsoft.com/en-gb/azure/postgresql/overview) from Microsoft only allows traffic over SSL and actively encourages you to continue this to discourage Man in the Middle attacks and so on. If you are in full control of the client application then this should be fine and you could follow their [suggested approach](https://docs.microsoft.com/en-gb/azure/postgresql/concepts-ssl-connection-security), however if this is not the case then you need an alternative solution.

A great alternative is to use Stunnel https://www.stunnel.org:

Stunnel is a proxy designed to add TLS encryption functionality to existing clients and servers without any changes in the programs' code. Its architecture is optimized for security, portability, and scalability (including load-balancing), making it suitable for large deployments.

The steps to get this working are as follows:

1. Create an Azure PostgresDB server
2. Install the PSQL client tools (to test)
3. Install Stunnel client side
4. Configure Stunnel for Postgres
5. Connect to your Postgres Server

To understand the problem, let's create a new Azure PostgresDB instance and try and connect via PSQL.

Create an Azure PostgresDB

Log into the Azure portal and search the marketplace for PostgreSQL (or click here to go directly). You will see a list like below and you are looking for "Azure Database for PostgreSQL (preview)".

Click on that item and on the create blade you can enter the various details to create your Postgres instance, taking note of the username and password you enter. You don’t yet need to create a database as we will connect to the default database just to test the various connection scenarios.

By default nothing will be able to connect to the server. You must add the client ip to the Postgres database on Azure.

You can get your ip from a terminal window by entering the following:

$ dig TXT +short o-o.myaddr.l.google.com @ns1.google.com

"23.43.124.34"

Add this IP via the Connections Security tab within the Settings section of the Postgres Azure instance.

Now we have an instance, we can try connecting to it.  You need the server name which you will find on the Overview tab for the Postgres instance. It will be of the form:

mypgsql.postgres.database.azure.com

Attempt to insecurely connect to PostgresDB

To connect to Postgres we are going to install the psql client tools (you can alternatively get a cross platform GUI at https://www.pgadmin.org/), so in your terminal you can enter the following command:

sudo apt-get update && apt-get install postgresql-client

With the client installed you can now attempt to connect to the Postgres server and the default database. Note that psql is smart enough to negotiate SSL to create a secure connection, so we want to tell it NOT to do this in order to show what clients that do not do this will experience.

psql -h servername.postgres.database.azure.com -U username@servername “dbname=postgres sslmode=disable”

Note the servername if your servername and that you must add the servername when specifying the username, as highlighted above.

However, you will get the following error:

psql: FATAL:  SSL connection is required. Please specify SSL options and retry.

Now, fixing this in psql is simple but the reality (as it was in my case) is that the client libraries used by applications you use may not do the negotiation, so you need do look at Stunnel to help here.

Install and Configure Stunnel

To use Stunnel, you need to first install it, then configure it for the target service, and connect to the endpoint from psql via the Stunnel interface.

First install Stunnel

sudo apt-get install stunnel4

Next we want to configure Stunnel to talk to our Postgres instance.

nano /etc/stunnel/stunnel.conf

The following is an example of the configuration for Stunnel.

pid = /tmp/stunnel-postgres.pid
client = yes

[postgres-server]
protocol = pgsql
accept = 127.0.0.1:5432
connect = servername.postgres.database.azure.com:5432options = NO_TICKET
retry = yes

Next, enable Stunnel SSL tunnels.

sudo sed -i "s/ENABLED=0/ENABLED=1/" /etc/default/stunnel4

Finally, restart Stunnel.

sudo service stunnel4 restart

Now we can connect to Postgres securely.

psql -h 127.0.0.1 -U username@servername -d postgres

Now you should be presented with the postgres prompt for your Postgres server in Azure.

postgres=> \du

Role name     |
----------------
azure_pg_admin
azure_superuser
...