A managed database

eodia analytics works with a managed PostgreSQL database: Amazon RDS, Google Cloud SQL, Azure Database for PostgreSQL, Scaleway, OVHcloud… PostgreSQL 17, the version in the Compose files, is recommended. Simply point DATABASE_URL to it and remove the db service from the deployment.

DATABASE_URL=postgres://eodia:<password>@pg.example.com:5432/analytics?sslmode=require

What the application user must be able to do

The DATABASE_URL user is the owner of the three schemas (public, collect, analytics): it creates the tables, the monthly partitions, the views and their comments. Give it a database of its own, which it owns.

The views read with their owner’s privileges: this is what lets the read role see only analytics, without any privilege on the raw data.

Creating the read role by hand

The migrations create the eodia_analytics role if the application user is allowed to (CREATEROLE). On a managed database, this privilege is often missing: the migration reports it in the API log, and Administration › Settings › Read role shows that the role does not exist. Then create it with an administrator account of the database:

CREATE ROLE eodia_analytics NOLOGIN;
GRANT USAGE ON SCHEMA analytics TO eodia_analytics;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO eodia_analytics;

ALL TABLES also covers views. Then restart the API: if READER_PASSWORD is set, it gives the role its password (LOGIN PASSWORD), if it is allowed to; otherwise, do it yourself:

ALTER ROLE eodia_analytics LOGIN PASSWORD '<password>';

Views created later

Generated views (evt_*, cls_*) appear each time an event or a classification is declared. For the role to read them without intervention, the application user, as owner of the views, must also set the default privileges:

ALTER DEFAULT PRIVILEGES FOR ROLE eodia IN SCHEMA analytics
  GRANT SELECT ON TABLES TO eodia_analytics;

(eodia being the DATABASE_URL user.)

The network

eodia insights connects to the same database with this role. Restrict access to the insights address (the provider’s network rules), and require TLS (sslmode=require on the insights side).

What the database never sees

No IP address reaches the database: it is forgotten in the request’s memory. The raw data carries only pseudonymous visitor identifiers, and the salt of the daily hash is destroyed the next day.