# PostgreSQL row security: test the role that actually reads

A query can be parameterized and still return another organization's records.
Application scope and database enforcement are different layers. In this
45-minute lesson, run actual PostgreSQL 17 queries under four fictional roles.
Observe ordinary grants, default denial, policy checks, owner and maintenance
bypass, and an additional policy that unexpectedly broadens a read.

You need basic SELECT/INSERT knowledge, Python 3.11+ and an approved local Docker
runtime. Start with [Identity 101](/training/guides/identity) if authentication,
authorization or tenant scope is unfamiliar. The workshop uses invented procedure
rows. It does not connect to an application, cloud project or existing database.

## 1. Separate the connection, grant and row policy

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:

| ID | tenant_id | Title |
| --- | --- | --- |
| 101 | tenant_alpha | Alpha procedure |
| 202 | tenant_beta | Beta procedure |

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.

## 2. Follow the policy before assuming isolation

The scoped policy is deliberately small:

```sql
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.

## 3. Prepare the isolated local runtime

Download [the original Python runner](/templates/fde-postgres-row-security.py).
Use your normal approved process to install Docker and cache a PostgreSQL 17
image. If allowed in your environment, the cache command is:

```sh
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.

## 4. Predict ten cases and run the SQL

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

| Case | Question to predict |
| --- | --- |
| ordinary-grant | What does tenant_alpha see before RLS exists? |
| default-deny | What changes after ENABLE, before a policy exists? |
| scoped-roles | Which rows can Alpha and Beta independently read? |
| requested-tenant | Can Alpha's requested Beta filter change its database role? |
| write-check | Can Alpha insert record 303 owned by Beta? |
| owner-bypass | What does the table owner see before FORCE? |
| force-and-bypass | What do the owner and BYPASSRLS role see after FORCE? |
| permissive-or | What happens when Alpha gets an extra permissive SELECT policy? |
| policy-restored | What does Alpha see after that extra policy is removed? |
| role-switch-denied | Can Alpha SET ROLE to Beta without membership? |

```sh
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.

## 5. Interpret the default run

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:

```sql
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.

## 6. Change the request without changing identity

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

```sh
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.

## 6a. Reuse a connection without reusing its authority

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:

```sql
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:

| Sequence | Question to predict |
| --- | --- |
| sticky-session | Alpha sets a session value; a later Beta-labelled request omits setup. Which row is read? |
| commit-restoration | A local Beta value ends with COMMIT. What session value comes back? |
| rollback-restoration | Does ROLLBACK restore a different baseline from COMMIT? |
| outside-transaction | Does SET LOCAL without BEGIN establish scope for the next query? |
| caller-chosen-context | The supplied request fixture permits Alpha but the SQL chooses Beta. Does the policy verify the fixture? |
| clean-request-boundaries | With a known empty baseline and explicit local scope, what can each request and the after-end read see? |

## 6b. Run and change the session baseline

Download the [current runner](/templates/fde-postgres-row-security.py) again if
you retained the earlier role-only version. Keep the original role reports,
then run the two optional modes into separate files:

```sh
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.

## 7. Give the receiving team a specific check

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:

```text
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.

## 8. Explain the mechanism without the report

**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.

## Sources

Primary PostgreSQL 17 documentation checked 27 October 2026:
[Row security policies](https://www.postgresql.org/docs/17/ddl-rowsecurity.html), [SET ROLE](https://www.postgresql.org/docs/17/sql-set-role.html),
[SET and SET LOCAL](https://www.postgresql.org/docs/17/sql-set.html), and
[configuration setting functions](https://www.postgresql.org/docs/17/functions-admin.html).
The lesson and synthetic exercise are original instruction, not a reproduction
of a vendor course.