Corona Warn App and Database Security¶

Veit Schiele

6 June 2020

5–7 minutes

Alvar Freude took a closer look at the database code of the Corona Warning App. This is a prime example of what can go wrong in many database projects. It also shows how we can avoid such mistakes ourselves.

Alvar Freude is a consultant for technical data protection and freedom of information at the LfDI Baden-Württemberg. He is also the author of PostgreSQL Secure Monitoring (Posemo) and TLS-Check, among other things. He analysed the database code of the Corona Warn App server and published his findings on Twitter.

His tweets are summarised and supplemented below:

Database permissions¶

One of the key findings is that database permissions are far too extensive, allowing a successful attacker to access and delete all data, and so on. He refutes the objection that an attacker would never get that far by pointing to the fact that, unfortunately, this happens time and time again.

Therefore, PostgreSQL login via superuser postgres should only ever be permitted via Unix domain sockets and via localhost. Access with peer authentication in the pg_hba.conf file is OK, however:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
host    all             all             10.23.42.1/24           scram-sha-256

Since the application receives a database superuser but no database is configured, it will probably be created automatically. However, this would mean that the process would have to be executed as a superuser – a very bad idea. The database should be created by the DBA with superuser rights and then configured so that not everyone (PUBLIC) can connect to it:

CREATE DATABASE cwa;
REVOKE ALL ON cwa FROM PUBLIC;

This means that only the superuser can connect to the cwa database.

However, if a table, such as V1__createTables.sql, was initially created normally, meaning that PUBLIC has all rights, these should be revoked with:

REVOKE ALL ON diagnosis_key FROM PUBLIC;
REVOKE ALL ON diagnosis_key FROM current_user;

You can then create a role cwa_users with the two users cwa_reader and cwa_inserter to distinguish between read and write access:

CREATE ROLE cwa_users;
CREATE USER cwa_reader IN ROLE cwa_users PASSWORD '…';
CREATE USER cwa_inserter IN ROLE cwa_users  PASSWORD '…';

Now, the role cwa_users first receives CONNECT rights and then cwa_reader read rights and cwa_inserter add rights:

GRANT CONNECT ON DATABASE to cwa_users;
GRANT SELECT ON diagnosis_key TO cwa_reader;
GRANT INSERT ON diagnosis_key TO cwa_inserter;

However, the cwa_reader user can initially read all data at once. Since this is not necessary, this attack vector can be mitigated by a function:

CREATE OR REPLACE FUNCTION get_key_data(in_id UUID)
    RETURNS JSONB
    AS 'SELECT key_data FROM diagnosis_key WHERE id = in_id;'
    LANGUAGE sql SECURITY DEFINER SET search_path = :schema, pg_temp;

Subsequently, the function cwa_owner is assigned, cwa_reader and cwa_inserter are revoked, and finally the execution of the function cwa_reader is permitted:

ALTER FUNCTION get_key_data(UUID) OWNER TO cwa_owner;
REVOKE ALL ON FUNCTION get_key_dataUUID) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION get_key_data(UUID) TO cwa_reader;

This means that cwa_reader can now only read a single data record.

Storing passwords¶

The default passwords specified in .env also have a good chance of remaining in production, according to Alvar Freude.

Therefore, secure passwords should be assigned when creating users, which should then be stored in Vault or similar.

`id`¶

Another interesting feature of the table definition is that the id is implemented as bigserial. However, an incrementing number could be guessed by attackers. Therefore, the UUIDv4 data type is likely to be more suitable. For UUIDv4 generation, either the uuid-ossp extension or, for PostgreSQL≥9.4, the pgcrypto extension can be used, either:

CREATE EXTENSION "uuid-ossp";
CREATE TABLE diagnosis_key (
  id uuid primary key default uuid_generate_v4() NOT NULL,
  …
);

or:

CREATE EXTENSION "pgcrypto";
CREATE TABLE diagnosis_key (
  id uuid primary key default gen_random_uuid() NOT NULL,
  …
);

Timestamp¶

In the table definition of V1__createTables.sql, the date and time are stored in submission_timestamp as bigint, thus as a number, even though there is also a TIMESTAMP data type. This would have the advantage that they can also be used for calculations, for example:

SELECT age(submission_timestamp);
SELECT submission_timestamp - '1 day'::interval;

In addition, the data could be deleted after a certain period of time, for example after thirty days, using:

DELETE FROM diagnosis_key WHERE age(submission_timestamp) > 30;

Deletion can be accelerated even further if a separate partition is created for each day using the PostgreSQL extension pg_partman.

Corona Warn App and Database Security¶

Database permissions¶

Storing passwords¶

id¶

Timestamp¶

`id`¶