Grafana Integration

Grafana connects to the LEAF platform database as leaf_grafana_user, a read-only service account. All data access goes through SECURITY DEFINER functions that enforce the same management-based access control as the portal. A Grafana user only sees data their LEAF account has been granted access to.

How access control works

Every query passes the logged-in Grafana user’s email address via Grafana’s built-in ${__user.email} variable. The database function resolves that email to the user’s management grants and returns only the rows within the user’s authorised organisation, department, entity scope, and time window.

Because leaf_grafana_user is readers-only (not writers), it has no direct SELECT on sensor_data. Raw SQL panels cannot bypass the access model — all data access goes through the SECURITY DEFINER functions.

Database connection setup

In Grafana, go to Connections → Add new connection → PostgreSQL.

Fill in the connection details:

Field	Value
Host URL	`timescaledb:5432` (Docker) or your DB host
Database name	`leaf`
Username	`leaf_grafana_user`
Password	The password set in `deploy.sql` (change before production)
TLS/SSL Mode	`disable` (or `require` with certificates)

Click Save & test — you should see “Database Connection OK”.

Grafana variables

Add these as dashboard variables so users can select organisation, department, entity, and metric from drop-downs. All variable queries are scoped to the logged-in user’s access.

Available departments

SELECT department_name
FROM management_for_user('${__user.email}')
WHERE department_name IS NOT NULL
ORDER BY 1

Entities within a department

SELECT entity
FROM sensor_catalog
WHERE department_id = (
    SELECT id FROM department d
    JOIN organisation o ON o.id = d.organisation_id
    WHERE o.name = '${organisation}' AND d.name = '${department}'
)
ORDER BY entity

Available metrics

SELECT metric
FROM sensor_catalog
WHERE department_id = (
    SELECT id FROM department d
    JOIN organisation o ON o.id = d.organisation_id
    WHERE o.name = '${organisation}' AND d.name = '${department}'
)
ORDER BY metric

Time-series panel (aggregated)

Use sensor_data_for_department_per_time for all time-series panels. Pass $__timeFrom() and $__timeTo() as function parameters — not as outer WHERE clauses. This is important: because the function uses SECURITY DEFINER, PostgreSQL cannot inline it, so a time filter applied outside the function call cannot be pushed into the function for chunk exclusion. Passing the times as parameters lets TimescaleDB prune chunks inside the function.

SELECT *
FROM sensor_data_for_department_per_time(
    '${__user.email}',
    '${organisation}',
    '${department}',
    $__timeFrom()::timestamptz,
    $__timeTo()::timestamptz,
    '$__interval'::interval,
    NULLIF(ARRAY['${entity:csv}']::text[], ARRAY[''])
)
ORDER BY time

The function returns (time, entity, metric, value, tags). Map time to the X axis and value to Y. Use entity or metric as the series name.

Aggregation interval: $__interval is Grafana’s auto-calculated interval based on the panel width and time range. You can also hardcode it: '1 hour'::interval.

Full query — what PostgreSQL actually executes (with example values and comments)

Grafana interpolates variables before sending anything to the database. This is the literal SQL PostgreSQL receives and then runs — first the function call as sent by Grafana, then the function body expanded with those values inlined.

-- ── What Grafana sends to PostgreSQL ──────────────────────────────────────
--
-- Variables resolved for this example:
--   ${__user.email}  → 'user@example.com'   (logged-in Grafana account)
--   ${organisation}  → 'WUR'
--   ${department}    → 'Greenhouse A'
--   $__timeFrom()    → '2025-01-15 09:00:00+00'  (left edge of time picker)
--   $__timeTo()      → '2025-01-15 21:00:00+00'  (right edge of time picker)
--   $__interval      → '3 minutes'               (auto-sized to panel width)
--   ${entity:csv}    → 'sensor-1,sensor-2'        (multi-select variable)

