Database Access

The LEAF platform uses dedicated database users following the principle of least privilege. All sensor data access goes through SECURITY DEFINER functions - no role has direct SELECT on sensor_data except writers.

Default-deny, grant-on-demand. Beyond leaf_portal_user (which genuinely needs broad access to run the application) and leaf_vernemq_user/leaf_nodered_auth_user (narrow, purpose-built roles with exactly one grant each), every other service account starts with zero table/function access and only accumulates specific GRANTs as a real, concrete need shows up. leaf_grafana_user is the clearest example: it is not a readers member - see Grafana Integration for exactly what it can do today and why.

Authentication architecture

The portal is the single source of truth for user identity - every other service either checks logins against it directly, or has its own account mirrored from it. Nobody maintains a separate standalone credential.

Portal: checks the submitted password against user_account.password_hash directly (bcrypt.checkpw).
Grafana: doesn’t check against the database at all - the portal mirrors the plaintext password into Grafana’s own Admin API (Basic auth, not a service-account token - Grafana rejects tokens for this API) whenever it has one in hand, i.e. on login or password reset. This is fire-and-forget so a slow/unreachable Grafana never blocks a portal response.
Node-RED: the opposite pattern - no mirrored account, no separate credential at all. settings.js queries user_account live on every login attempt via leaf_nodered_auth_user, and only succeeds for existing is_superadmin = true accounts. This means the login is always current (no stale mirrored password), but it does mean Node-RED’s editor is unreachable if the database is down.

Database roles and users

Roles

Role	Purpose
`readers`	Broad service-account access: email-based SECURITY DEFINER functions + SELECT on management tables (including `user_account`, so membership is not handed out casually - see below). No direct access to `sensor_data`.
`writers`	Service accounts that write data (Node-RED for `sensor_data`, portal for management tables). Full DML on all tables.
`api_readers`	External callers. Token-based SECURITY DEFINER functions only. No email functions, no direct table access.

readers carries more than dashboards need - it includes SELECT on user_account (email, password hash, TOTP secret) and vmq_auth_acl (MQTT device passwords), because those grants exist for the portal’s own use. Any new service account should almost never join readers wholesale; grant it the specific functions/tables it actually needs instead (see leaf_grafana_user below for the pattern).

Service accounts

User	Access	Used by
`leaf_portal_user`	`readers` + `writers`	Portal application
`leaf_grafana_user`	Nothing by default. Currently granted: `EXECUTE` on `leaf_organisations_for_user`, `leaf_management_for_user`, `leaf_sensor_data_bucketed_by_department`. Not a `readers` member.	Grafana dashboards
`leaf_vernemq_user`	`SELECT` on `vmq_auth_acl` only. Not a `readers`/`writers` member.	VerneMQ’s Postgres-auth plugin (per-department MQTT credentials)
`leaf_nodered_auth_user`	`SELECT (email, password_hash, is_superadmin)` on `user_account` only. Not a `readers`/`writers` member.	Node-RED editor login (checks against existing LEAF superadmin accounts)
`leaf_api_user`	`api_readers` only	External scripts, notebooks
`leaf_backup_user`	`SELECT` on `sensor_data` (raw export) + every operational table (for full `pg_dump` restore, including auth state)	Backup jobs

Node-RED actually connects with two separate roles for two different purposes: one moves sensor data, the other only ever checks a login. Neither is on its intended role in production yet, and for different reasons - the data-processing side (NODERED_DB_USER) is pinned to a maple stand-in because leaf_nodered_user isn’t defined in deploy.sql yet, while the auth-check side (NODERED_AUTH_DB_USER) is pinned to the same maple stand-in because leaf_nodered_auth_user already exists with the grants above but is still missing its pg_hba.conf entry - a DB-admin action, not a deploy.sql change.

Access model

All data access is management-based. A user must have at least one user_management entry whose management scope covers the requested data.

The management record defines:

Which organisation and department the user can access
Optionally, which entity (sensor/device) within that department
Optionally, a time window (time_start / time_end) outside which data is not returned

These bounds are hard limits enforced at the database level inside every SECURITY DEFINER function. A caller cannot request data outside their management grant regardless of what parameters they pass.

SECURITY DEFINER functions

Email-based (for `readers`)

These functions are used by the portal and Grafana. The caller’s email is resolved to management grants at query time.

Function	Returns	Use case
`leaf_organisations_for_user(email)`	Organisations the user has any access to	Portal navigation, Grafana org/dept variables
`leaf_management_for_user(email)`	All management grants for the user	Grafana variable queries, name->UUID resolution
`leaf_sensor_data_for_department_per_uuid(email, dept_uuid, entity, metric, from, to)`	Raw rows (with `tags`), most recent first. `from`/`to` are optional - omit them and it returns everything matching, unbounded.	Portal data explorer export
`leaf_sensor_data_for_department_per_time(email, org, dept, from, to, interval, entities[])`	Time-bucketed aggregates (live `AVG()` over `sensor_data`)	Portal plots
`leaf_sensor_data_bucketed_by_department(email, dept_uuids[], bucket, entities[], metrics[], from, to, limit)`	Downsampled time series read from TimescaleDB continuous aggregates. `bucket` is one of `1min/5min/10min/1hour/1day` - pick it from `$__interval_ms` so it tracks the panel’s zoom level.	Grafana time-series panels (this is what `leaf_grafana_user` is actually granted, unlike `_per_time` above)
`leaf_sensor_data_recent(email, limit)`	Most recent N rows across all accessible data	Dashboard summary

