PostgreSQL/TimescaleDB Backend🔗

The postgres backend (aliases: postgresql, pg) converts Sigma rules into PostgreSQL SQL, with optional TimescaleDB-specific extensions. It is the most fully featured of the conversion backends and the default choice for SOCs running PostgreSQL or TimescaleDB as their log store.

For the narrative version with workflow recipes (PostgreSQL view per rule, Grafana wiring, sliding-window correlations) see Rule Conversion. For the CLI surface see backend convert, backend targets, backend formats.

Backend options🔗

Pass options via -O key=value on the command line. Unknown keys are silently ignored so forward-compatible options can land without breaking existing invocations.

Pipeline set_state with keys table, schema, database overrides the corresponding -O option for the duration of one rule's conversion. Per-rule custom_attributes of postgres.table, postgres.schema, postgres.database override pipeline state. The full precedence chain is documented in Custom Attributes.

Modifier mapping🔗

Every Sigma modifier is translated to a native PostgreSQL construct. The mapping is verified by the postgres backend's golden tests in crates/rsigma-convert/src/backends/postgres/tests.rs.

Keyword matching uses the 'simple' text-search configuration (no language stemming) over ROW(*)::text, so the query matches the token against every column concatenated. This is intentionally broader than per-field FTS: keyword detections in Sigma are unbound, "search this string anywhere in the event".

Field names are always double-quoted ("CommandLine"). String literals are always single-quoted with PostgreSQL-standard escaping ('don''t'). Identifiers passed through -O table=... are validated against ^[A-Za-z_][A-Za-z0-9_$]*$ before insertion; non-matching identifiers fail conversion with InvalidIdentifier. See Security Hardening: SQL injection prevention.

Output formats🔗

Pick the format with -f <format>. List available formats with backend formats postgres.

default🔗

Plain SELECT * FROM <table> WHERE ..., one per rule:

SELECT * FROM security_events WHERE "CommandLine" ILIKE '%whoami%'

Use for ad-hoc execution in psql, embedding into application code, or as the source for materialised views built elsewhere.

view🔗

Wraps each rule's query in CREATE OR REPLACE VIEW sigma_<sanitised-id> AS SELECT ...:

CREATE OR REPLACE VIEW sigma_8b1d8c97_5b3a_4d77_9b48_7c5f7c8b1a2a AS
    SELECT * FROM security_events WHERE "CommandLine" ILIKE '%whoami%'

Useful for dashboards: each rule becomes a named view that Grafana, Superset, or any SQL client can query without parsing rsigma's SQL. Combine with a CI pipeline that runs backend convert -f view ... > views.sql and psql -f views.sql to keep the database in sync with the rule repo.

timescaledb🔗

Adds time_bucket('1 hour', <timestamp_field>) AS bucket to the projection. Use when your events sit on a TimescaleDB hypertable:

SELECT time_bucket('1 hour', time) AS bucket, *
    FROM security_events WHERE "EventID" = 4625

continuous_aggregate🔗

Wraps each detection rule in CREATE MATERIALIZED VIEW ... WITH (timescaledb.continuous) AS ... WITH NO DATA:

CREATE MATERIALIZED VIEW sigma_9d2e7c48_4a3b_4f99_93c9_1c5f7c8b1a2a
    WITH (timescaledb.continuous) AS
    SELECT time_bucket('1 hour', time) AS bucket, *
    FROM security_events WHERE "EventID" = 4625
    WITH NO DATA

TimescaleDB refreshes the aggregate in the background; your dashboards query the materialised result instead of the raw hypertable.

The format applies to base detection rules. Correlation rules are not the right shape for continuous aggregates; convert them separately with default or sliding_window.

sliding_window🔗

Uses SQL window functions for event_count correlations, producing a per-row sliding window that emits every event crossing the threshold:

WITH source AS (
    SELECT * FROM security_events
    WHERE time >= NOW() - INTERVAL '300 seconds'
),
event_counts AS (
    SELECT *, COUNT(*) OVER (
        PARTITION BY "User"
        ORDER BY time
        RANGE BETWEEN INTERVAL '300 seconds' PRECEDING AND CURRENT ROW
    ) AS correlation_event_count
    FROM source
)
SELECT * FROM event_counts WHERE correlation_event_count >= 5

Use when you want per-event explanations of why a brute-force correlation fired (every row above the threshold appears in the output, not just a single aggregate row). Base detection rules return unknown output format: sliding_window and must be skipped with --skip-unsupported.

JSONB mode🔗

Setting -O json_field=data (or pipeline set_state key: json_field) switches the backend from bare column references to JSONB extraction. Useful when events land in a single JSONB column (Okta logs, CloudTrail, OCSF, any "blob" shape).

Top-level fields use ->> (returns text):

-- Sigma field: eventType
data->>'eventType'

Dotted field paths emit chained operators. Every intermediate segment uses -> (returns jsonb); the final segment uses ->> (returns text):

-- Sigma field: securityContext.isProxy
data->'securityContext'->>'isProxy'

-- Sigma field: actor.detail.alternateId
data->'actor'->'detail'->>'alternateId'

Each path segment is validated against the SQL identifier regex (^[A-Za-z_][A-Za-z0-9_$]*$) before insertion; malformed segments fail conversion. Single quotes inside path segments are doubled (don''t). See Security Hardening.

Correlation rules🔗

The backend handles every aggregation type:

Non-temporal correlations that reference detection rules in the same collection auto-wrap the detection logic in WITH combined_events AS (q1 UNION ALL q2 ...). Multi-table temporal correlations (where referenced detection rules target different tables via pipeline routing) generate UNION ALL CTEs with a rule_name discriminator column.

Custom attributes🔗

The backend reads three per-rule attributes from custom_attributes::

See Custom Attributes for the precedence chain.

OCSF pipelines🔗

Two pipelines ship with rsigma-convert for Open Cybersecurity Schema Framework deployments:

Both are starting points; copy and customise for your schema.

Roadmap🔗

Tracked items not yet implemented:

• temporal_ordered correlation via LAG()/LEAD().
• prepared output format that emits PL/pgSQL-friendly placeholders for parameter binding.
• Value modifier transforms (base64, base64offset, wide, utf16le) — currently fail with Unsupported. Most workloads do not need these because they can be preprocessed at ingest by the agent.

These are tracked on the project roadmap and are not blocking any current user.

See also🔗

• Rule Conversion for the workflow walkthrough.
• backend convert for the CLI flag table.
• Custom Attributes for postgres.* per-rule overrides.
• LynxDB backend reference for the alternate target.
• Security Hardening for identifier validation and escape rules.
• crates/rsigma-convert/src/backends/postgres for the implementation.