Enforcing Row-Level Access Control for Restricted Grant Funds

This guide is part of the Data Security & Access Boundaries section within the broader Core Architecture & Compliance Mapping framework, and it solves one narrow problem: how do you guarantee that a grant manager querying the awards table sees only the funds their organization is entitled to — and, within those, only the donor-restricted balances they are cleared to touch — without threading a WHERE org_id = ? clause through every query in the codebase?

The application-layer answer — filter in the ORM and trust that nobody forgets — fails the first time a new report, a background job, or a psql session skips the filter. A single missing predicate exposes another nonprofit’s restricted endowment. The durable answer pushes the boundary into Postgres itself with row-level security (RLS), so the database rejects unentitled rows no matter which query, tool, or developer issued them. This guide enables RLS on the awards and transactions tables, writes policies that scope by organization, fund, and donor restriction, sets the acting principal through a session variable the app cannot forge, keeps the application role a non-superuser so it cannot bypass the policy, and logs each access decision for audit.

When to Use This Approach #

Reach for database-enforced RLS when all three conditions hold:

• Multiple tenants or funds share one physical table. If every nonprofit or every donor restriction lived in its own database, connection routing would be the boundary. Here, awards and transactions for many organizations coexist in shared tables, so the boundary has to be a per-row predicate the engine enforces on every read and write. Application-side filtering is defense in depth, not the boundary.
• The data is legally restricted, not merely private. Donor-restricted net assets under FASB ASC 958 and federal award funds under 2 CFR §200.303 internal-controls requirements carry a duty to prevent commingling and unauthorized access. A leaked balance is a control failure, not a cosmetic bug, and NIST SP 800-53 AC-3 access enforcement expects the mediation to happen at the resource, not only in the caller.
• You cannot audit every query path. Ad hoc reports, migrations, and third-party BI tools all reach the tables. RLS is the one control that binds them all uniformly.

Column-level masking of PII, encryption at rest, and key management are explicitly out of scope here; those belong to the sibling guide on securing PII in nonprofit grant databases. The immutable recording of the access decisions this page emits belongs to Audit Trail & Evidence Automation. This guide covers only the row-visibility boundary itself.

Step-by-Step Implementation #

The reference implementation targets PostgreSQL 14+ and Python 3.10+ with SQLAlchemy 2.0. Postgres RLS is a native feature; the Python layer’s only job is to open a transaction, stamp the principal into session variables, and never fall back to string-built SQL. Install the driver stack:

sqlalchemy==2.0.30
psycopg[binary]==3.1.19

One contract to internalize before writing any code: RLS is enforced per role, and the table owner plus any superuser are exempt by default. A policy you can prove works in a test can still leak in production if the application connects as the owning role or a superuser. Step 4 is not optional hardening — it is the step that makes the policy real.

Step 1: Enable row-level security on the awards and transactions tables #

RLS is off by default even after you write policies; you must arm it per table. ENABLE ROW LEVEL SECURITY turns the feature on, and FORCE ROW LEVEL SECURITY extends it to the table owner as well, closing the most common leak where the migration role owns the table and silently sees everything.

-- Arm RLS on the two tables that carry restricted balances.
ALTER TABLE awards        ENABLE ROW LEVEL SECURITY;
ALTER TABLE awards        FORCE  ROW LEVEL SECURITY;
ALTER TABLE transactions  ENABLE ROW LEVEL SECURITY;
ALTER TABLE transactions  FORCE  ROW LEVEL SECURITY;

-- Deny-by-default: with RLS on and no permissive policy, every row is hidden.

Once RLS is enabled with no policy attached, the table returns zero rows to every non-owner — a deny-by-default posture. That is the correct starting point: you open access deliberately in Step 2, never the reverse. FORCE matters because migrations and seed scripts typically run as the owner, and you do not want the owning connection to be the one path that ignores the boundary.

Step 2: Write policies that scope rows by org, fund, and donor restriction #

A policy is a boolean expression evaluated per row; a row is visible only when it returns true. Read the acting principal’s organization and donor clearance from session variables (set in Step 3) via current_setting(name, true) — the true second argument returns NULL instead of erroring when the variable is unset, so an unstamped connection sees nothing rather than everything.