Token-based (for `api_readers` and `readers`)

These functions are used by external scripts and by the portal’s own REST API (/tokens) - the same personal API tokens users generate there for scripts and notebooks are what these functions accept. The API token is resolved to the owning user’s email internally.

Function	Returns	Use case
`leaf_management_for_token(token)`	Management grants for the token’s owner	Variable queries in token-based Grafana
`leaf_sensor_data_timeseries_by_token(token, org, dept, from, to, interval, entities[], metric)`	Time-bucketed aggregates	Grafana panels with API token auth
`leaf_sensor_data_raw_by_token(token, org, dept, from, to, entities[], metric, limit)`	Raw rows	External scripts, notebooks
`leaf_sensor_catalog_for_token(token, org, dept)`	Distinct (entity, metric) pairs	Variable queries, discovery

Connecting from Python

asyncpg (recommended for async applications)

import asyncpg

pool = await asyncpg.create_pool(
    host="localhost",
    port=5432,
    database="leaf",
    user="leaf_api_user",
    password="your-password",
    min_size=2,
    max_size=10,
)

# Query with an API token
rows = await pool.fetch(
    """
    SELECT *
    FROM leaf_sensor_data_timeseries_by_token(
        $1, $2, $3,
        $4::timestamptz, $5::timestamptz,
        '1 hour'::interval,
        NULL,  -- all entities
        NULL   -- all metrics
    )
    ORDER BY time
    """,
    "your-api-token",
    "YourOrganisation",
    "YourDepartment",
    datetime(2025, 1, 1),
    datetime(2025, 2, 1),
)

pandas + SQLAlchemy (for data analysis)

import pandas as pd
from sqlalchemy import create_engine, text

engine = create_engine(
    "postgresql://leaf_api_user:your-password@localhost:5432/leaf"
)

with engine.connect() as conn:
    df = pd.read_sql(
        text("""
            SELECT *
            FROM leaf_sensor_data_timeseries_by_token(
                :token, :org, :dept,
                :from_ts::timestamptz, :to_ts::timestamptz,
                '1 hour'::interval,
                NULL, NULL
            )
            ORDER BY time
        """),
        conn,
        params={
            "token": "your-api-token",
            "org": "YourOrganisation",
            "dept": "YourDepartment",
            "from_ts": "2025-01-01",
            "to_ts": "2025-02-01",
        }
    )

print(df.head())

Discover available entities and metrics

with engine.connect() as conn:
    catalog = pd.read_sql(
        text("""
            SELECT entity, metric
            FROM leaf_sensor_catalog_for_token(:token, :org, :dept)
            ORDER BY entity, metric
        """),
        conn,
        params={"token": "your-api-token", "org": "YourOrg", "dept": "YourDept"},
    )
print(catalog)

Connection string

postgresql://leaf_api_user:YOUR_PASSWORD@localhost:5432/leaf

When connecting from inside the Docker network, use timescaledb as the host instead of localhost.

Best practices

Always provide a time range. Without p_from/p_to, the query scans all TimescaleDB chunks. At large data volumes (100M+ rows) this adds hundreds of milliseconds of planning time alone. Pass a concrete time range to let TimescaleDB prune to only the relevant chunks.

Pass time parameters inside the function. Because the functions use SECURITY DEFINER, PostgreSQL cannot inline them. A WHERE time > ... clause applied outside the function call does not benefit from chunk exclusion. Pass p_from and p_to as function arguments.

Use leaf_sensor_catalog_for_token for entity/metric discovery. The sensor_catalog is a pre-computed materialized view of distinct (department_id, entity, metric) triples, refreshed automatically. Querying it is far faster than running DISTINCT over sensor_data.

Store credentials in environment variables. Never hardcode passwords or API tokens in scripts.

import os
token = os.environ["LEAF_API_TOKEN"]
password = os.environ["LEAF_DB_PASSWORD"]

Permissions summary

Operation	`leaf_api_user`	`leaf_grafana_user`	`leaf_vernemq_user`	`leaf_nodered_auth_user`	`leaf_portal_user`
Direct SELECT on `sensor_data`	-	-	-	-	-
Token-based functions	+	-	-	-	+
Email-based functions	-	3 specific functions only (see above)	-	-	+
SELECT on `vmq_auth_acl`	-	-	+	-	+ (via `writers`)
SELECT on `user_account` (3 columns)	-	-	-	+	+ (full, via `writers`)
INSERT / UPDATE on management tables	-	-	-	-	+
INSERT on `sensor_data`	-	-	-	-	+

leaf_grafana_user’s “email-based functions” access is not blanket readers access - it’s exactly leaf_organisations_for_user, leaf_management_for_user, and leaf_sensor_data_bucketed_by_department, added deliberately one at a time. Don’t assume any other email-based function works for it without checking deploy.sql’s grants first.

Tools and clients

Tool	Use case
asyncpg	Async Python applications
pandas + SQLAlchemy	Data analysis notebooks
psql	Quick interactive queries
pgAdmin / DBeaver	GUI query builder
Grafana	Dashboards - see Grafana Integration