How to encrypt database fields transparently for your app using Acra and DigitalOcean managed PostgreSQL

Bookmark this tutorial made by security engineers for you to implement transparent field-level encryption using Django-based web app, free Acra 1-Click App, and DigitalOcean-managed PostgreSQL.

However, Acra works for any web apps and PostgreSQL/MySQL databases, so feel free to use this tutorial as a starting point for your apps.

🚀 Click here to see what you get as a result.

---

🔎 5 kinds of encryption for database data protection

First, let’s have an overview of various kinds of encryption you can use to protect your database data.

• Enabling TLS between app and database protects data in transit. Still, fields are stored in plaintext in a database, available for users with direct access to a database, exposed in server memory and backups.
• Data at rest encryption helps to encrypt all the data in the database while stored on disk. But it doesn’t prevent reading data from database memory/backups for those who have access to a database, and attacks aimed at stealing data like SQL injections.
• Field-level encryption means that each database field is encrypted (sometimes by a unique key). It covers both cases: data is encrypted during storage, in memory, and transit. It provides better security guarantees than encryption of the entire database, as database owners can’t get access to decrypted data.
• Application-side field-level encryption means that application is responsible for encryption/decryption and handles cryptographic code and keys. Cracking the app exposes the risk of accessing and decrypting fields that were not supposed to be accessed by this app.
• Transparent field-level encryption: moving encryption and decryption to a separate service decreases the chance of data leakage and helps to control the attack surface. This makes encryption “transparent” for the application and the database. The encryption service should handle code and keys with care.

We introduce this service – Acra database security suite. Acra handles cryptography and key management, while the app and database don’t notice that data is encrypted.

---

🎯 Set security goal and scenario

Now let’s set up a goal. What exactly are we trying to protect in this tutorial?

The target application is a blog app, based on Django, forked from djangoproject.com open source repo.

So, the goal is to protect sensitive fields, e.g. author’s name, body of the post, metadata and store them encrypted in the database.

Next point is the scenario.

We put Acra suite between the blog app and the database, as a reverse proxy server. This Acra component is known as AcraServer, and it is available at DigitalOcean marketplace as a free Acra 1-Click App.

Follow the idea.

We connect the blog app to the AcraServer, then AcraServer to the database. The app talks to the AcraServer like it is a database: by sending queries to it. AcraServer encrypts the received data and sends it to the database. On reading data, AcraServer requests the data from the database, decrypts it, and returns it to the application.

What next?

AcraServer authenticates the application using cryptographic keys, so if the app was cracked and it doesn't have the correct keys, the decryption fails and attackers don’t get the data.

In addition to encryption, AcraServer provides such security measures as SQL firewall, intrusion detection, key rotation and revocation utils, SIEM integration, and the list can go on and on.

---

💻 Initial setup. Have your web app and database running

We assume that you meet a few prerequisites before starting this tutorial:

• Your web application and managed PostgreSQL instance are deployed on DigitalOcean:

• Your web application is deployed and public:

Alternatively, you can use any of your existing apps or deploy an example Django app using the following instructions:

• How To Set Up Django with Postgres, Nginx, and Gunicorn on Ubuntu 16.04
• How to Set Up a Scalable Django App with DigitalOcean Managed Databases and Spaces

---

👐 Manual setup

Here you are going to add AcraServer Droplet to the existing infrastructure, configure it to work as database proxy, and update the web app to use AcraServer instead of the database.

Let's do it step by step.

❇️ Step 1. Install Acra 1-Click App

1. Go to Create > Droplets > Marketplace > See all Marketplace Apps.

2. Type “Acra” in the search field. You should find Acra 1-Click App.

3. Click the 'Create Acra Droplet' button, select plan and datacenter region (we recommend selecting the same location for all your droplets and database clusters).

Note: It's better to minimise the number of SSH keys you use to access Acra Droplet. As AcraServer will encrypt and decrypt the data, you don't want many users to be able to connect to it.

