PostgreSQL row security: test the role that actually reads

A role is a PostgreSQL identity with privileges. A schema groups database objects; a table holds rows. A table grant permits a kind of operation such as SELECT. Row-level security (RLS) adds row conditions for ordinary reads and writes. These layers answer different questions: which role is connected, which operation is granted, and which rows may participate?

Our scratch table has two independently defined records:

The two tenant roles receive SELECT and INSERT grants. They are neither table owners, superusers nor BYPASSRLS roles. lab_owner owns the table. lab_bypass is a deliberately powerful maintenance example with BYPASSRLS. The setup records these actual role attributes in role_attributes.

These are separate database roles to make the mechanism inspectable. Many applications instead share a database role and bind trusted request context to a transaction. That requires its own context-setting, pool reuse and denial tests; this lab does not implement that design.

The scoped policy is deliberately small:

ALTER TABLE lab.documents ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_scope ON lab.documents
USING (tenant_id = current_user)
WITH CHECK (tenant_id = current_user);

current_user is the effective database role, not a tenant string sent by an agent or browser. USING controls eligible existing rows. WITH CHECK controls proposed new rows for the relevant writes. A filtered SELECT can successfully return zero rows; that is different from a rejected INSERT. Neither establishes whether an application authenticated the right person.

Enabling RLS without an applicable policy denies ordinary row access even when the role has a table grant. A table owner ordinarily bypasses row policies. FORCE ROW LEVEL SECURITY subjects the owner to them, but does not constrain superusers or BYPASSRLS roles. An owner can also change the table's controls. Choosing an ordinary runtime role remains part of the boundary.

Download the original Python runner. Use your normal approved process to install Docker and cache a PostgreSQL 17 image. If allowed in your environment, the cache command is:

docker pull postgres:17
python3 --version
docker version

The runner never pulls an image. It resolves the cached tag once and starts that exact local image ID. Keep image_id, image_repo_digests and postgres_version with your results: postgres:17 is a moving major-version tag, not an immutable release identity or evidence of a verified signature.

One uniquely named scratch container uses a temporary memory-backed data volume, no published host port and Docker's none network. Readiness uses loopback inside that container; the actual SQL calls use its local socket. Local trust login supplies each fictional connection identity without checking a password. That fixture is confined to the disposable container and is not a production authentication example. The runner removes only its owned container on ordinary exit or failure and reports whether removal was confirmed. A forced process kill or unavailable Docker daemon can prevent cleanup.

Before opening the interpretation below, predict the returned IDs or SQLSTATE for each case. Keep your first answers separately from the report.

python3 fde-postgres-row-security.py > original.json
python3 -m json.tool original.json

The ten cases run in order in one fresh scratch database. changes records the DDL between observations. Every read retains its exact SQL, role, exit code and actual rows. Expected refusal attempts additionally retain SQLSTATE and the executed statement. A successful runner exit means it produced observations and confirmed cleanup; it does not grade your predictions or establish isolation for a real application.

Before RLS, Alpha reads 101 and 202 through its ordinary grant. After ENABLE without a policy it reads nothing. With the scoped policy, Alpha gets 101 and Beta gets 202. Alpha's request for tenant_beta returns an empty array; a WHERE condition narrows a query and does not select the connection identity.

Alpha's INSERT of Beta record 303 is rejected with SQLSTATE 42501. The separate administrator read confirms that 303 is absent. That read intentionally uses a superuser to inspect the complete synthetic table; it is not the application's permission check. Capture the role and statement with the error: the later forbidden SET ROLE also produces 42501, for a different operation.

The table owner initially reads both records. After FORCE, it sees none because neither row belongs to lab_owner. The maintenance BYPASSRLS role still reads both. FORCE is therefore not sufficient evidence that every configured runtime is subject to the intended boundary.

The extra SELECT policy is:

CREATE POLICY extra_read ON lab.documents
FOR SELECT TO tenant_alpha USING (true);

Permissive policies combine with OR. Alpha now reads both records despite the original tenant policy still existing; Beta still reads 202. After the extra policy is dropped, Alpha returns to 101. Do not infer safety from the presence or name of one policy. Review the applicable policy set and role memberships.

Alpha's attempt to become Beta is refused. The separately recorded fresh connection still reports session_user and current_user as tenant_alpha. The example checks a role boundary; it does not test a password, SSO token, connection pool or a security-definer function.

Keep original.json. Predict the changed requested-tenant case, then run:

python3 fde-postgres-row-security.py --requested-tenant tenant_alpha > changed.json

Only that case's requested filter changes: its Alpha connection now returns 101. Its role and scoped policy did not become more powerful. Compare the actual SQL, returned rows and the rest of the case sequence. Two matching whole-report files are not expected because runtime and image metadata may vary between machines.

For an advanced transfer, copy the runner and change only extra_read from TO tenant_alpha to TO tenant_beta. Predict both reads in permissive-or, run the changed copy and retain its exact code beside the output. Alpha should remain scoped while Beta's new applicable permissive policy broadens its read. The receiver must inspect those actual results rather than inherit your conclusion.

