Database security#

Database permissions#

The PostgreSQL login via superuser postgres should only ever be allowed via Unix domain sockets and via localhost. Access with peer authentication in the pg_hba.conf, however, can be granted:

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

The database should be created by the database administrator and then configured in such a way that not everyone (PUBLIC) can connect to it:

CREATE DATABASE myapp;
REVOKE ALL ON myapp FROM PUBLIC;

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

Save passwords#

Passwords should never be in plain text, e.g. also not be saved in an .env file. When saving and transmitting passwords, this should always be salted. For PostgreSQL there is the extension pgcrypto, which can be easily activated with

CREATE EXTENSION pgcrypto;

For this reason, secure passwords should be assigned when they are created, which can then get saved e.g. in Vault or similar:

CREATE ROLE myapp_users;
CREATE ROLE myapp_reader IN ROLE myapp_users LOGIN PASSWORD '…';
CREATE ROLE myapp_writer IN ROLE myapp_users LOGIN PASSWORD '…';
Then users with the role myapp_users first get CONNECT rights and then

myapp_reader read rights and myapp_writer write rights:

GRANT CONNECT ON DATABASE to myapp_users;
GRANT SELECT ON diagnosis_key TO myapp_reader;
GRANT INSERT ON diagnosis_key TO myapp_writer;

The user myapp_reader can, however, read all data at once. This is also a point of attack that is better cut 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;

Then the function myapp_owner is assigned, the authorisations for myapp_reader and myapp_writer are revoked and finally the execution of the function myapp_reader is allowed:

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

This means that myapp_reader can only read a single data record.

id#

rhe id shouldn’t be written as serial, bigserial or similar. Counting numbers could be easily guessed by attackers. Therefore the UUIDv4 data type is much more suitable. In PostgreSQL you can generate UUIDv4 with the uuid-ossp extension or for PostgreSQL≥9.4 also the pgcrypto extension:

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,
  ...
);

Time stamp#

Occasionally, the date and time are stored as bigint, i.e. as a number, even though there is also a TIMESTAMP data type. This would have the advantage that you can easily count on them, 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, e.g. after thirty days with:

DELETE FROM diagnosis_key WHERE age(submission_timestamp) > 30;

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

See also