SELECT *
FROM sensor_data_for_department_per_time(
    'user@example.com',                      -- ${__user.email}
    'WUR',                                   -- ${organisation}
    'Greenhouse A',                          -- ${department}
    '2025-01-15 09:00:00+00'::timestamptz,   -- $__timeFrom()
    '2025-01-15 21:00:00+00'::timestamptz,   -- $__timeTo()
    '3 minutes'::interval,                   -- $__interval
    ARRAY['sensor-1', 'sensor-2']            -- NULLIF(ARRAY[...], ARRAY['']) with entity:csv
    -- NULL here when "All" is selected → function returns all accessible entities
)
ORDER BY time;


-- ── What PostgreSQL runs (function body with values inlined) ───────────────
--
-- The SECURITY DEFINER function runs as the database owner (postgres), not as
-- leaf_grafana_user. PostgreSQL cannot inline SECURITY DEFINER functions, so
-- the query planner sees the function as a black box. Time bounds must be
-- passed as p_from/p_to parameters so TimescaleDB can prune chunks *inside*
-- the function. A WHERE clause applied outside the function call arrives too
-- late for chunk exclusion.

WITH

-- Step 1: resolve org/dept names → UUIDs
-- This is a tiny indexed lookup; result is 1 row.
dept AS MATERIALIZED (
    SELECT d.id AS dept_id, o.id AS org_id
    FROM   department   d
    JOIN   organisation o ON o.id = d.organisation_id
    WHERE  o.name = 'WUR'            -- p_organisation
      AND  d.name = 'Greenhouse A'   -- p_department
    LIMIT  1
),

-- Step 2: resolve user email → management grants
-- Each row represents one data slice the user is allowed to see:
-- (organisation_id, optional entity scope, optional time window).
-- Result is typically a handful of rows — this CTE is materialised once
-- and reused for every sensor_data row in step 3.
user_access AS MATERIALIZED (
    SELECT m.organisation_id, m.entity, m.time_start, m.time_end
    FROM   user_management um
    JOIN   management      m  ON m.id  = um.management_id
    JOIN   user_account    ua ON ua.id = um.user_id
    JOIN   dept                ON TRUE           -- binds to the dept CTE
    WHERE  ua.email = 'user@example.com'         -- p_user_email
      AND  (m.department_id IS NULL              -- NULL = dept-wide grant
            OR m.department_id = (SELECT dept_id FROM dept))
)

-- Step 3: scan sensor_data and aggregate into time buckets
-- TimescaleDB evaluates the WHERE sd.time >= / < conditions during
-- planning and excludes chunks outside the range before the scan starts.
-- Chunk exclusion is the primary performance lever for large datasets.
SELECT
    time_bucket('3 minutes', sd.time) AS time,  -- bucket aligned to interval start
    sd.entity,
    sd.metric,
    AVG(sd.value)::double precision    AS value, -- average of all readings in the bucket
    sd.tags
FROM   sensor_data sd
JOIN   dept        ON sd.department_id = dept.dept_id
JOIN   user_access ua ON (
    ua.organisation_id = sd.organisation_id
    -- Entity scope: NULL in management = access to all entities in the dept
    AND (ua.entity     IS NULL OR ua.entity     = sd.entity)
    -- Management time bounds: hard limits set by the administrator.
    -- The UI time range (p_from/p_to) can only narrow this window, never expand it.
    AND (ua.time_start IS NULL OR sd.time       >= ua.time_start)
    AND (ua.time_end   IS NULL OR sd.time       <  ua.time_end)
)
WHERE  sd.time >= '2025-01-15 09:00:00+00'       -- p_from  ← chunk exclusion happens here
  AND  sd.time <  '2025-01-15 21:00:00+00'        -- p_to
  AND  sd.entity = ANY(ARRAY['sensor-1', 'sensor-2'])  -- p_entities filter
GROUP BY 1, sd.entity, sd.metric, sd.tags
ORDER BY 1;

Raw data table panel

