# Postgres row-level security with EF Core

A PostgreSQL 18 row-level security policy on an `invoices` table with 1,000,000 rows, and a .NET 10 console app that runs twelve scenarios against it. The scenarios show what the policy catches, where the tenant setting gets lost on pooled connections, and what the policy costs in query plans, maintenance work, and per-request time.

## Prerequisites

The .NET 10 SDK (`global.json` pins 10.0.301) and Docker with Compose v2.

## Run it

```sh
docker compose up -d
dotnet run -- setup
dotnet run
```

`setup` connects as the `postgres` superuser and creates the `app_owner` and `app_user` roles, the `tenants` and `invoices` tables, 50 tenants with 20,000 invoices each, and the `tenant_isolation` policy. It takes a few seconds. `dotnet run` then runs the ten scenarios and prints what you see in `output.txt`. Row counts and plan shapes should match, timings will not.

The container listens on `127.0.0.1:5433`, so it does not conflict with a PostgreSQL instance on the default port.

## Scenarios

1. Two layers: the EF query filter and the policy. `IgnoreQueryFilters()` as `app_user` still sees only the current tenant's 20,000 rows.
2. Who bypasses the policy. The table owner sees every row until `FORCE ROW LEVEL SECURITY` is on, and the superuser always does.
3. Writes: attach a stub for another tenant's invoice. The tracked `UPDATE` and an `ExecuteUpdate` by id both affect zero rows.
4. Writes: insert a row for another tenant. The `INSERT` fails with `42501` because of the `WITH CHECK` clause.
5. No tenant resolved. An empty setting matches nothing, so every query returns zero rows.
6. `SET` at the start of the request, then let EF open and close connections. The setting only survives on a connection EF keeps open.
7. What the next request sees on the same physical connection. The default connection reset clears the setting, `No Reset On Close=true` leaks the previous tenant, and the interceptor overwrites it on every open.
8. Plans: the policy is a predicate like any other. The policy uses the `(tenant_id, created_at)` index with the same buffer count as an explicit `WHERE tenant_id = ...`.
9. Plans: a predicate the planner cannot push below the policy. `LIKE` is not leakproof, so it runs after the policy scan over 20,000 rows instead of combining two indexes.
10. A composite index does not rescue `LIKE`, a leakproof operator does. Adding `(tenant_id, number text_pattern_ops)` changes nothing, while the same prefix through `^@` puts both columns in the `Index Cond`. EF Core translates `StartsWith` to `LIKE`.
11. What `FORCE` costs the owner during maintenance. `pg_dump`'s default fails, a dump with the policy on exports zero rows, a backfill reports zero rows changed, and `COPY FROM` is rejected.
12. Cost of `set_config` on every open. The interceptor adds about half a millisecond per request in `output.txt`.

## Notes

The role names and passwords in `setup.sql` and `Program.cs` are demo values. The superuser connection runs `setup`, and a few scenarios use it to toggle the policy or to read a row for comparison. Everything that stands in for application code connects as `app_user`.