itechmeat/llm-code/postgresql

PostgreSQL

RLS Multi-tenancy Pattern

Non-negotiables

• RLS context is mandatory for any tenant-scoped query
• Context must be set inside the same transaction as the queries
• No fallbacks for tenant ID (fail fast if missing)
• Async-only DB access when using async frameworks

Setting RLS Context

RLS works only if the current transaction has the context set:

SET LOCAL app.current_tenant_id = '<tenant_uuid>';

Must run before the first tenant-scoped query in that transaction.

Common Failure Modes

• Setting SET LOCAL ... after the first select()
• Setting the context in one session, then querying in another
• Running queries outside the expected transaction scope

Typical RLS Policy

ALTER TABLE some_table ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY some_table_tenant_isolation
ON some_table
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

Multi-tenant Table Checklist

• Tenant ID column is UUID
• FK to tenants table with ON DELETE CASCADE
• Indexes aligned with access patterns (usually tenant_id first)
• PostgreSQL does not auto-index FK columns — add explicit indexes
• UNIQUE allows multiple NULLs unless using NULLS NOT DISTINCT (PG15+)
• RLS is enabled and policies exist
• Application code sets RLS context at transaction start

Alembic Migrations Checklist

1. Add/modify schema (columns, constraints, FKs)
2. Create/update indexes
3. Enable RLS and create/adjust policies
4. Add verification (tests) for isolation
5. Provide a real downgrade (no stubs)

Version Notes

PostgreSQL 19 (RC 1)

• 19 is at RC 1 (2026-10-15): the feature set is frozen and matches the upcoming general availability, planned for 2026-11-19. RC 2 ships on 2026-11-12 together with the November update release, so that release's fixes land in 19 before GA. Latest stable line is 18.6.
• Headline changes: REPACK / REPACK CONCURRENTLY replacing VACUUM FULL and CLUSTER, parallel autovacuum with a scoring system, logical replication of sequences, SQL/PGQ property graphs, GROUP BY ALL, FOR PORTION OF, and online checksum enable/disable.
• 18 incompatible changes to plan for, including forced standard_conforming_strings, RADIUS removal, jit off by default, default_toast_compression switching to lz4, and max_locks_per_transaction defaulting to 128 with changed sizing.
• Full detail and the upgrade checklist: postgresql-19.md

PostgreSQL 18.4

• 18.4 is a security/robustness patch release; no dump/restore is required for existing 18.x clusters.
• The patch line hardens startup packet parsing, backup tools (pg_basebackup, pg_rewind, pg_verifybackup), and several logical replication code paths.
• Planner/executor fixes also land for MERGE, nondeterministic collations, generated columns, and assorted aggregate/window edge cases.

RLS Isolation Testing Recipe

Goal:

• Data for tenant A is visible to tenant A
• Data for tenant A is NOT visible to tenant B

Canonical flow:

1. Setup data through an admin session (RLS bypass) for tenant A and B
2. Assert via an RLS session:

• set context to tenant A → sees only tenant A data
• set context to tenant B → does not see tenant A data

Destructive Operations Safety

Hard rules:

• Never run DELETE without a narrow WHERE targeting specific data
• Never run TRUNCATE/DROP without explicit confirmation

Pre-flight before destructive actions:

1. Confirm exact target (tables / IDs / date range)
2. Run a SELECT/row count first and show results
3. Ask for final confirmation, then execute

References

Versions

• postgresql-19.md — PostgreSQL 19 (beta): new features by area, full incompatible-changes list, upgrade checklist

Schema & Design

• table-design.md — Data types, constraints, indexing, partitioning, JSONB, safe schema evolution
• charset-encoding.md — Character sets, encoding, collation, ICU, locale settings

Authentication

• authentication.md — pg_hba.conf, SCRAM-SHA-256, md5, peer, cert, LDAP, GSSAPI
• authentication-oauth.md — OAuth 2.0 (PostgreSQL 18+), SASL OAUTHBEARER, validators
• user-management.md — CREATE/ALTER/DROP ROLE, membership, GRANT/REVOKE, predefined roles

Runtime Configuration

• connection-settings.md — listen_addresses, max_connections, SSL, TCP keepalives
• query-tuning.md — Planner settings, work_mem, parallel query, cost constants
• replication.md — Streaming replication, WAL, synchronous commit, logical replication
• vacuum.md — Autovacuum, vacuum cost model, freeze ages, per-table tuning
• error-handling.md — exit_on_error, restart_after_crash, data_sync_retry

Internals

• internals.md — Query processing pipeline, parser/rewriter/planner/executor, system catalogs, wire protocol, access methods
• protocol.md — Wire protocol v3.2: message format, startup, auth, query, COPY, replication

Links

• Documentation
• Releases
• GitHub

See Also

• sql-expert — Query patterns, EXPLAIN workflow, optimization