Rolling back your database

Encrypting your sensitive data might feel like a solely one-way process — but what do you do if you change your mind, need to change the key layout (i.e. go from a zoneless mode to using Zones or vice-versa), or re-generate all the keys?

The answer is to perform a rollback. You could do the rollback yourself, manually — by crafting special requests within your app, accumulating the results, and then converting them into database inserts again. However, we've created a convenient utility to help you generate a clean SQL dump from an existing protected one.

WARNING: For security reasons, we advise you to perform the following procedures on AcraServer exclusively!

Building AcraRollback


go get

Note: Starting with Acra v.0.77.0, acra-rollback requires Go version >= 1.8.

Running (see full query example below):

$GOPATH/bin/acra-rollback --postgresql_enable
# or for MySQL database:
$GOPATH/bin/acra-rollback --mysql_enable

Note: For acra-rollback to work, the key folder has to have the proper permissions (as originally set by acra_genkeys):

  • folder 700,
  • private keys 600.

How AcraRollback works

To decrypt the protected data, the rollback the utility will make a request to your database using query from parameter --select, which must return:

  • a single column that stores the encrypted data in simple mode (a mode without Zones).
  • two columns with --zonemode_enable parameters if Zones are used. The first column should be zone_id, the second — encrypted data payload.

After decrypting the data, the utility can:

  • Generate an SQL dump with insert queries for you to evaluate and execute later, at your own risk;
  • Execute the queries from insert CLI parameter with replacing placeholder $1 on the decrypted data.

By default, the data will be put into the hex format (i.e. this can be E'\\xDECRYPTED DATA'). You can change the format to escape with --escape flag.

Note: AcraRollback currently only supports WholeCell mode. InjectedCell support will be added at some point. You can read more about storage models in Acra here.

Here's acra-rollback CLI syntax:

    Client id should be name of file with private key
    path to config
    Connection string for db
    dump config
    Escape bytea format
    Execute inserts
    Query for insert decrypted data with placeholders (pg: $n, mysql: ?)
    Folder from which the keys will be loaded (default ".acrakeys")
    Handle MySQL connections
    File for store inserts queries (default "decrypted.sql")
    Handle Postgresql connections
    Query to fetch data for decryption
    Turn on zone mode
  • --client_id — Id of the client, which was used for encryption. Example: --client_id=someid.
  • --config-file — path to configuration file (use config file instead of CLI arguments, see an example in configs/acra-rollback.yaml).
  • --connection_string — connection string to PostgreSQL in format dbname=<DBNAME> host=<HOST> port=<PORT> user=<USER> password=<PASSWORD>.
  • --escape — change decrypted data output format from hex to escape.
  • --execute — with this flag, all the insert queries will be executed.
  • --insert — insert SQL query for executing or writing to the file, replacing placeholder ($1) on the decrypted data. Example: insert into decrypted_data_table (data) values ($1);.
  • --keys_dir — path to the folder where the utility will search for private keys for decryption (using <client_id> or <zone_id>).
  • --mysql_enable — use this flag to work with MySQL database.
  • --output_file — filename or path to the file which will be used for dump insert queries.
  • --postgresql_enable — use this flag to work with PostgreSQL database.
  • --select — query that will be used for fetching data for decryption. The query should return two columns (Zone Id, encrypted data) with --zonemode_enable flag and one column with encrypted data without this parameter (default).
  • --zonemode_enable tells the utility to fetch two columns and to use the first for decrypting with a Zone.

Quote escaping for command line would turn a typical request into either:

  • Single-quote syntax with $ escaping:
$GOPATH/bin/acra-rollback --client_id=client --postgresql_enable --connection_string="dbname=acra user=postgres password=postgres host= port=5432" --output_file=out.txt --select="select data from test_example_without_zone;" --insert="insert into test_example_without_zone values(\$1);"
  • Double-quote syntax:
$GOPATH/bin/acra-rollback --client_id=client --postgresql_enable --connection_string="dbname=acra user=postgres password=postgres host= port=5432" --output_file=out.txt --select="select data from test_example_without_zone;" --insert='insert into test_example_without_zone values($1);'

Sample queries

  • Example without --zonemode_enable:
    select encrypted_data from some_table;

  • Example with --zonemode_enable:
    select zone_id, encrypted_data from some_table;.

Decrypting AcraStructs without Zones

You need to pass the following as arguments:

  • --postgresql_enable or --mysql_enable to select the database type;
  • select SQL query for fetching the data from the database --select "select data from data_table;";
  • insert SQL query with a placeholder $1 instead of which the data will be placed or bound for insertion directly into database (if you use --execute param) --insert 'insert into test_insert(data) values($1);;
  • client id for finding key --client_id=onekey;
  • connection string that will be used to connect to db --connection_string="dbname=some_database user=postgres password=postgres host= port=5432".

The script will search for the key in .acrakeys folder with the name <client_id>, then generate SQL insert queries to file decrypted.sql. If you use --execute argument, the script will insert the data into the database, too. If you don't want to create an output file while executing insert, pass an empty filename, i.e.: --output_file="".

$GOPATH/bin/acra-rollback --postgresql_enable --select "select data from data_table;" --insert 'insert into test_insert(data) values($1);' --connection_string="dbname=some_database user=postgres password=postgres host= port=5432" --client_id=onekey

Decrypting AcraStructs with Zones

Same as above, but:

  • use --zonemode_enable parameter;
  • you don't need to pass the client id;
  • the provided SELECT SQL query must fetch Zone and data from the database and Zone should be first, i.e.: --select "select zone, data from data_table;";
  • all zone private keys should be placed into the keys' dir (.acrakeys by default).
$GOPATH/bin/acra-rollback --postgresql_enable --select "select zone, data from data_table;" --insert 'insert into test_insert(data) values($1);' --connection_string="dbname=some_database user=postgres password=postgres host= port=5432" --zonemode_enable

Saving decrypted data to file

Instead of inserting data back into the database, you can print it to the output file, to handle it later. To do it, change the insert query to a simple '$1;', like this:

$GOPATH/bin/acra-rollback --client_id=client --postgresql_enable --connection_string="dbname=acra user=postgres password=postgres host= port=5432" --output_file=data.txt --select="select data from test_example_without_zone;" --insert='$1;'

If the decryption is successful, data.txt will contain the encrypted data separated by \n.

Poison records

AcraRollback (acra_rollback) currently ignores poison records. Security-wise, the consideration is that if you can run CLI commands on the server that holds all the private keys, you can compromise the system anyway. You can read more about poison records on the corresponding documentation page.