Now, all the components of the infrastructure are ready – web app, Acra, and database cluster.

❇️ Step 2. Collect connection parameters

You will need the following connection parameters to continue the configuration process, let’s locate them:

ACRA_HOST
DJANGO_HOST
DB_CERTIFICATE
DB_HOST
DB_PORT

Look for connection details and CA certificate in the setting of your database cluster:

Look for host IPs of your Droplets (Acra host IP and Django host IP):

❇️ Step 3. Configure AcraServer

⚙️ Automation makes everything better! Feel free to jump to Automation with Ansible part and perform ❇️ Steps 1-3 to setup database and AcraServer using Ansible script.

Open a terminal on your local machine and connect to AcraServer:

ssh root@<ACRA_HOST>

Upon connection, you'll see the configuration script with a step-by-step guide.

The script will ask for the connection parameters to connect AcraServer to the database and the web app and encryption configuration – you will need to specify which tables and columns to encrypt:

* Hostname: ACRA_HOST
* Allowed hosts: DJANGO_HOST
* CA certificate:  DB_CERTIFICATE
* DB host: DB_HOST
* DB port: DB_PORT
* Table: blog_entries (name of table which AcraServer will encrypt)
* Columns: id headline slug is_active pub_date content_format summary summary_html body body_html author
* Encrypt columns: author body body_html headline summary summary_html
* Table: (skip further tables, just press 'Enter')

Finally, you should see something similar to this:

Configuration of AcraServer: network

Configuration of AcraServer: database

Excellent. AcraServer was successfully configured! ✨

❇️ Step 4. Modify the source code of web application

Encrypted data is binary data. As AcraServer doesn't know the nature of data, it returns the decrypted binary data to the web app. You’ll need to change the source code of your web app for the app to expect the decrypted data as binary, then to encode it into the original format (strings, numbers, bytes, etc.).

Connect to the DJANGO_HOST (the Droplet that runs your blog based on djangoproject.com application) and update the web application’s settings one by one. Follow the typical scenario of building and setting up djangoproject.com and rebuild your application after changing the code.

Update blog/fields.py with encoding/decoding functions.

diff -urN djangoproject.com/blog/fields.py patched/blog/fields.py
--- djangoproject.com/blog/fields.py    1970-01-01 03:00:00.000000000 +0300
+++ patched/blog/fields.py  2019-10-06 18:56:08.232183901 +0300
@@ -0,0 +1,53 @@
+from binascii import hexlify
+from django.db import models
+from django.utils.translation import gettext_lazy as _
+import codecs
+
+
+class PgTextBinaryField(models.TextField):
+    description = _("Text as binary data")
+
+    def from_db_value(self, value, expression, connection):
+        return db_value_to_string(value)
+
+    def get_db_prep_value(self, value, connection, prepared=False):
+        return string_to_dbvalue(value)
+
+
+class PgCharBinaryField(models.CharField):
+    description = _("Chars as binary data")
+
+    def from_db_value(self, value, expression, connection):
+        return db_value_to_string(value)
+
+    def get_db_prep_value(self, value, connection, prepared=False):
+        return string_to_dbvalue(value)
+
+
+def bytes_to_string(b):
+    if len(b) >= 2 and b[0:2] == b'\\x':
+        return codecs.decode(b[2:].decode(), 'hex').decode('utf-8')
+
+    return b.decode()
+
+
+def memoryview_to_string(mv):
+    return bytes_to_string(mv.tobytes())
+
+
+def db_value_to_string(value):
+    if isinstance(value, memoryview):
+        return memoryview_to_string(value)
+    elif isinstance(value, bytes) or isinstance(value, bytearray):
+        return bytes_to_string(value)
+
+    return value
+
+
+def string_to_dbvalue(s):
+    if s == '':
+        return b''
+    elif s is None:
+        return None
+
+    return '\\x{}'.format(bytes(s, 'utf-8').hex()).encode('ascii')`
```
⚙️ Automation makes everything better! Feel free to jump to <a href="#automation-with-ansible">Automation with Ansible</a> and perform ❇️ **Step 4** to setup your web application using an Ansible script.