-- Visibility: same org, and either the fund is unrestricted or the
-- principal's donor scope covers the row's donor_restriction.
CREATE POLICY awards_org_donor_scope ON awards
  FOR SELECT
  USING (
    org_id = current_setting('app.current_org', true)::uuid
    AND (
      donor_restriction IS NULL
      OR donor_restriction = ANY (
        string_to_array(current_setting('app.donor_scope', true), ',')
      )
    )
  );

-- Writes are narrower: only unrestricted funds or an explicit donor grant,
-- and a WITH CHECK clause stops a user writing a row they could not read.
CREATE POLICY awards_write_scope ON awards
  FOR ALL
  USING      (org_id = current_setting('app.current_org', true)::uuid)
  WITH CHECK (org_id = current_setting('app.current_org', true)::uuid);

The USING clause filters rows on read and on the pre-image of a write; the WITH CHECK clause validates the post-image of an INSERT/UPDATE, which is what prevents a user from moving a row into another organization. Splitting SELECT from ALL lets read visibility (donor-scoped) differ from write authority (org-scoped) — the common shape for grant data, where a manager reads restricted balances but only edits their own org’s records. Mirror the same two policies onto transactions keyed by its award_id join or its own org_id column.

Step 3: Set the current principal with a session GUC, not application trust #

The policies are only as trustworthy as the session variables they read, so those variables must be set by a small, audited code path — never interpolated from a request. Use set_config(name, value, is_local => true), the function form of SET LOCAL: it is scoped to the current transaction, resets automatically on commit or rollback, and — unlike the bare SET LOCAL statement — accepts bind parameters, so the principal id can never break out of a string.

import logging
from contextlib import contextmanager
from typing import Iterator, Sequence

from sqlalchemy import text
from sqlalchemy.engine import Connection

AUDIT_LOGGER = logging.getLogger("grant_access.audit")


@contextmanager
def scoped_principal(
    conn: Connection,
    *,
    user_id: str,
    org_id: str,
    donor_scope: Sequence[str],
) -> Iterator[Connection]:
    """Open a transaction and stamp the principal into transaction-local GUCs.

    Uses set_config(..., is_local => true) with bind parameters so the values
    are parameterized, never string-built, and reset on transaction exit.
    """
    scope_csv = ",".join(donor_scope)
    with conn.begin():
        conn.execute(
            text(
                "SELECT set_config('app.current_user', :uid,   true), "
                "       set_config('app.current_org',  :oid,   true), "
                "       set_config('app.donor_scope',  :scope, true)"
            ),
            {"uid": user_id, "oid": org_id, "scope": scope_csv},
        )
        AUDIT_LOGGER.info(
            "principal_bound user=%s org=%s donor_scope=%s",
            user_id, org_id, scope_csv or "(none)",
        )
        yield conn

Because set_config uses bind parameters (:uid, :oid, :scope), the principal identity is transported as data, not concatenated into SQL — the same discipline the sibling securing PII in nonprofit grant databases guide applies to every parameter. The is_local => true flag ties the setting to the transaction opened by conn.begin(); if the request errors and rolls back, the principal is gone, so a leaked connection from a pool never carries a stale identity into the next request.

Step 4: Run the app under a non-superuser role so policies cannot be bypassed #

A Postgres superuser — and BYPASSRLS roles — ignore every policy. This is the single most common way an RLS deployment silently fails: the app connects with an over-privileged role and the carefully written policies never run. Provision a dedicated, minimal role and connect as it.

-- Least-privilege application role. No SUPERUSER, no BYPASSRLS, no ownership.
CREATE ROLE app_user LOGIN PASSWORD :'app_pw'
  NOSUPERUSER NOCREATEDB NOCREATEROLE NOBYPASSRLS;

GRANT SELECT, INSERT, UPDATE ON awards, transactions TO app_user;
-- app_user is NOT the table owner, so FORCE ROW LEVEL SECURITY still applies.

