Webhook icon
Schedule icon
If icon
Fail icon
Get icon
Query icon
SlackIncomingWebhook icon
Set icon
Exit icon

Catch PostgreSQL Row-Level Security Decision Regressions

Detect PostgreSQL RLS leaks and lockouts by probing real tenant roles and rows, with change-only Slack alerts and a failing gate.

Categories
DataInfrastructure

A table can have RLS enabled while a permissive policy leaks another tenant's rows, or a restrictive change locks out its own tenant. This flow tests the decision each application login actually receives. It reads two permanent canary rows through two non-owner PostgreSQL credentials and fails the execution if any allow or deny decision changes. It never writes application data or changes a policy. This canary pair checks the selected policy path; add further pairs for other roles or conditions.

How it works

  1. validate_canaries (If) rejects duplicate IDs or roles before making a database call.
  2. probe_alpha and probe_beta (PostgreSQL Query) connect independently as the two application roles. Each checks current_user, row_security_active, one visible own row, and zero visible peer rows.
  3. evaluate_decisions (If) sends one actionable Slack alert for a new drift, remembers the verdict in Kestra KV, and ends in FAILED. On recovery from drift or a probe error it sends one recovery alert. Repeated clean, drift or error runs stay quiet.

Error handling

The canary guard fails before the database calls. Each JDBC probe retries transient connection errors with exponential backoff. Missing table permissions, bad credentials and outages fail closed; the errors handler names the execution and directs the operator to the logs. It remembers the ERROR state and alerts only on the first failing run until the next clean or drift verdict. The verdict is stored only after Slack succeeds, so a notification failure is retried on the next run. The single-run concurrency limit serializes KV updates.

What this blueprint teaches about Kestra

Two JDBC tasks with different secrets can test the authorization boundary the application actually uses. A flowable If turns those database decisions into a release gate, while Kestra KV suppresses duplicate notifications across scheduled and webhook executions.

Prerequisites

PostgreSQL 13+ with a protected public.tenant_documents(id bigint, tenant text, body text) relation. Put a stable alpha canary at ID 101 and beta canary at ID 202, changing the input IDs if needed. Grant both non-owner login roles USAGE on public and SELECT on the relation. Neither role may own the table, have BYPASSRLS, or be superuser. Adapt the relation name and row predicate in both probe SQL statements for your protected table. A Slack incoming webhook receives alerts.

Secrets

  • POSTGRES_URL: JDBC URL of the database that holds both canaries.
  • POSTGRES_ALPHA_USER, POSTGRES_ALPHA_PASSWORD: alpha application's read-only login.
  • POSTGRES_BETA_USER, POSTGRES_BETA_PASSWORD: beta application's read-only login.
  • SLACK_WEBHOOK_URL: incoming webhook for drift and error alerts.

Inputs

  • alpha_id (INT, default 101): canary visible only to alpha.
  • beta_id (INT, default 202): canary visible only to beta.
  • alpha_role (STRING, default rls_alpha): expected current_user on alpha connection.
  • beta_role (STRING, default rls_beta): expected current_user on beta connection.
  • kestra_url (STRING, default http://localhost:8080): link base in failure alerts.

Outputs

  • alpha_decision, beta_decision (JSON): role identity, active-RLS flag, own and peer row counts, and verdict. Empty on a failed execution.
  • status (STRING): both probe verdicts on a successful clean run; failed runs have no flow outputs, so use the Slack alert or task outputs.

Quick start

  1. In an empty test database, run this fixture as the table owner (set login passwords through your usual secret manager):

    CREATE ROLE rls_alpha LOGIN;
    CREATE ROLE rls_beta LOGIN;
    CREATE TABLE public.tenant_documents(id bigint PRIMARY KEY, tenant text NOT NULL, body text NOT NULL);
    INSERT INTO public.tenant_documents VALUES (101, 'rls_alpha', 'alpha canary'), (202, 'rls_beta', 'beta canary');
    ALTER TABLE public.tenant_documents ENABLE ROW LEVEL SECURITY;
    CREATE POLICY tenant_read ON public.tenant_documents FOR SELECT USING (tenant = current_user);
    GRANT USAGE ON SCHEMA public TO rls_alpha, rls_beta;
    GRANT SELECT ON public.tenant_documents TO rls_alpha, rls_beta;
    
  2. Set the six secrets, run manually, and confirm both own_rows=1, both peer_rows=0, and both rls_active=true. Do not connect as the table owner or a role with BYPASSRLS.

  3. Connect a migration job to the webhook trigger. Enable the hourly schedule only after the first clean run. A permissive policy should fail the gate and notify once; restoring the policy should notify once that decisions recovered.

Plugins used

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.