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.

Database roles and users

Roles

Role	Purpose
readers	Service accounts that query data (Grafana, portal). Access via email-based SECURITY DEFINER functions + SELECT on management tables. 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.

Service accounts

User	Roles	Used by
leaf_portal_user	readers + writers	Portal application
leaf_grafana_user	readers only	Grafana dashboards
leaf_api_user	api_readers only	External scripts, notebooks

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.

user_account ──► user_management ──► management
                                         │
                              ┌──────────┴──────────┐
                          department_id          organisation_id
                          entity (optional)      time_start / time_end

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
organisations_for_user(email)	Organisations the user has any access to	Portal navigation
management_for_user(email)	All management grants for the user	Grafana variable queries
sensor_data_for_department_per_uuid(email, dept_uuid, entity, metric)	Raw rows, most recent first	Portal data explorer export
sensor_data_for_department_per_time(email, org, dept, from, to, interval, entities[])	Time-bucketed aggregates	Portal plots, Grafana time-series panels
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 the portal API endpoint. The API token is resolved to the owning user’s email internally.

Function	Returns	Use case
management_for_token(token)	Management grants for the token’s owner	Variable queries in token-based Grafana
sensor_data_timeseries_by_token(token, org, dept, from, to, interval, entities[], metric)	Time-bucketed aggregates	Grafana panels with API token auth
sensor_data_raw_by_token(token, org, dept, from, to, entities[], metric, limit)	Raw rows	External scripts, notebooks
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 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 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 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 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_portal_user
Direct SELECT on sensor_data	—	—	—
Token-based functions	✓	✓	✓
Email-based functions	—	✓	✓
INSERT / UPDATE on management tables	—	—	✓
INSERT on sensor_data	—	—	✓

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