Migrate data to `binary` format and add migration script `blog/migrations/0003_encrypt.py`:

```diff
diff -urN djangoproject.com/blog/migrations/0003_encrypt.py patched/blog/migrations/0003_encrypt.py
--- djangoproject.com/blog/migrations/0003_encrypt.py   1970-01-01 03:00:00.000000000 +0300
+++ patched/blog/migrations/0003_encrypt.py 2019-10-06 18:57:14.743866140 +0300
@@ -0,0 +1,43 @@
+# -*- coding: utf-8 -*-
+from __future__ import unicode_literals
+
+from django.db import migrations, models
+
+class Migration(migrations.Migration):
+
+    dependencies = [
+        ('blog', '0002_event'),
+    ]
+
+    operations = [
+        migrations.AlterField(
+            model_name='entry',
+            name='author',
+            field=models.BinaryField(),
+        ),
+        migrations.AlterField(
+            model_name='entry',
+            name='body',
+            field=models.BinaryField(),
+        ),
+        migrations.AlterField(
+            model_name='entry',
+            name='body_html',
+            field=models.BinaryField(),
+        ),
+        migrations.AlterField(
+            model_name='entry',
+            name='headline',
+            field=models.BinaryField(),
+        ),
+        migrations.AlterField(
+            model_name='entry',
+            name='summary',
+            field=models.BinaryField(),
+        ),
+        migrations.AlterField(
+            model_name='entry',
+            name='summary_html',
+            field=models.BinaryField(),
+        ),
+    ]
```

Update the `blog/models.py` file to encode the data from `binary` type back into its original data type (strings, numbers, chars, etc.):

```diff
diff -urN djangoproject.com/blog/models.py patched/blog/models.py
--- djangoproject.com/blog/models.py    2019-10-09 19:37:15.829280692 +0300
+++ patched/blog/models.py  2019-10-06 18:57:30.015795274 +0300
@@ -10,6 +10,8 @@
 from django_hosts.resolvers import reverse
 from docutils.core import publish_parts

+from .fields import PgCharBinaryField, PgTextBinaryField
+
 BLOG_DOCUTILS_SETTINGS = {
     'doctitle_xform': False,
     'initial_header_level': 3,
@@ -35,7 +37,7 @@


 class Entry(models.Model):
-    headline = models.CharField(max_length=200)
+    headline = PgCharBinaryField(max_length=200)
     slug = models.SlugField(unique_for_date='pub_date')
     is_active = models.BooleanField(
         help_text=_(
@@ -53,11 +55,11 @@
         ),
     )
     content_format = models.CharField(choices=CONTENT_FORMAT_CHOICES, max_length=50)
-    summary = models.TextField()
-    summary_html = models.TextField()
-    body = models.TextField()
-    body_html = models.TextField()
-    author = models.CharField(max_length=100)
+    summary = PgTextBinaryField()
+    summary_html = PgTextBinaryField()
+    body = PgTextBinaryField()
+    body_html = PgTextBinaryField()
+    author = PgCharBinaryField(max_length=100)

     objects = EntryQuerySet.as_manager()
```

###These are all the necessary source changes! 

No encryption, no magic – the application doesn't know that data will be encrypted/decrypted by an external party.

