Acra provides selective encryption, multi-layered access control, SQL firewall (SQL injection prevention), database leakage prevention, and intrusion detection capabilities as server deployed in your infrastructure (or cloud).
Working as database proxy for SQL databases, Acra allows performing client-side or server-side encryption as easily as redirecting your app to AcraServer instead of a database (see Acra Documentation for more information).
This article is a step-by-step guide that illustrates how to setup AcraServer for work with DigitalOcean managed Postgres database.
AcraServer can work in different modes, depending on your preferences and security requirements:
- Server-side encryption – AcraServer works as transparent proxy, intercepting SQL traffic from backend app to the database and back, and encrypting and decrypting the payload. You select which fields to encrypt. Neither database nor backend app can decrypt the data (so no plaintext data leaks out in case of a data breach);
- Client-side encryption where backend application encrypts the data using Acra client-side library (AcraWriter) and AcraServer only decrypts it. You decide which data to encrypt in your code.
Acra 1-Click App has an interactive configuration script that prepares AcraServer for work in server-side encryption mode (as transparent proxy) with managed PostgreSQL database.
This is what the high-level scheme looks like:
You can read about the other possible configuration options (including MySQL and NoSQL databases) in Acra's GitHub repository or check out the example applications for typical infrastructures (using Python and Ruby web applications, Acra, and various databases).
Step 1: Creating Acra droplet
1. If you’re new to DigitalOcean, you can register using Cossack Lab referral code – follow this link to get a $100, 60 day bonus.
3. Configure droplet properties. Start with choosing your Droplet plan (Standard, General Purpose, or CPU Optimized; learn more about DigitalOcean pricing and available configurations).
Any payment plan in the “Standard” section should be enough to run Acra 1-Click App.
4. Choose a hostname, following the instructions. It can only contain alphanumeric characters, dashes, and periods.
A unique name will be automatically generated for you by DigitalOcean – i.e. “acra-server-lon1-62793” generated in the screenshot below, you can change it.
Step 2: Installing PostgreSQL Droplet
If you already have PostgreSQL Droplet installed, go directly to step 2.2.
1. You need to setup DigitalOcean managed PostgreSQL (remember that you need to have DigitalOcean account first to do it).
Start with setting up a database cluster – choose the database engine: PostgreSQL. Choose a cluster configuration: $15/month (any basic plan will do).
Next, choose a unique name for your database cluster. Same rules apply as on the “choose hostname” step – unique name will be automatically generated for you by DigitalOcean, you can change it (remember, only alphanumeric characters, dashes, and periods).
2. After setting up DigitalOcean managed Postgresql, you’ll get database connection parameters and CA certificate. Download the CA certificate – you need it for configuring AcraServer.
Step 3: Configuring PostgreSQL Droplet
1. Add your machine’s IP and hostname of AcraServer Droplet as an allowed inbound source. This will allow you and Acra to connect to the database:
You server IP is only temporary necessary for configuration purposes. After completing the configuration, PostgreSQL database will communicate with AcraServer, so you can remove your IP later.
If you already have an existing backend application that connects to the database, you should keep its address among the inbound sources until you change the applications’ connection string to AcraServer (see Step 6: Configuring client (backend) application and adding some data, point 1).
2. Make sure you have
psql installed on your machine (typically you’ll need to install PostgreSQL for this). If you use macOS, run
brew install postgresql.
3. Connect directly to the database using its connection string (screenshot above) from your machine:
4. Create an example table and make sure it exists:
CREATE TABLE users( id serial PRIMARY KEY, name VARCHAR (50) UNIQUE NOT NULL, password VARCHAR (50) NOT NULL, email VARCHAR (355) UNIQUE NOT NULL ); SELECT * FROM users;
This is what you’ll get:
Okay, the database is up and running. The table is ready to get some data.
Step 4: Configuring AcraServer to encrypt and decrypt data
Now, it’s time to configure AcraServer – it should know how to connect to the database, which CA certificate to use, and – what’s most important – what fields to encrypt.
1. Connect to the AcraServer Droplet from your machine:
AS_HOST="AcraServer_droplet_IP" ssh root@$AS_HOST
2. Right after the connection is established, configuration script will start. This script will perform configuration for you. You can run this script as many times as necessary, in case you need to change something:
3. Select AcraServer hostname:
4. List hosts that are allowed to connect. You need to put here the host, which will be connected to the database through AcraServer (usually your backend application). Don’t forget to include your own host if you plan to play with the infrastructure manually.
Note: if you fail to list hosts, AcraServer won’t accept any external connections and you will need to start the configuration process again.
You can get your external IP using:
dig @resolver1.opendns.com ANY myip.opendns.com +short
5. Configure TLS. DigitalOcean generates individual TLS chain for each PostgreSQL instance. That chain uses the self-signed CA certificate. AcraServer needs this CA in order to verify the certificate of the database server. Copy the CA certificate from PostgreSQL Droplet and paste the certificate into the configurator when you're prompted for it.
6. Configure the connection to the database: PostgreSQL host and port.
7. Configure the structure of tables in your database, and select which fields to encrypt. Use the same table as you’ve created on previous step:
- Columns (
column1 column2 ...):
id name password email
- Columns (
column1 column2 ...):
name password email
After this, you'll get AcraServer that's fully configured and is ready to work.
Step 5: Changing data format
Now, when we know which fields to encrypt, we need to change the data format to binary in the database table. Because the encrypted data is binary data.
1. Connect again to the database using its connection string from your machine:
2. Convert the fields in the database table that you plan to encrypt into binary format:
ALTER TABLE users ALTER COLUMN name TYPE bytea USING name::bytea, ALTER COLUMN password TYPE bytea USING password::bytea, ALTER COLUMN email TYPE bytea USING email::bytea;
Step 6: Configuring client (backend) application and adding some data
So now we have a database to store the encrypted data and AcraServer to encrypt and decrypt the fields. There should be something that stores and reads the data in the database, right?
Your backend application can work with your database transparently through AcraServer. Just as if you were working with it directly.
1. If you already have a backend application, redirect it to the AcraServer address instead of PostgreSQL address.
2. If you don’t have a backend app, let’s use your machine as backend app and add some data from your machine to the database, through AcraServer.
Connect to the AcraServer using PostgreSQL database user and its password, but AcraServer’s hostname:
psql "postgres://doadmin:<do_password>@<acraserver-host-name>:9393/defaultdb?sslmode=require" INSERT INTO users VALUES ('1', 'Jessica', '3272bd9dq0F', 'email@example.com'); INSERT INTO users VALUES ('2', 'Peter', 'hdy3s02ve9^', 'firstname.lastname@example.org'); SELECT * FROM users;
What did you just do? You’ve added some data into the database through AcraServer that encrypted it. The data we just added looks like plaintext if we read it through AcraServer. However, if you read the data from the database directly – you’ll see that it’s encrypted.
3. Read the table content directly from the database. Connect to the database and select users.
psql "postgres://doadmin:<password>@db-postgresql-lon1-94615-do-user-5016390-0.db.ondigitalocean.com:25060/defaultdb?sslmode=require" SELECT id, name FROM users;
You’ll get encrypted data in response. If you look deeper, each user name (email and password) is being encrypted using a unique key into special cryptographic container AcraStruct. Only AcraServer has private keys and can decrypt the data.
Great, you’ve done the basic setup and now AcraServer will encrypt data for you!
Step 7: Changing AcraServer configuration manually
If you change table structure, you’ll need to update AcraServer configuration so it knows what data to encrypt and what data to keep “as is”. Let’s add a new column and mark it as “encrypted”.
This time we won’t use the script, but will change AcraServer’s configuration manually.
1. Add new column “address” to the database (connect to the database):
psql "postgres://doadmin:<password>@db-postgresql-lon1-94615-do-user-5016390-0.db.ondigitalocean.com:25060/defaultdb?sslmode=require" ALTER TABLE users ADD COLUMN address bytea;
2. Change configuration of AcraServer (connect to the AcraServer), adding “address” column to the
ssh root@<AcraServer_droplet_IP> edit /etc/acra/cfg/acra-server-encryptor.yaml cat /etc/acra/cfg/acra-server-encryptor.yaml
schemas: - columns: - id - name - password - email - address // add this encrypted: - column: name - column: password - column: email - column: address // add this table: users
3. Restart AcraServer locating it’s container ID and using Docker restart:
docker ps docker restart
Okay, now AcraServer will encrypt the “address” field, too.
4. Let’s add some data into the table -– a new user with their home address to the database through AcraServer (use your backend application or
psql "postgres://doadmin:<do_password>@<acraserver-host-name>:9393/defaultdb?sslmode=require" INSERT INTO users VALUES ('3', 'Sasha', 'hdyy3he20', 'email@example.com', '21 Great Tower St, London EC3R 5AR, UK'); SELECT * FROM users; id | name | password | email | address ----+---------+-------------+---------------------+---------------------------------------- 1 | Jessica | 3272bd9dq0F | firstname.lastname@example.org | 2 | Peter | hdy3s02ve9^ | email@example.com | 3 | Sasha | hdyy3he20 | firstname.lastname@example.org | 21 Great Tower St, London EC3R 5AR, UK (3 rows)
However, if you read “Sasha” record from the PostgreSQL directly, you’ll see encrypted data:
Congrats! It means that AcraServer uses our new configuration and encrypts the address, too. :)
Step 8: Using AcraServer 1-Click App in production settings
This example illustrates the most basic feature of Acra Community Edition – server-side encryption as transparent proxy for PostgreSQL.
For production use, we suggest that you configure Acra directly from the configuration files and follow the configuration-as-a-code approach (instead of using
server_configure.py script provided above).
You’ll need to generate your own keys, connect to your own database, use strong TLS, configure the SQL firewall, and preferably use client-side encryption. Please refer to Acra's configuration guides and examples and Acra docs.
If you use MySQL, PostgreSQL, or MariaDB – configure AcraServer to connect to your specific database. If you configure server-side encryption, almost no changes in your backend app are required (only change all encrypted fields’ type to binary). AcraServer also has built-in SQL firewall (to protect from SQL injections) and intrusion detection system.
If you prefer client-side encryption, Acra has client-side libraries (AcraWriter) for nearly 11 languages and platforms.
If you use NoSQL database or any other file storage, you can use Acra with client-side encryption and AcraTranslator-side decryption. Download AcraWriter library for your backend app and deploy AcraTranslator into your infrastructure to decrypt the data stored in any storage. Please read the docs.
Acra encryption suite is powerful and is capable of much more than this basic use. Acra is compatible with tools that export logs, metrics and tracing, it can be used as event source for modern SIEMs, and if your infrastructure requires high availability and performance, Acra supports different HA balancers. Purchase of the license for Acra Enterprise provides an access to more security features (i.e. search over secure data, integration with KMS and HSMs) and help from our team of security engineers.
This is it! You’ve reached the end of Acra 1-Click App DigitalOcean tutorial. We hope it has helped you. If you still have any questions about installing Acra, don’t hesitate to write us!