Verify the running connection at startup and refuse to serve if it is over-privileged — a fail-closed check is cheaper than an incident. rolsuper and rolbypassrls are the two flags that void RLS, and pg_has_role confirms the app is not connected as the table owner.

from sqlalchemy.engine import Connection
from sqlalchemy import text


def assert_role_cannot_bypass_rls(conn: Connection) -> None:
    """Fail closed if the connected role can bypass row-level security."""
    row = conn.execute(
        text(
            "SELECT rolsuper, rolbypassrls "
            "FROM pg_roles WHERE rolname = current_user"
        )
    ).one()
    if row.rolsuper or row.rolbypassrls:
        AUDIT_LOGGER.critical(
            "startup_refused role=%s bypasses RLS (super=%s bypassrls=%s)",
            "current_user", row.rolsuper, row.rolbypassrls,
        )
        raise RuntimeError(
            "Application role bypasses RLS; connect as a NOSUPERUSER "
            "NOBYPASSRLS role that does not own the protected tables."
        )
    AUDIT_LOGGER.info("startup_ok connected role cannot bypass RLS")

This satisfies the NIST SP 800-53 AC-3 requirement that access be enforced by the resource, not merely requested politely by the caller: even a SQL-injection foothold in the app cannot escalate past the policy, because the connected role has no exemption to escalate into.

Step 5: Log every access decision for the audit trail #

RLS filters silently — that is its strength for confidentiality and its weakness for accountability, because a filtered-out row leaves no trace. To satisfy the 2 CFR §200.303 internal-controls duty, record the principal, the scope, and the row count returned for each entitled query, so an auditor can reconstruct who saw which funds and when.

from typing import Any

from sqlalchemy import text
from sqlalchemy.engine import Connection


def logged_award_query(
    conn: Connection, *, user_id: str, org_id: str
) -> list[dict[str, Any]]:
    """Run a policy-filtered read and emit a structured access-decision record."""
    result = conn.execute(
        text(
            "SELECT award_id, fund_code, donor_restriction, balance "
            "FROM awards ORDER BY award_id"
        )
    )
    rows = [dict(r._mapping) for r in result]
    AUDIT_LOGGER.info(
        "access_decision user=%s org=%s table=awards rows_returned=%d",
        user_id, org_id, len(rows),
    )
    return rows

The query carries no WHERE org_id = ... clause at all — the policy supplies it — which is the proof that the boundary is the database’s, not the query author’s. Ship these access_decision lines to the append-only store described in building an append-only audit ledger in Postgres so the record cannot be edited after the fact.

Verification #

Confirm the boundary holds with four checks:

1. Deny-by-default is real. With RLS enabled and no session variables set, run SELECT count(*) FROM awards on a fresh app_user connection and assert it returns 0 — an unstamped principal sees nothing, not everything.
2. Cross-org rows are invisible. Stamp app.current_org to org A, insert a row under org B directly (as owner in a separate session), then assert org A’s query never returns it, regardless of how the query is written.
3. Donor scope narrows within an org. With the same org but a donor_scope that omits endowment-2024, assert that unrestricted rows appear and the endowment-2024-restricted row does not.
4. The app role cannot bypass. Assert assert_role_cannot_bypass_rls raises RuntimeError when pointed at a superuser connection and passes for app_user.

A compliant run emits one principal_bound line and one access_decision line per request; a startup on an over-privileged role emits a CRITICAL startup_refused and never serves traffic.

with scoped_principal(conn, user_id="u-42", org_id=ORG_A,
                      donor_scope=["general"]) as c:
    rows = logged_award_query(c, user_id="u-42", org_id=ORG_A)

assert all(r["org_id"] == ORG_A for r in rows) if rows else True
assert not any(r["donor_restriction"] == "endowment-2024" for r in rows)
assert_role_cannot_bypass_rls(conn)  # passes only for a NOBYPASSRLS role

Common Errors & Fixes #

Related #

• Parent section: Data Security & Access Boundaries
• The column-level companion boundary: Securing PII in Nonprofit Grant Databases
• Where the access-decision logs become tamper-evident evidence: Audit Trail & Evidence Automation
• The append-only sink for these records: Building an Append-Only Audit Ledger in Postgres