Database setup for the time-tracking app

A working time-tracking backend needs explicit database roles and grants. The Spanish originals use the schema name control_tiempo; we keep it here so SQL snippets stay aligned with earlier posts.

Roles

DBA

CREATE ROLE dba_user WITH LOGIN PASSWORD 'replace-me';
ALTER ROLE dba_user CREATEDB;

Admin (creates business rows)

CREATE ROLE admin_user WITH LOGIN PASSWORD 'replace-me';

Web (API-facing)

CREATE ROLE web_user WITH LOGIN PASSWORD 'replace-me';

Anonymous (login bootstrap)

CREATE ROLE anon_user WITH LOGIN PASSWORD 'replace-me';

Schema and starter table

CREATE SCHEMA control_tiempo;

CREATE TABLE control_tiempo.usuarios (
    id SERIAL PRIMARY KEY,
    nombre VARCHAR(50),
    apellidos VARCHAR(50),
    email VARCHAR(100),
    clave VARCHAR(100),
    fecha_creacion TIMESTAMP,
    activo BOOLEAN
);

Column names stay Spanish to match the rest of the series’ SQL; translate at the ORM layer if you prefer English field names in application code.

Grants

GRANT ALL PRIVILEGES ON SCHEMA control_tiempo TO dba_user;
GRANT USAGE, CREATE ON SCHEMA control_tiempo TO admin_user;
GRANT USAGE, CREATE ON SCHEMA control_tiempo TO web_user;

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE control_tiempo.usuarios TO admin_user;
GRANT SELECT ON TABLE control_tiempo.usuarios TO web_user;

Next

We add the remaining tables for roles, projects, tasks, and hours—see Remaining tables for the time-tracking database.