For short time windows or debugging use sensor_data_for_department_per_time with a short interval, or filter to a single entity. For very short windows (< 1 hour) you can set the interval to '1 second'::interval to get near-raw data.

Entity filter — passing multiple entities

When ${entity} is a multi-value variable, Grafana renders it as a comma-separated string with the :csv format modifier. The query above wraps it in ARRAY[...]::text[] and uses NULLIF(..., ARRAY['']) to convert an empty selection to NULL (meaning “all entities”).

If the entity variable is single-value only, pass it directly:

SELECT *
FROM sensor_data_for_department_per_time(
    '${__user.email}',
    '${organisation}',
    '${department}',
    $__timeFrom()::timestamptz,
    $__timeTo()::timestamptz,
    '$__interval'::interval,
    CASE WHEN '${entity}' = '' THEN NULL ELSE ARRAY['${entity}']::text[] END
)
ORDER BY time

Access model summary

What the user sees	Why
Only their departments	`management_for_user` filters by `ua.email`
Only their entities	Management entity scope is a JOIN condition, not a WHERE filter
Only their time window	`management.time_start` / `time_end` are hard bounds enforced inside the function
UI time range can only narrow	`p_from` / `p_to` are intersected with the management time bounds — cannot expand beyond the grant

Network configuration

Port: 3000 (Grafana web interface)
Internal DB host: timescaledb:5432 (Docker backend network)
External DB host: expose via SSH tunnel or VPN — do not expose port 5432 publicly

Building a dashboard — step by step

This walkthrough creates a complete sensor dashboard from scratch. All queries are copy-paste ready — replace YourOrganisation and YourDepartment only if you hardcode them instead of using variables.

Step 1 — Add dashboard variables

Go to Dashboard settings → Variables and add these four variables in order. Each depends on the previous one.

Variable: organisation

Setting	Value
Type	Query
Data source	Your LEAF datasource
Refresh	On dashboard load

Query:

SELECT DISTINCT organisation_name
FROM management_for_user('${__user.email}')
ORDER BY 1

Variable: department

Setting	Value
Type	Query
Data source	Your LEAF datasource
Refresh	On time range change

Query:

SELECT DISTINCT department_name
FROM management_for_user('${__user.email}')
WHERE organisation_name = '${organisation}'
ORDER BY 1

Variable: entity

Setting	Value
Type	Query
Multi-value	On
Include All	On
Data source	Your LEAF datasource
Refresh	On time range change

Query:

SELECT DISTINCT entity
FROM sensor_catalog sc
JOIN department d ON d.id = sc.department_id
JOIN organisation o ON o.id = sc.organisation_id
WHERE o.name = '${organisation}'
  AND d.name = '${department}'
ORDER BY 1

Variable: metric

Setting	Value
Type	Query
Multi-value	On
Include All	On
Data source	Your LEAF datasource
Refresh	On time range change

Query:

SELECT DISTINCT metric
FROM sensor_catalog sc
JOIN department d ON d.id = sc.department_id
JOIN organisation o ON o.id = sc.organisation_id
WHERE o.name = '${organisation}'
  AND d.name = '${department}'
ORDER BY 1

Step 2 — Time-series panel

Panel type: Time series

Shows one line per entity-metric combination for the selected time range.

SELECT time, entity || ' — ' || metric AS metric, value
FROM sensor_data_for_department_per_time(
    '${__user.email}',
    '${organisation}',
    '${department}',
    $__timeFrom()::timestamptz,
    $__timeTo()::timestamptz,
    '$__interval'::interval,
    CASE
        WHEN '${entity}' = 'All' THEN NULL
        ELSE string_to_array('${entity:csv}', ',')
    END
)
ORDER BY time

In the Field tab set String as the Series name override to ${__field.labels.metric} if you want entity and metric as separate labels.

Step 3 — Current value (Stat panel)

Panel type: Stat
Calculation: Last (not null)

Shows the most recent reading for a single metric across all selected entities.

