== 1. Two layers: the EF query filter and the policy filtered read as tenant 1: 3 rows, tenants seen: 01 IgnoreQueryFilters() as app_user: 20,000 rows (table has 1,000,000) == 2. Who bypasses the policy app_owner, ENABLE only: 1,000,000 rows app_owner, FORCE: 20,000 rows postgres (superuser): 1,000,000 rows == 3. Writes: attach a stub for another tenant's invoice tracked UPDATE: DbUpdateConcurrencyException: The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded. ExecuteUpdate by id only: 0 rows affected stored status of that invoice: Paid == 4. Writes: insert a row for another tenant INSERT: 42501 new row violates row-level security policy for table "invoices" == 5. No tenant resolved current_setting in the session: '' filtered read: 0 rows, IgnoreQueryFilters(): 0 rows == 6. SET at the start of the request, then let EF open and close connections SET, then a query on a pooled connection: 0 rows SET on a connection EF keeps open: 20,000 rows == 7. What the next request sees on the same physical connection default (reset on close) next request, no SET: setting = '', 0 rows visible No Reset On Close=true next request, no SET: setting = '00000000-0000-0000-0000-000000000001', 20,000 rows visible with the interceptor next request as tenant 2: setting = '00000000-0000-0000-0000-000000000002', 20,000 rows visible == 8. Plans: the policy is a predicate like any other SELECT id, number, amount FROM invoices ORDER BY created_at DESC LIMIT 20 --- Limit (actual time=0.008..0.016 rows=20.00 loops=1) Buffers: shared hit=23 -> Index Scan using ix_invoices_tenant_created on invoices (actual time=0.008..0.015 rows=20.00 loops=1) Index Cond: (tenant_id = (NULLIF(current_setting('app.tenant_id'::text, true), ''::text))::uuid) Index Searches: 1 Buffers: shared hit=23 Planning Time: 0.042 ms Execution Time: 0.029 ms SELECT id, number, amount FROM invoices WHERE tenant_id = '00000000-0000-0000-0000-000000000001' ORDER BY created_at DESC LIMIT 20 --- Limit (actual time=0.008..0.016 rows=20.00 loops=1) Buffers: shared hit=23 -> Result (actual time=0.008..0.014 rows=20.00 loops=1) One-Time Filter: ((NULLIF(current_setting('app.tenant_id'::text, true), ''::text))::uuid = '00000000-0000-0000-0000-000000000001'::uuid) Buffers: shared hit=23 -> Index Scan using ix_invoices_tenant_created on invoices (actual time=0.006..0.011 rows=20.00 loops=1) Index Cond: (tenant_id = '00000000-0000-0000-0000-000000000001'::uuid) Index Searches: 1 Buffers: shared hit=23 Planning Time: 0.038 ms Execution Time: 0.024 ms == 9. Plans: a predicate the planner cannot push below the policy SELECT id, number, amount FROM invoices WHERE number LIKE 'INV-00000%' --- Bitmap Heap Scan on invoices (actual time=1.668..5.757 rows=9.00 loops=1) Recheck Cond: (tenant_id = (NULLIF(current_setting('app.tenant_id'::text, true), ''::text))::uuid) Filter: (number ~~ 'INV-00000%'::text) Rows Removed by Filter: 19991 Heap Blocks: exact=11647 Buffers: shared hit=11748 -> Bitmap Index Scan on ix_invoices_tenant_created (actual time=0.859..0.860 rows=20000.00 loops=1) Index Cond: (tenant_id = (NULLIF(current_setting('app.tenant_id'::text, true), ''::text))::uuid) Index Searches: 1 Buffers: shared hit=101 Planning Time: 0.062 ms Execution Time: 5.768 ms -- same query with row-level security disabled -- SELECT id, number, amount FROM invoices WHERE number LIKE 'INV-00000%' AND tenant_id = '00000000-0000-0000-0000-000000000001' --- Bitmap Heap Scan on invoices (actual time=0.646..0.648 rows=9.00 loops=1) Recheck Cond: (tenant_id = '00000000-0000-0000-0000-000000000001'::uuid) Filter: (number ~~ 'INV-00000%'::text) Heap Blocks: exact=5 Buffers: shared hit=109 -> BitmapAnd (actual time=0.641..0.641 rows=0.00 loops=1) Buffers: shared hit=104 -> Bitmap Index Scan on ix_invoices_number (actual time=0.008..0.008 rows=450.00 loops=1) Index Cond: ((number ~>=~ 'INV-00000'::text) AND (number ~<~ 'INV-00001'::text)) Index Searches: 1 Buffers: shared hit=3 -> Bitmap Index Scan on ix_invoices_tenant_created (actual time=0.632..0.632 rows=20000.00 loops=1) Index Cond: (tenant_id = '00000000-0000-0000-0000-000000000001'::uuid) Index Searches: 1 Buffers: shared hit=101 Planning Time: 0.049 ms Execution Time: 0.657 ms == 10. A composite index does not rescue LIKE, a leakproof operator does starts_with leakproof=true, textlike leakproof=false -- LIKE again, now with an index on (tenant_id, number text_pattern_ops) -- SELECT id, number, amount FROM invoices WHERE number LIKE 'INV-00000%' --- Bitmap Heap Scan on invoices (actual time=1.482..5.490 rows=9.00 loops=1) Recheck Cond: (tenant_id = (NULLIF(current_setting('app.tenant_id'::text, true), ''::text))::uuid) Filter: (number ~~ 'INV-00000%'::text) Rows Removed by Filter: 19991 Heap Blocks: exact=11647 Buffers: shared hit=11748 -> Bitmap Index Scan on ix_invoices_tenant_created (actual time=0.654..0.654 rows=20000.00 loops=1) Index Cond: (tenant_id = (NULLIF(current_setting('app.tenant_id'::text, true), ''::text))::uuid) Index Searches: 1 Buffers: shared hit=101 Planning Time: 0.072 ms Execution Time: 5.502 ms -- the same prefix through the leakproof ^@ operator -- SELECT id, number, amount FROM invoices WHERE number ^@ 'INV-00000' --- Bitmap Heap Scan on invoices (actual time=0.011..0.013 rows=9.00 loops=1) Recheck Cond: (tenant_id = (NULLIF(current_setting('app.tenant_id'::text, true), ''::text))::uuid) Filter: (number ^@ 'INV-00000'::text) Heap Blocks: exact=5 Buffers: shared hit=8 -> Bitmap Index Scan on ix_invoices_tenant_number (actual time=0.007..0.007 rows=9.00 loops=1) Index Cond: ((tenant_id = (NULLIF(current_setting('app.tenant_id'::text, true), ''::text))::uuid) AND (number ~>=~ 'INV-00000'::text) AND (number ~<~ 'INV-00001'::text)) Index Searches: 1 Buffers: shared hit=3 Planning Time: 0.065 ms Execution Time: 0.021 ms -- what EF Core generates for StartsWith -- WHERE i.tenant_id = @ef_filter__TenantId AND i.number LIKE 'INV-00000%' rows: 9 == 11. What FORCE costs the owner during maintenance SET row_security = off (pg_dump default) : 42501 query would be affected by row-level security policy for table "invoices" migration backfill, no tenant set : 0 rows COPY FROM (Npgsql binary import) : 0A000 COPY FROM not supported with row-level security COPY TO with the policy on (dump) : 0 rows exported of 1,000,000 == 12. Cost of set_config on every open 500 requests, interceptor on : 1.293 ms per request 500 requests, interceptor off: 0.844 ms per request 500 requests, interceptor on : 1.214 ms per request 500 requests, interceptor off: 0.817 ms per request