id: postgres-rls-decision-regression-gate
namespace: company.team
description: |
Test PostgreSQL row-level security as the application actually sees it.
Two non-owner login roles each read a canary in the protected table. The
flow blocks when either role loses its own row, sees the other's row,
bypasses RLS, or connects as an unexpected database user.
concurrency:
limit: 1
behavior: QUEUE
triggers:
- id: policy_change
type: io.kestra.plugin.core.trigger.Webhook
key: postgres-rls-change-me
description: After a policy migration, POST to
/api/v1/main/executions/webhook/company.team/postgres-rls-decision-regression-gate/postgres-rls-change-me
to run the decision gate.
- id: hourly_backstop
type: io.kestra.plugin.core.trigger.Schedule
cron: "23 * * * *"
timezone: UTC
disabled: true
description: Enable after validating the fixture and credentials; it catches
policy changes made outside the migration pipeline.
inputs:
- id: alpha_id
type: INT
defaults: 101
description: ID of a permanent canary row that the alpha login must see and the
beta login must not see.
- id: beta_id
type: INT
defaults: 202
description: ID of a permanent canary row that the beta login must see and the
alpha login must not see.
- id: alpha_role
type: STRING
defaults: rls_alpha
description: Exact PostgreSQL current_user expected from the alpha connection;
catches a service or owner credential used by mistake.
- id: beta_role
type: STRING
defaults: rls_beta
description: Exact PostgreSQL current_user expected from the beta connection;
catches a service or owner credential used by mistake.
- id: kestra_url
type: STRING
defaults: http://localhost:8080
description: Base URL of your Kestra UI, used only in an actionable failure alert.
tasks:
- id: validate_canaries
type: io.kestra.plugin.core.flow.If
description: A shared or non-positive canary ID would make an allow/deny result
meaningless.
condition: "{{ inputs.alpha_id <= 0 or inputs.beta_id <= 0 or inputs.alpha_id ==
inputs.beta_id or inputs.alpha_role == inputs.beta_role }}"
then:
- id: reject_invalid_canaries
type: io.kestra.plugin.core.execution.Fail
errorMessage: Alpha and beta must have distinct positive IDs and distinct
expected login roles. No database probe was run.
- id: read_previous_verdict
type: io.kestra.plugin.core.kv.Get
key: postgres_rls_decision_last_verdict
errorOnMissing: false
- id: probe_alpha
type: io.kestra.plugin.jdbc.postgresql.Query
description: Query the protected relation using the real alpha application
login, not the owner or a service role.
url: "{{ secret('POSTGRES_URL') }}"
username: "{{ secret('POSTGRES_ALPHA_USER') }}"
password: "{{ secret('POSTGRES_ALPHA_PASSWORD') }}"
fetchType: FETCH_ONE
parameters:
own_id: "{{ inputs.alpha_id }}"
peer_id: "{{ inputs.beta_id }}"
expected_role: "{{ inputs.alpha_role }}"
sql: |
SELECT current_user AS actual_role,
row_security_active(CAST('public.tenant_documents' AS regclass)) AS rls_active,
count(*) FILTER (WHERE id = CAST(:own_id AS bigint)) AS own_rows,
count(*) FILTER (WHERE id = CAST(:peer_id AS bigint)) AS peer_rows,
CASE WHEN current_user = :expected_role
AND row_security_active(CAST('public.tenant_documents' AS regclass))
AND count(*) FILTER (WHERE id = CAST(:own_id AS bigint)) = 1
AND count(*) FILTER (WHERE id = CAST(:peer_id AS bigint)) = 0
THEN 'PASS' ELSE 'DRIFT' END AS verdict
FROM public.tenant_documents
WHERE id IN (CAST(:own_id AS bigint), CAST(:peer_id AS bigint))
retry: { type: exponential, interval: PT2S, maxInterval: PT10S, maxAttempts: 3 }
- id: probe_beta
type: io.kestra.plugin.jdbc.postgresql.Query
description: The inverse read catches a policy that grants only one tenant too
much or denies only one tenant too much.
url: "{{ secret('POSTGRES_URL') }}"
username: "{{ secret('POSTGRES_BETA_USER') }}"
password: "{{ secret('POSTGRES_BETA_PASSWORD') }}"
fetchType: FETCH_ONE
parameters:
own_id: "{{ inputs.beta_id }}"
peer_id: "{{ inputs.alpha_id }}"
expected_role: "{{ inputs.beta_role }}"
sql: |
SELECT current_user AS actual_role,
row_security_active(CAST('public.tenant_documents' AS regclass)) AS rls_active,
count(*) FILTER (WHERE id = CAST(:own_id AS bigint)) AS own_rows,
count(*) FILTER (WHERE id = CAST(:peer_id AS bigint)) AS peer_rows,
CASE WHEN current_user = :expected_role
AND row_security_active(CAST('public.tenant_documents' AS regclass))
AND count(*) FILTER (WHERE id = CAST(:own_id AS bigint)) = 1
AND count(*) FILTER (WHERE id = CAST(:peer_id AS bigint)) = 0
THEN 'PASS' ELSE 'DRIFT' END AS verdict
FROM public.tenant_documents
WHERE id IN (CAST(:own_id AS bigint), CAST(:peer_id AS bigint))
retry: { type: exponential, interval: PT2S, maxInterval: PT10S, maxAttempts: 3 }
- id: evaluate_decisions
type: io.kestra.plugin.core.flow.If
condition: "{{ outputs.probe_alpha.row.verdict != 'PASS' or
outputs.probe_beta.row.verdict != 'PASS' }}"
then:
- id: alert_new_drift
type: io.kestra.plugin.core.flow.If
condition: "{{ (outputs.read_previous_verdict.value ?? '') != 'DRIFT' }}"
then:
- id: notify_drift
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
messageText: >-
PostgreSQL RLS decision drift: alpha expected {{ inputs.alpha_role
}}, actual {{ outputs.probe_alpha.row.actual_role }} (own={{
outputs.probe_alpha.row.own_rows }}, peer={{
outputs.probe_alpha.row.peer_rows }}, RLS={{
outputs.probe_alpha.row.rls_active }}); beta expected {{
inputs.beta_role }}, actual {{ outputs.probe_beta.row.actual_role
}} (own={{ outputs.probe_beta.row.own_rows }}, peer={{
outputs.probe_beta.row.peer_rows }}, RLS={{
outputs.probe_beta.row.rls_active }}). Hold the policy deployment;
inspect public.tenant_documents policies and canary rows.
Execution {{ execution.id }}.
retry: { type: exponential, interval: PT2S, maxInterval: PT10S, maxAttempts: 3 }
- id: remember_drift
type: io.kestra.plugin.core.kv.Set
key: postgres_rls_decision_last_verdict
value: DRIFT
kvType: STRING
- id: block_policy_change
type: io.kestra.plugin.core.execution.Exit
state: FAILED
else:
- id: alert_recovery
type: io.kestra.plugin.core.flow.If
condition: "{{ (outputs.read_previous_verdict.value ?? '') == 'DRIFT' or
(outputs.read_previous_verdict.value ?? '') == 'ERROR' }}"
then:
- id: notify_recovery
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
messageText: >-
PostgreSQL RLS decisions recovered: both roles see exactly their
own public.tenant_documents canary and neither sees the peer
canary. The policy migration may continue after review. Execution
{{ execution.id }}.
retry: { type: exponential, interval: PT2S, maxInterval: PT10S, maxAttempts: 3 }
- id: remember_clean
type: io.kestra.plugin.core.kv.Set
key: postgres_rls_decision_last_verdict
value: CLEAN
kvType: STRING
errors:
- id: read_previous_error_state
type: io.kestra.plugin.core.kv.Get
key: postgres_rls_decision_last_verdict
errorOnMissing: false
- id: alert_new_probe_error
type: io.kestra.plugin.core.flow.If
condition: "{{ (outputs.read_previous_error_state.value ?? '') != 'ERROR' }}"
then:
- id: notify_probe_error
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
messageText: >-
PostgreSQL RLS gate could not complete (execution {{ execution.id }}).
Inspect the failed task at {{ inputs.kestra_url
}}/ui/main/executions/company.team/{{ flow.id }}/{{ execution.id }}.
If validate_canaries failed, correct duplicate IDs or roles. If
probe_alpha or probe_beta failed, check relation grants, role
credentials and PostgreSQL health. Do not treat this run as a passing
policy test.
retry: { type: exponential, interval: PT2S, maxInterval: PT10S, maxAttempts: 3 }
- id: remember_probe_error
type: io.kestra.plugin.core.kv.Set
key: postgres_rls_decision_last_verdict
value: ERROR
kvType: STRING
outputs:
- id: alpha_decision
type: JSON
value: "{{ (outputs.probe_alpha.row ?? {}) | toJson }}"
- id: beta_decision
type: JSON
value: "{{ (outputs.probe_beta.row ?? {}) | toJson }}"
- id: status
type: STRING
value: "{{ outputs.probe_alpha.row.verdict ?? 'UNKNOWN' }} / {{
outputs.probe_beta.row.verdict ?? 'UNKNOWN' }}"