id: postgres-orphaned-large-object-cleanup-guard
namespace: company.team
inputs:
- id: postgres_url
type: STRING
defaults: jdbc:postgresql://localhost:5432/postgres
description: PostgreSQL JDBC URL for the database whose large objects are
checked. Example jdbc:postgresql://postgres:5432/app.
- id: use_secret_auth
type: BOOL
defaults: true
description: Leave true in production so the connection reads POSTGRES_USERNAME
and POSTGRES_PASSWORD from secrets. Set false only to pass credentials
inline in a disposable test environment.
- id: postgres_username
type: STRING
defaults: postgres
description: Username used only when use_secret_auth is false. Ignored in production.
- id: postgres_password
type: STRING
defaults: ""
description: Password used only when use_secret_auth is false. Ignored in production.
- id: min_age_minutes
type: INT
defaults: 60
description: Minimum age in minutes. An orphan is unlinked only once this guard
has seen it with no referencing row for at least this long. An orphan seen
for the first time is reported but never unlinked on the same run, so a
large object created moments before its owner row is spared.
- id: dry_run
type: BOOL
defaults: true
description: When true, report the orphaned large objects without unlinking anything.
- id: auto_remediate
type: BOOL
defaults: false
description: When true and dry_run is false, unlink the aged orphans behind an
approval gate, then re-read pg_largeobject_metadata.
variables:
cursor_key: "plo_firstseen_{{ inputs.postgres_url | slugify }}_all"
now_sql: "SELECT extract(epoch FROM now())::bigint AS now_epoch"
detect_sql: |
WITH cols AS (
SELECT quote_ident(n.nspname) AS s, quote_ident(c.relname) AS r, quote_ident(a.attname) AS col
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_type t ON t.oid = a.atttypid
WHERE c.relkind IN ('r','p','m')
AND a.attnum > 0 AND NOT a.attisdropped
AND n.nspname NOT IN ('pg_catalog','information_schema','pg_toast')
AND t.typname IN ('oid','lo')
),
gen AS (
SELECT string_agg(
'SELECT '||col||'::oid::text AS loid FROM '||s||'.'||r||' WHERE '||col||' IS NOT NULL',
' UNION '
) AS unionsql
FROM cols
),
refdoc AS (
SELECT CASE WHEN unionsql IS NULL THEN NULL
ELSE query_to_xml('SELECT DISTINCT loid FROM ('||unionsql||') u', false, false, '')
END AS doc
FROM gen
),
referenced AS (
SELECT (xpath('/table/row/loid/text()', doc))::text[] AS arr FROM refdoc
),
ref_oids AS (
SELECT NULLIF(x,'')::oid AS loid FROM referenced, unnest(coalesce(arr, ARRAY[]::text[])) AS x
)
SELECT m.oid::text AS loid, m.lomowner::regrole::text AS owner
FROM pg_largeobject_metadata m
WHERE NOT EXISTS (SELECT 1 FROM ref_oids r WHERE r.loid = m.oid)
ORDER BY m.oid
triggers:
- id: daily_orphan_lo_check
type: io.kestra.plugin.core.trigger.Schedule
disabled: true
cron: "0 3 * * *"
description: Daily orphaned large object sweep. Disabled by default and pinned
to safe mode so it reports without unlinking until an operator enables it.
inputs:
dry_run: true
auto_remediate: false
tasks:
- id: now_probe
type: io.kestra.plugin.jdbc.postgresql.Query
description: Read the server clock as epoch seconds so the first-seen age is
measured against the PostgreSQL clock, not the Kestra host clock.
url: "{{ inputs.postgres_url }}"
username: "{{ inputs.use_secret_auth ? secret('POSTGRES_USERNAME') :
inputs.postgres_username }}"
password: "{{ inputs.use_secret_auth ? secret('POSTGRES_PASSWORD') :
inputs.postgres_password }}"
fetchType: FETCH
sql: "{{ vars.now_sql }}"
- id: detect
type: io.kestra.plugin.jdbc.postgresql.Query
description: Scan every user column of type oid or lo, union the large object
ids they hold and diff that referenced set against
pg_largeobject_metadata. The rows that come back are large objects no
column points at, which no delete reclaims and autovacuum never touches.
url: "{{ inputs.postgres_url }}"
username: "{{ inputs.use_secret_auth ? secret('POSTGRES_USERNAME') :
inputs.postgres_username }}"
password: "{{ inputs.use_secret_auth ? secret('POSTGRES_PASSWORD') :
inputs.postgres_password }}"
fetchType: FETCH
sql: "{{ render(vars.detect_sql) }}"
- id: read_cursor
type: io.kestra.plugin.core.kv.Get
description: Read the first-seen cursor, a KV map from large object id to the
epoch second this guard first saw it orphaned. A missing key is the first
run and returns null.
key: "{{ render(vars.cursor_key) }}"
errorOnMissing: false
- id: compose
type: io.kestra.plugin.core.output.OutputValues
description: Build one JSON document holding the server clock, the minimum age
in seconds, the stored cursor and the detected orphans, so every later
step reads the same input.
values:
blob: >-
{{ {'now': outputs.now_probe.rows[0].now_epoch, 'thr':
(inputs.min_age_minutes * 60), 'old': (outputs.read_cursor.value ?? ''),
'orphans': outputs.detect.rows} | toJson }}
- id: enrich
type: io.kestra.plugin.core.output.OutputValues
description: For each orphan, look up its first-seen epoch (or set it to now on
first sight), compute its age and flag it a target once the age is at or
past the minimum.
values:
all_json: >-
{{ outputs.compose.values.blob | jq('(.now|tonumber) as $N |
(.thr|tonumber) as $T | (.old | if type == "string" then (if . == ""
then {} else fromjson end) elif . == null then {} else . end) as $old |
[ .orphans[] | (.loid|tostring) as $k | {loid:.loid, owner:.owner, k:$k,
first_seen:(($old[$k] // $N)|tonumber)} | . + {age:($N - .first_seen)} |
. + {is_target:(.age >= $T)} ]') | first | toJson }}
- id: plan
type: io.kestra.plugin.core.output.OutputValues
description: Split the enriched list into the unlink targets and the fresh
orphans, build the pruned cursor and the human readable lists once so the
logs, the alert and the failure message all agree.
values:
targets_json: >-
{{ outputs.enrich.values.all_json | jq('map(select(.is_target)) |
map({loid, owner, first_seen, age})') | first | toJson }}
new_cursor_json: >-
{{ outputs.enrich.values.all_json | jq('map({(.k): .first_seen}) | add
// {}') | first | toJson }}
targeted_names: >-
{{ outputs.enrich.values.all_json | jq('map(select(.is_target)) |
map("loid "+(.loid|tostring)+" (owner "+(.owner|tostring)+")") | join(",
")') | first }}
fresh_names: >-
{{ outputs.enrich.values.all_json | jq('map(select(.is_target | not)) |
map("loid "+(.loid|tostring)+" (seen "+(.age|tostring)+"s ago)") |
join(", ")') | first }}
- id: write_cursor
type: io.kestra.plugin.core.kv.Set
description: Write the pruned first-seen cursor back. It keeps the first-seen
epoch for orphans still present, records now for orphans seen for the
first time and drops entries for ids that are no longer orphaned. No ttl,
so a long lived orphan keeps its original first-seen time.
key: "{{ render(vars.cursor_key) }}"
value: "{{ outputs.plan.values.new_cursor_json }}"
- id: log_detect
type: io.kestra.plugin.core.log.Log
message: |
PostgreSQL orphaned large objects: {{ outputs.detect.size }} found. Targets past the {{ inputs.min_age_minutes }} minute minimum age: {{ outputs.plan.values.targets_json | jq('length') | first }}.
Targeted: {{ outputs.plan.values.targeted_names }}
Within minimum age (reported, not unlinked): {{ outputs.plan.values.fresh_names }}
Mode: dry_run={{ inputs.dry_run }} auto_remediate={{ inputs.auto_remediate }}
- id: guard
type: io.kestra.plugin.core.flow.If
condition: "{{ outputs.detect.size > 0 }}"
then:
- id: findings_alert
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
description: Alert on every detection, in dry run and in remediate mode, so a
human sees the orphaned large objects even when nothing is unlinked.
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
payload: |
{"text": "PostgreSQL orphaned large object guard: {{ outputs.detect.size }} orphan(s), {{ outputs.plan.values.targets_json | jq('length') | first }} past the {{ inputs.min_age_minutes }}m minimum age. Targeted {{ outputs.plan.values.targeted_names }}. dry_run={{ inputs.dry_run }} auto_remediate={{ inputs.auto_remediate }}."}
- id: mode
type: io.kestra.plugin.core.flow.If
condition: "{{ inputs.auto_remediate and not inputs.dry_run and
(outputs.plan.values.targets_json | jq('length') | first) > 0 }}"
then:
- id: approval_gate
type: io.kestra.plugin.core.flow.Pause
description: Hold the run until a human approves the unlink. The destructive
step never starts on its own.
pauseDuration: PT2H
behavior: FAIL
onResume:
- id: approved
type: BOOL
defaults: false
description: Set true to unlink the listed orphaned large objects. Anything else
leaves them in place.
- id: apply
type: io.kestra.plugin.core.flow.If
condition: "{{ outputs.approval_gate.onResume.approved ?? false }}"
then:
- id: unlink_orphans
type: io.kestra.plugin.core.flow.Loop
description: Remediation loop. One lo_unlink per aged orphan, concurrencyLimit 1
so the statements run in turn and never race. allowFailure so
one rejected unlink does not stop the pass and the re-read
stays the real gate.
concurrencyLimit: 1
values: "{{ outputs.plan.values.targets_json }}"
tasks:
- id: unlink_one
type: io.kestra.plugin.jdbc.postgresql.Query
description: Unlink one orphaned large object by its id, only if the same orphan
query still lists it, so an object that gained a
referencing row during the approval wait is kept.
lo_unlink removes the metadata row and every data page.
allowFailure: true
url: "{{ inputs.postgres_url }}"
username: "{{ inputs.use_secret_auth ? secret('POSTGRES_USERNAME') :
inputs.postgres_username }}"
password: "{{ inputs.use_secret_auth ? secret('POSTGRES_PASSWORD') :
inputs.postgres_password }}"
fetchType: FETCH
sql: "SELECT lo_unlink(d.loid::oid) AS unlinked FROM ({{ render(vars.detect_sql)
}}) d WHERE d.loid = '{{ fromJson(item.value).loid }}'"
- id: unlink_log
type: io.kestra.plugin.core.log.Log
message: "Checked loid {{ fromJson(item.value).loid }} again (owner {{
fromJson(item.value).owner }}) and issued lo_unlink if it
was still unreferenced."
- id: reverify
type: io.kestra.plugin.jdbc.postgresql.Query
description: Re-run the same orphan diff. The large objects still here are
compared against the unlink targets, so a fresh orphan left
inside its minimum age does not count as a failure.
url: "{{ inputs.postgres_url }}"
username: "{{ inputs.use_secret_auth ? secret('POSTGRES_USERNAME') :
inputs.postgres_username }}"
password: "{{ inputs.use_secret_auth ? secret('POSTGRES_PASSWORD') :
inputs.postgres_password }}"
fetchType: FETCH
sql: "{{ render(vars.detect_sql) }}"
- id: verify_plan
type: io.kestra.plugin.core.output.OutputValues
description: Keep only the unlink targets that are still present after the pass.
The fresh orphans left in place are excluded by design.
values:
still_json: >-
{{ {'targets': outputs.plan.values.targets_json,
'remaining': outputs.reverify.rows} | toJson |
jq('(.remaining | map(.loid|tostring)) as $rem | (.targets |
if type == "string" then fromjson else . end) | map(.loid as
$l | select($rem | index($l|tostring)))') | first | toJson
}}
still_names: >-
{{ {'targets': outputs.plan.values.targets_json,
'remaining': outputs.reverify.rows} | toJson |
jq('(.remaining | map(.loid|tostring)) as $rem | (.targets |
if type == "string" then fromjson else . end) | map(.loid as
$l | select($rem | index($l|tostring)) | "loid
"+(.loid|tostring)) | join(", ")') | first }}
- id: verify_result
type: io.kestra.plugin.core.flow.If
condition: "{{ (outputs.verify_plan.values.still_json | jq('length') | first) >
0 }}"
then:
- id: remediation_failed
type: io.kestra.plugin.core.execution.Fail
errorMessage: >-
{{ outputs.verify_plan.values.still_json | jq('length') |
first }} targeted large object(s) still present after the
unlink pass: {{ outputs.verify_plan.values.still_names }}.
The connecting user may not own the large objects or a
referencing row was removed during the run.
else:
- id: remediated
type: io.kestra.plugin.core.log.Log
message: "Unlinked and verified. {{ outputs.plan.values.targets_json |
jq('length') | first }} target(s) before, 0 of them
remaining after."
else:
- id: not_approved
type: io.kestra.plugin.core.log.Log
message: "Approval not granted, so nothing was unlinked. {{
outputs.plan.values.targets_json | jq('length') | first }}
aged orphan(s) still remain."
else:
- id: report_only
type: io.kestra.plugin.core.log.Log
message: "Report only (dry_run={{ inputs.dry_run }}, auto_remediate={{
inputs.auto_remediate }}). {{ outputs.plan.values.targets_json |
jq('length') | first }} aged orphan(s) would be unlinked: {{
outputs.plan.values.targeted_names }}. No change made."
else:
- id: clean
type: io.kestra.plugin.core.log.Log
message: "No orphaned large objects found. Every large object in
pg_largeobject_metadata is referenced by a live oid or lo column.
Nothing to unlink."
errors:
- id: alert_failure
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
description: Post to Slack on any failure, including a connection error or
targeted large objects still present after an unlink pass.
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
payload: |
{"text": "PostgreSQL orphaned large object guard FAILED (execution {{ execution.id }}). A connection error, an unlink error or targeted large objects still present after an unlink pass. Check the run."}