Key takeaways
- Layer 1 — Role ACL: declarative rules over resource, action, fields, and data scope, evaluated server-side on every request.
-
Layer 2 — Forced RLS: PostgreSQL row policies driven by transaction-scoped session GUCs, on an application role that can never
BYPASSRLS— with pooling handled so context cannot leak between requests. - Layer 3 — Field masking in SQL: protected columns are masked inside the query, so unmasked values never leave PostgreSQL at all.
- Semantics are fail-closed: missing context, missing rule, or an evaluation error all resolve to deny (403 or zero rows), never to exposure.
- AI Employees obey all three layers and are additionally hard-denied the build surface, with every action audit-logged.
Why one layer is never enough
Most application breaches are not exotic. They are one missed authorization check on one endpoint — a filter forgotten in a bulk export, an admin route reused by a background job, an ORM query built before the permission check ran. When authorization lives only in application code, the security of every record depends on every code path being right, forever.
Defense in depth reframes the problem: assume some layer will eventually have a bug, and arrange the layers so the bug’s blast radius is bounded by the layers beneath it. If the application-layer check is bypassed, the database still refuses to return rows outside the caller’s scope. If a query somehow addresses the right rows with the wrong privileges, the sensitive columns are still masked inside the SQL. Each layer is simple enough to audit on its own; their composition is what a security reviewer can actually sign off on. The full picture sits on our Security page; this post walks the three layers in order.
Layer 1: Role-based ACL — the intent layer
The first layer expresses what each role is meant to do. Because Megapodes applications are declarative metadata (see Features), ACL rules are part of the application definition itself — versioned, diffable, and promoted through environments alongside the schema. A rule speaks four dimensions:
- Resource — which collection or surface the rule governs.
- Action — create, read, update, delete, or a workflow-level operation.
- Fields — which attributes of the resource the role may see or write; the field lists that drive Layer 3.
- Data scope — which rows: own records, team records, all records — the predicate that Layer 2 will enforce again in the database.
Evaluation happens server-side on every request; the client UI merely reflects the outcome. A request with no matching allow rule is denied with an explicit 403 — absence of permission is denial, not a fall-through. Layer 1 is where administrators reason about policy. But intent expressed in application code is a promise, not a guarantee, which is why the same intent is compiled downward into the database.
Layer 2: Forced Row-Level Security — the database refuses
PostgreSQL Row-Level Security attaches policies to tables so that every query — regardless of who wrote it or which code path issued it — is implicitly filtered to permitted rows. Three implementation details decide whether RLS is a real boundary or decoration.
Forced, not merely enabled
Megapodes enables RLS in forced mode, so policies apply even to the table owner. And the application connects as a dedicated database role that is never granted BYPASSRLS and is not a superuser. This is the keystone: no matter what SQL the application layer produces — under a bug, a compromise, or a hallucinated query — the database applies row policies. The application cannot opt out of its own containment.
Identity via session GUCs
Policies need to know who is asking. The application authenticates the end user (short-lived rotating JWTs; argon2id at rest; OIDC-ready), then hands the identity to PostgreSQL as transaction-scoped session variables — GUCs, set via set_config(..., true) at the start of the transaction. Policies read those GUCs to evaluate the caller’s user ID and roles against each row. The database enforces scope per-transaction with the application supplying only claims, never exemptions.
The connection-pooling pitfall
This is where naive RLS implementations go wrong. Pooled connections are reused across requests, so session state set for one user can survive into the next request’s queries — the classic cross-tenant leak. The discipline is twofold. First, context is transaction-scoped: set inside the transaction, gone at commit or rollback, so it cannot outlive the request that set it. Second — and this is the fail-closed part — policies treat an absent or empty GUC as deny. A connection that reaches a query without context returns zero rows. The failure mode of forgetting to set identity is an empty result and an error to investigate, never another tenant’s data. Combined with schema-per-app tenancy, a scoping defect in one application cannot even address another application’s tables.
Layer 3: Field masking — applied in SQL, not after it
Row access is not field access. A support agent may legitimately open a customer record yet have no business seeing the customer’s tax ID; a finance role may see amounts but not medical notes. Layer 3 enforces the ACL’s field rules — and does it inside the query itself.
The placement is the point. If masking ran in application code, the unmasked values would already have left PostgreSQL: present in the wire result, in ORM objects, in memory — one forgotten serializer, one verbose error payload, one debug log away from disclosure. Megapodes instead rewrites the query so that protected columns are masked or excluded in the SQL, per the requesting role. Data the caller is not entitled to never leaves the database at all. Everything downstream inherits the guarantee for free: API responses, exports, workflow steps, logs — and, critically, the context window of any AI reading the data. A model cannot leak a value that was never in its input.
Fail-closed, everywhere
The stack’s semantics are uniform: every ambiguous situation resolves to denial. No matching ACL rule — 403. Missing session context — zero rows. No field grant — masked. An error during policy evaluation aborts the request rather than proceeding unfiltered. Fail-open systems leak precisely when they are misconfigured, which is precisely when nobody is looking; fail-closed systems turn misconfiguration into a visible, fixable outage of one feature. For a system of record, that is the correct trade, and we treat it as non-negotiable.
AI agents obey the same three layers
The question every reviewer now asks: what can the AI see? The answer is structural, not aspirational — an AI Employee in Megapodes is a client of the same stack, not a peer of it.
AI Employees are role-bound. When one executes a data-only tool — read, summarize, extract, classify, fill_form, answer — the call passes Layer 1’s ACL like any user request, runs under Layer 2’s forced RLS via the same transaction-scoped GUCs, and receives Layer 3’s masking in SQL. Give an AI Employee the support-agent role and it sees exactly what a human support agent sees: same rows, same masked fields. Its RAG retrievals from pgvector knowledge bases return cited sources drawn only from permitted data, because retrieval is a database query under the same policies.
Two properties harden this further. AI Employees are hard-denied the build surface server-side — the runtime AI plane cannot modify schema, permissions, or workflows, and the build-time plane cannot touch runtime data; the two planes cannot cross, and we test that boundary adversarially. And every AI action lands in the append-only audit log with actor_type distinguishing human, ai_build, and ai_employee, so “what did the AI access, as whom, and when” is a query, not an investigation. How the governed build pipeline complements this at build time is covered on How It Works.
This is what makes AI adoption tractable for a security team: authorization for AI is not a new, parallel policy engine to review. It is the same three layers you already audited for humans — enforced in the database, where neither an engineer’s bug nor a model’s mistake can reach around them. For deployment models and security-review support, see our Enterprise page.
The principle underneath
Every mature engineering discipline eventually separates describing a system from executing it: SQL over hand-written file scans, infrastructure-as-code over shell scripts, Kubernetes manifests over deploy scripts. In each case the industry accepted a bounded declarative surface in exchange for validation, diffing, and auditability. AI-built applications are simply the next system to learn this lesson — and with a generator as unpredictable as an LLM in the loop, the lesson is not optional. Governance is not friction added to the AI. It is the reason the AI can be trusted with the job at all.
Frequently asked questions
Why use three authorization layers instead of one well-tested one?
Because the layers fail differently. An application-layer ACL bug leaks whatever that code path touched; forced Row-Level Security in PostgreSQL still filters the rows; SQL-level masking still strips protected fields. Defense in depth means a single defect produces a degraded, still-safe result instead of a breach — and each layer is simple enough to reason about independently.
Does PostgreSQL Row-Level Security work with connection pooling?
Yes, if the session context is handled with discipline. Megapodes sets the user’s identity and roles as transaction-scoped session GUCs at the start of each transaction, so the context lives and dies with the transaction rather than the pooled connection. Policies treat a missing or empty GUC as deny, so a connection that skipped context-setting returns zero rows instead of another tenant’s data.
Why apply field masking in SQL rather than in application code?
Because data that never leaves PostgreSQL cannot be leaked by anything downstream. If masking happened in the application, the unmasked values would exist in database results, ORM objects, and memory — one forgotten code path away from a serialized response. Applying masking in the query itself means logs, error payloads, exports, and AI context windows only ever see the masked form.
Do AI Employees get any special data access?
No. An AI Employee acts under a role exactly as a human user does: its tool calls pass the same ACL checks, run under the same forced RLS policies via the same session GUCs, and receive the same field masking in SQL. It is additionally hard-denied the build surface server-side, and every action it takes is recorded in the append-only audit log with actor_type ai_employee.
Put the authorization stack in front of your security team
We will walk through ACL, forced RLS, and SQL-level masking against your own data model — and answer the hard questions live.
Self-hosted — your data never leaves your network · Every change human-approved and audit-logged · SSO/OIDC available · We support your security review
