Auditing Kubernetes Manifests With SQL and a Findings Score

Kubernetes manifests are long, similar and security-relevant, which makes them the worst material to review by eye and the best to audit with a query. The questions are mechanical: which containers have no resource limits, which use latest, which run privileged, which lack a probe. Policy engines and scanners answer them too, and so does a table and a few lines of SQL, with the advantage that you can ask a question nobody wrote a rule for. This post audits a manifest of eight Deployments. It belongs to the cluster on analyzing configuration and metadata held in YAML. Syntax and schema checks come first, as in checking Kubernetes YAML before you apply it; this is the next layer, about what the valid files say.

Reading a multi-document file

A manifest file usually bundles resources separated by ---. Use safe_load_all, skip empty documents and non-Deployments, and emit one row per container:

rows = []
for doc in yaml.safe_load_all(text):
    if not doc or doc.get("kind") != "Deployment":
        continue
    spec = doc["spec"]["template"]["spec"]
    for c in spec["containers"]:
        sc = c.get("securityContext", {})
        rows.append((doc["metadata"]["name"], c["name"], c["image"], doc["spec"].get("replicas", 1),
                     "limits" in c.get("resources", {}),
                     "readinessProbe" in c or "livenessProbe" in c,
                     bool(sc.get("privileged", False)), bool(sc.get("runAsNonRoot", False))))

The .get() calls with defaults carry the audit's logic: a missing resources is "no limits", a missing securityContext is "not privileged and not non-root". Be explicit about those defaults, because absence of a field is the finding. The multi-document behaviors (a None for an empty document, a generator) are covered in multi document YAML streams. Init containers, sidecars, StatefulSets, DaemonSets and CronJobs need the same treatment with their own paths (spec.template.spec.containers, spec.jobTemplate.spec.template.spec.containers); extend the loop with a lookup of where the pod spec lives per kind. Pod-level securityContext also applies to containers that do not set their own, which a stricter audit merges in.

The rows go into DuckDB:

con.execute("create table c (deployment varchar, container varchar, image varchar, replicas int, has_limits boolean, has_probe boolean, privileged boolean, non_root boolean)")
con.executemany("insert into c values (?,?,?,?,?,?,?,?)", rows)

The questions

Each check is a short query. Results from the eight containers:

containers: 8
no resource limits: ['billing', 'cache', 'legacy']
no probe: ['etl', 'legacy', 'search']
latest or untagged image: ['billing', 'etl']
privileged: ['legacy']
may run as root: ['legacy', 'mailer']

No limits. Three containers can use unbounded memory, so one leak takes out a node. The query is select deployment from c where not has_limits.

No probe. Three have no readiness or liveness probe, so Kubernetes sends traffic to a process that may not be ready and does not restart one that hangs.

Latest or untagged. image like '%:latest' or image not like '%:%' found two. A deployment that references latest changes when the image is pushed, not when the manifest does, which breaks the link between a commit and what runs (finding inconsistent versions across YAML configs). An image with no tag at all means latest too, which is why the query tests for both.

Privileged. The legacy container runs privileged, which gives it nearly the host's capabilities. That is a finding in nearly every environment.

May run as root. Containers without runAsNonRoot: true may run as uid 0 depending on the image. Two qualify, the privileged one and mailer.

A reviewer reading eight manifests would find the privileged one; the missing runAsNonRoot on mailer and the missing probe on search are the kind of omission that is hard to see because nothing is there to look at. Absence is exactly what a table makes visible.

Ranking with a score

A list per check gives five lists. A work queue needs one ordering. A simple score adds a point for each failed check:

select deployment,
  (not has_limits)::int + (not has_probe)::int + (image like '%:latest')::int
  + privileged::int + (not non_root)::int as findings
from c order by findings desc, deployment limit 4;

('legacy', 4)
('billing', 2)
('etl', 2)
('cache', 1)

The legacy deployment fails four of five checks, billing and etl two each, cache one. The ordering says where to start. Weighting is a policy choice: a privileged container probably deserves more than a missing probe, so multiply the terms by weights that match your risk model, or list findings by severity and fix those first. The point of the score is triage, not precision. Keep it simple enough that engineers can predict it.

Combining columns

The most useful audit questions combine checks, and a table makes that trivial:

replicas of single-replica services without probes: 3

Three deployments run a single replica and have no probe. Neither fact alone is alarming. Together they mean that if the process hangs, nothing restarts it and nothing else serves traffic. Other combinations worth asking: workloads with latest and no limits (changes unpredictably and can consume unbounded resources), privileged containers in namespaces that hold secrets (join the namespace column), and workloads with many replicas and no pod disruption budget. These come from asking, not from a rule list, which is the advantage of having the data as rows.

From audit to practice

Run it regularly. A script in CI that parses the manifests in the repository and fails on selected findings (privileged, latest) is a policy check you wrote in an afternoon. A scheduled job that reports the full table to a channel keeps the softer findings visible. Pair it with a real policy engine for enforcement at admission time, because a repository audit sees what you intended to deploy and not what a cluster admits; query the cluster (kubectl get deploy -A -o yaml, parsed with the same loop) when you need the truth about what runs.

Keep false positives down. A sidecar that legitimately has no probe, or a job container that does not need runAsNonRoot, will show up; add an annotation convention (audit.example.com/ignore: probe) the script respects, so exceptions are visible and reviewed. And validate first with the schema tools, so the audit's assumptions about structure hold.

Keeping the audit honest

An audit script is code, and it has bugs and blind spots like any other. Three habits keep it trustworthy. First, test it with a manifest that has every weakness: ours does, with a deployment that fails four of five checks, and the script's output for it is the regression test. Second, make the checks explicit about defaults. Kubernetes applies defaults the manifest does not show (a container's securityContext can inherit from the pod's, a namespace can carry a LimitRange), so a finding means "the manifest does not say", and the report should use that wording instead of "the container is insecure". Third, review the false positives. A finding that engineers consistently dismiss is a rule to refine or retire, because an audit that cries wolf stops being read.

A final point is scale. Eight deployments is a demonstration. A repository with hundreds of manifests across environments produces a table of thousands of containers, which DuckDB handles in milliseconds, and the question changes from "which fail" to "who owns them". Add the owning team (from a label, a directory name or a CODEOWNERS entry) as a column, group the findings by team and send each team its own list. Findings with an owner get fixed; findings without one get discussed.

When a SQL audit of manifests is not enough

The manifests are templates. Helm charts and Kustomize overlays render to the YAML that matters. Render first (Helm values files without copy paste), then audit the output.

Defaults live elsewhere. A LimitRange or an admission controller may add limits or security contexts that the manifest does not show. The manifest says "no limits" and the cluster may apply them.

You need enforcement. An audit is a report. Admission policies block bad workloads before they run.

The risk is behavioral. A well-configured container can still be a bad one. Configuration audits find configuration problems.

The fields are defined in the Kubernetes pod security documentation and the API reference for containers. To look at a manifest as data, use the YAML to JSON converter.