>Note: For your convenience, we’ve created a [patch that applies this modification] (https://github.com/cossacklabs/acra-engineering-demo/blob/master/acra-do-deploy/acra_modifications.patch).

### <a name="step-4">❇️ Step 4. Modify the network settings of the web application</a>

Now, the web application needs to be connected to AcraServer instead of the database, making SQL queries from the app go through AcraServer to the database and back.

The typical way to configure the connection settings of Django apps is to use `$DJANGOPROJECT_DATA_DIR/conf/secrets.json` file in `DJANGO_HOST`.

Prepare and add to the environmental variables the following parameters:

* `$ACRA_HOST` – IP address of Acra Droplet
* `$DJANGO_HOST` – IP address of your djangoproject.com-based blog
* `$ACRA_PORT` – port of AcraServer, 9393 by default
* `$POSTGRES_DJANGO_PASSWORD` – password of `djangoproject` user, specified on the stage of setting up a database for application

Next, please modify `$DJANGOPROJECT_DATA_DIR/conf/secrets.json` the following way:

```json
{
  "secret_key": "$(dd if=/dev/urandom bs=4 count=16 2>/dev/null | base64 | head -c 32)",
  "superfeedr_creds": ["[e-mail]", "some_string"],
  "db_host": "$ACRA_HOST",
  "db_password": "$POSTGRES_DJANGO_PASSWORD",
  "db_port": "$ACRA_PORT",
  "trac_db_host": "$ACRA_HOST",
  "trac_db_password": "$POSTGRES_DJANGO_PASSWORD",
  "trac_db_port": "$ACRA_PORT",
  "allowed_hosts": ["$DJANGO_HOST", "www.$DJANGO_HOST"],
  "parent_host": "$DJANGO_HOST"
}
```

Host/port of your database was changed to the host/port of AcraServer. 

Next, restart your application.

### <a name="step-5">❇️ Step 5. Test that encryption is working </a>

Let's see Acra at work. 

Create a blog post via the **admin panel** of your application.

To do it, go to the admin page by typing `DJANGO_HOST/admin` in browser:

![by Cossack Labs](https://dev-to-uploads.s3.amazonaws.com/i/5m6dbv75z2conhmomnew.png)

Put `admin` / `admin` as username / password and log into the admin page *(yeah, these are the default credentials for the Django example app, who knows how many apps are still using them in production)*:

![by Cossack Labs](https://dev-to-uploads.s3.amazonaws.com/i/1mejgw3lk8e0cxu37a4e.png)

Find the **Blog** category. In **Entries**, click **+ Add**:

![by Cossack Labs](https://dev-to-uploads.s3.amazonaws.com/i/vo2lgumkbb2dak9rw4kt.png)

Fill all the necessary fields and text boxes:

![by Cossack Labs](https://dev-to-uploads.s3.amazonaws.com/i/feuzcmh4ifn6pcdxwbyd.png)

Click **Save** at the bottom of the page. This will create an encrypted blog record:

![by Cossack Labs](https://dev-to-uploads.s3.amazonaws.com/i/x3dih8zh569po8q28utc.png)

Visitors of your site will see the blog posts in plaintext. You can check this by opening `DJANGO_HOST/weblog` in browser:

![by Cossack Labs](https://dev-to-uploads.s3.amazonaws.com/i/2670ebl5mbz2ttzolyjg.png)

But the blog posts are encrypted under the hood. Open `djangoproject` database, open table `blog_entries` and query data to see it for yourself:

```shell
PGPASSWORD=$POSTGRES_DJANGO_PASSWORD psql -h $DB_HOST -U djangoproject -p $DB_PORT -c 'select * from blog_entries;'
```

![by Cossack Labs](https://dev-to-uploads.s3.amazonaws.com/i/06wj58fbxtr1guv0u6st.png)

AcraServer now works as a proxy, encrypting and decrypting data transparently for the application. This way, if the app or the database are cracked, this won't lead to data compromise because every data field is encrypted using unique keys. 

You can read more about [how Acra works] (https://www.cossacklabs.com/acra/) and how to use it for different types of applications.

If you’ve had some difficulties following this instruction, [Cossack Labs’ Github repo] (https://github.com/cossacklabs/acra-engineering-demo/blob/master/acra-do-deploy/ansible/README.md) has a more detailed instruction for building djangoproject.com from sources manually and deploying it on DigitalOcean together with Acra. It should make following this tutorial a breeze.

If you’d like to automate a few steps in this process, proceed to the Ansible automation instructions below.

***

###<a name="automation-with-ansible">Automation with Ansible</a>

Everything is easier with automation. We’ve created [a few Ansible scripts] (https://github.com/cossacklabs/acra-engineering-demo/tree/master/acra-do-deploy/ansible) for you to make configuring your web app and Acra easier.

Start with revisiting the <a href="#step-3"><b>❇️ Step 3</b></a> in the main tutorial where you have 3 droplets in DigitalOcean account: Django web app, Acra 1-Click App, and PostgreSQL managed database cluster:

![by Cossack Labs](https://dev-to-uploads.s3.amazonaws.com/i/xxqy16676zehl4vectwx.png)

To configure your infrastructure automatically, perform the following steps from your local machine:

###✔️ Step 1. Create database
Run the following command to create two users and two databases: djangoproject and code.djangoproject (run from your local machine):

```shell
PGSSLMODE=require PGPASSWORD=<password_to_user_doadmin> psql -h <postgres_host> -p <postgres_port> -U doadmin -d defaultdb
create user djangoproject with password 'secret';
create user "code.djangoproject" with password 'secret';
create database djangoproject;
create database "code.djangoproject";
\q
```

###✔️ Step 2. Run script to configure AcraServer

Run the following command to configure AcraServer to connect to the database (run from your local machine):

```shell
ansible-playbook acra-ansible-script.yml -i <acra_droplet_ip>, --extra-vars "db_host=<postgres_host> db_port=<postgres_port> acra_host=<acra_droplet_ip> acra_port=9393 django_host=<django_droplet_ip>"
```

###✔️ Step 3. Run script to configure web application

Run the following command to configure Django web app to connect to the AcraServer (run from your local machine):

```shell
ansible-playbook django-ansible-script.yml -i <django_droplet_ip>, --extra-vars "django_host=<django_droplet_ip> acra_host=<acra_droplet_ip> acra_port=9393 postgres_admin_password=<password_to_user_doadmin> postgres_django_password=secret"
```

###✔️ Step 4. Test that encryption is working

Repeat the same steps as described above to make sure that blog posts are displayed in plaintext but are stored in a database in encrypted form.

***

### <a name="result">🎉 What you got now after all</a>

➤ [Acra 1-Click App] (https://marketplace.digitalocean.com/apps/acra) helps you to protect web apps, financial apps, critical infrastructure, e-commerce projects, and much more. It can encrypt sensitive data fields transparently for the app and database. And that’s just the free and open-source Acra Community Edition!

[Acra Pro and Acra Enterprise versions] (https://www.cossacklabs.com/acra/#pricing) are paid powerhouses of encryption and security that can cover most of your data protection business needs and use cases.  

<a href="#top">Get to the top ▲</a>

***

This post is authored by [Artem Storozhuk](https://www.linkedin.com/in/artem-storozhuk-1b767966/), security software engineer at [Cossack Labs](https://www.cossacklabs.com/). Say hi to us 👋

*** 

**P.S.** New DigitalOcean users get $100, 60-day credit when they [register using Cossack Labs referral code] (https://marketplace.digitalocean.com/apps/acra?refcode=3477f5f54884). Try Acra now! 

Comments

[pvleap]

Hi Artem,
Thanks for the detailed article. I am trying to integrate a python restful service app with a postgres db and are trying to transparently encrypt 2 fields in a postgres table that are of text datatype. We are using only acrakeymaker to generate the writer keys and acra server and no other acra components. The acra server set up by us transparently encrypts data inserted/updated by us and decrypts it when we retrieve it using SELECT statements but we find that the decrypted data is in HEX encoded format, while the python app expects the data in clear text format. Please let me know if there are any options by which we can make acra server to return the clear text data without us having to make any changes to our pythin app and without writing any wrapper classes? Also does acra server have the option of configuring custom converters that can be plugged into it which will do the decoding of the HEX encoded text? Thanks.

[vixentael]

Hi pvleap!
We've seen your similar question on Github, and replied there.
github.com/cossacklabs/acra/issues...

[pvleap]

Yes saw it.. Thanks for the prompt response. cheers.