SELECT time, entity AS metric, value
FROM sensor_data_for_department_per_time(
    '${__user.email}',
    '${organisation}',
    '${department}',
    now() - INTERVAL '2 hours',
    now(),
    '1 minute'::interval,
    CASE
        WHEN '${entity}' = 'All' THEN NULL
        ELSE string_to_array('${entity:csv}', ',')
    END
)
WHERE metric = '${metric}'
ORDER BY time

Set Value options → Calculation to Last * in the panel options.

Step 4 — Min / Max / Avg for the selected time range (Stat panel)

Panel type: Stat
Calculation: set individually per stat

One panel per stat — duplicate and change the SELECT expression:

-- For average:
SELECT
    AVG(value)   AS "Average",
    MIN(value)   AS "Minimum",
    MAX(value)   AS "Maximum"
FROM sensor_data_for_department_per_time(
    '${__user.email}',
    '${organisation}',
    '${department}',
    $__timeFrom()::timestamptz,
    $__timeTo()::timestamptz,
    ($__timeTo() - $__timeFrom())::interval,  -- single bucket = full range
    CASE
        WHEN '${entity}' = 'All' THEN NULL
        ELSE string_to_array('${entity:csv}', ',')
    END
)
WHERE metric = '${metric}'

The trick here is passing ($__timeTo() - $__timeFrom())::interval as the bucket interval so the whole time range collapses into a single row.

Step 5 — Recent readings table

Panel type: Table

Shows the last N raw readings for quick inspection.

SELECT
    time,
    entity,
    metric,
    ROUND(value::numeric, 4) AS value,
    tags
FROM sensor_data_for_department_per_time(
    '${__user.email}',
    '${organisation}',
    '${department}',
    $__timeFrom()::timestamptz,
    $__timeTo()::timestamptz,
    '1 second'::interval,
    CASE
        WHEN '${entity}' = 'All' THEN NULL
        ELSE string_to_array('${entity:csv}', ',')
    END
)
WHERE metric = '${metric}'
ORDER BY time DESC
LIMIT 500

'1 second'::interval gives one-second buckets, effectively raw data for most sensor frequencies.

Step 6 — Multi-entity bar gauge

Panel type: Bar gauge

Compares the most recent value across all entities in the department. Useful for seeing which sensors are highest/lowest right now.

SELECT entity, AVG(value) AS value
FROM sensor_data_for_department_per_time(
    '${__user.email}',
    '${organisation}',
    '${department}',
    now() - INTERVAL '1 hour',
    now(),
    '1 hour'::interval,
    NULL  -- all entities
)
WHERE metric = '${metric}'
GROUP BY entity
ORDER BY value DESC

Complete variable reference

Variable	Format modifier	Example usage
`${__user.email}`	—	Always pass as-is to functions
`${organisation}`	—	Single-value text
`${department}`	—	Single-value text
`${entity}`	`:csv`	`string_to_array('${entity:csv}', ',')`
`${metric}`	—	Single-value or `= '${metric}'` filter
`$__timeFrom()`	`::timestamptz`	Pass as `p_from`
`$__timeTo()`	`::timestamptz`	Pass as `p_to`
`$__interval`	`::interval`	Pass as `p_interval`

Troubleshooting

No data in panel : Check that the logged-in Grafana user’s email matches a user_account entry in LEAF. The user must also have at least one user_management grant for the selected department.

function does not exist error : Confirm leaf_grafana_user has the readers role: SELECT rolname FROM pg_roles WHERE rolname = 'leaf_grafana_user'; and \du leaf_grafana_user in psql.

All time ranges return the same data : You have a WHERE time BETWEEN ... clause outside the function call. Move the time bounds inside as p_from / p_to parameters.

Slow queries on large datasets : Always provide a time range. Without p_from/p_to, the function scans all chunks. With a time range, TimescaleDB prunes to only the relevant chunks — typically 1–3 for recent data.