A connection pool lends an existing database connection to another request. A physical PostgreSQL session can therefore outlive the application request that first used it. The ordinary database role may stay app_runtime while successive requests belong to different workspaces. Request scope must not be inferred from whatever the connection happens to remember.

The optional 30-minute session workshop uses the same approved local runtime as the first exercise. It holds one actual PostgreSQL connection open through one psql process and labels six sequences of logical requests. It does not instantiate a driver pool or authenticate a person. Each read observes the real backend PID, session/effective role, current context and returned rows. The request labels and intended tenants are separately supplied teaching fixtures.

This fixture uses a different row policy, under an ordinary non-owner role:

CREATE POLICY request_scope ON lab.documents FOR SELECT
USING (tenant_id = nullif(current_setting('app.tenant', true), ''));

The custom setting app.tenant is just a string belonging to the database session. A missing or empty value matches no row in this fixture. It is not a verified tenant credential. An ordinary runtime that can execute the setting statement can change it. Trusted application code must first establish the caller and permitted workspace; binding an untrusted tenant as a SQL parameter does not make it authorized.

Predict these six sequences before reading their observations:

Download the current runner again if you retained the earlier role-only version. Keep the original role reports, then run the two optional modes into separate files:

python3 fde-postgres-row-security.py --session-reuse > session-clean.json
python3 fde-postgres-row-security.py --session-reuse --baseline-tenant tenant_alpha > session-dirty.json

Both runs use a fresh isolated container. In each run, same_backend_connection is calculated from the fifteen observed backend_pid values. steps retains the supplied request tenant, actual setup commands and observation query; observations contains database results. sql_script is the complete sequence sent to that one connection. Keep diagnostic, actual runtime attributes and container cleanup with the reports. No real pool's checkout, cancellation, release or broken-connection handling has been tested.

A session-level Alpha setting survives into sticky-reused, whose supplied request label is Beta: the read still returns 101. Clearing the context makes sticky-cleared return no rows. A successful query is not evidence that the right request scope was established.

With the empty baseline in session-clean.json, the local Beta read returns 202; both after-commit and after-rollback have null context and empty rows. In session-dirty.json, those same transaction ends restore the earlier session-level Alpha value and return 101. SET LOCAL limits an override; it does not erase a dirty baseline. Compare these exact fields rather than whole files, since backend and container identities differ between runs.

outside-local-ignored follows SET LOCAL without an explicit transaction block. PostgreSQL warns and leaves no context for that subsequent read. Inspect the warning as well as the successful process exit. BEGIN, scope setup, queries and the transaction end must use the same leased connection in an actual driver; sending those operations independently through a pool is not that guarantee.

caller-chooses-beta returns 202 although its separately supplied request fixture permits Alpha. The runtime is neither owner nor BYPASSRLS, and FORCE is enabled. The policy follows the mutable custom value; it cannot authenticate who selected it. This is a deliberate boundary failure, not an example of secure caller verification. A real design must establish trusted scope before this SQL path and address arbitrary SQL access or a compromised runtime in its threat model. ROLLBACK afterward cannot recall content already read.

The final sequence starts from a known empty baseline, runs explicit local Alpha and Beta transactions, and observes empty rows after each end. It establishes those supplied operations on one backend. To receive an enterprise implementation, have a partner check an ordinary request, a failed request and the next caller through the actual driver and pooler. The application owner owns context authority; the pool owner owns connection lifecycle and handling of uncertain state. The pod lead coordinates evidence and the acceptance owner without inheriting database permissions. Record each participant's action and assistance in the worksheet below.

Work separately before comparing. The implementing engineer supplies the exact runner, image identity, role attributes and two reports. A receiving engineer chooses one role and attempts an ordinary and a denied operation. The database owner reviews actual runtime privileges, ownership and all applicable policies in the intended environment. The pod lead coordinates the missing checks and decision owner; the role does not grant database administration or acceptance.

Keep your record locally without credentials or real client data:

Case / exact runner revision / image ID:
My first predicted rows or SQLSTATE:
Actual statement, connection role and observed result:
Original versus changed request or policy:
Receiving action actually attempted / help received:
Unresolved access path (pool, view, function, export, cache):
Bounded receiving decision and supporting evidence:
Next action / authorized owner:
My individual contribution and remaining practice:

In an enterprise service, test the actual pooled identity, every access path, function privileges, source revocation and receiving contract. This exercise establishes its supplied PostgreSQL observations. It does not qualify a learner for unsupervised access or establish a healthcare or financial compliance posture.

Explain: Why can the same SELECT grant produce two, zero or one returned row as the policy state changes? Name the separate role, operation and row checks.

Debug: A tenant predicate exists but a worker reads both organizations. Which runtime attributes, table owner and additional policy would you inspect first? Use the owner-bypass, force-and-bypass and permissive-or cases as distinct leads.

Design: The application shares one database login across requests. What must establish the tenant context, how long may it remain attached, and how would the receiver test a connection reused by the next tenant? State the untested boundary instead of claiming this per-role example already proves it.

Primary PostgreSQL 17 documentation checked 27 October 2026: Row security policies, SET ROLE, SET and SET LOCAL, and configuration setting functions. The lesson and synthetic exercise are original instruction, not a reproduction of a vendor course.