Row-Level Security

KalamDB applies FORCE row-level security to every SHARED table. You grant visible and writable rows with PostgreSQL-shaped CREATE POLICY statements. There is no ACCESS_LEVEL table option.

This page is the SQL reference. For compile, bind, MVCC ordering, and live keyed routing, see /docs/server/architecture/row-level-security.

Row-level security applies to SQL scans, DML, live subscriptions, and FILE downloads for user and service sessions. system and dba bypass RLS. Anonymous sessions cannot open shared tables.

Complete KalamDB RLS example

As an administrator, create a shared notes table and one owner policy:

SQL

1CREATE SCHEMA IF NOT EXISTS app;2CREATE TABLE app.shared_notes (3  id BIGSERIAL PRIMARY KEY,4  owner_id TEXT NOT NULL DEFAULT CURRENT_USER,5  body TEXT NOT NULL6) WITH (TYPE = 'SHARED');7 8CREATE POLICY notes_owner ON app.shared_notes9  FOR ALL TO user10  USING (owner_id = CURRENT_USER)11  WITH CHECK (owner_id = CURRENT_USER);

Connect as a normal user created through authentication setup, then run:

SQL

1INSERT INTO app.shared_notes (body) VALUES ('Only I can read this note')2RETURNING id, owner_id, body;3 4SELECT * FROM app.shared_notes;

Connect as a second normal user and run the same SELECT: the first user’s note must not appear. An attempt to insert a different owner_id must fail. Test with normal accounts because dba and system bypass RLS. CURRENT_USER is the effective KalamDB identity, including the mapped OIDC subject for external sign-in.

KalamDB enforces RLS on SHARED tables without an extra ALTER TABLE ... ENABLE ROW LEVEL SECURITY step. USING checks existing rows; WITH CHECK checks new row values. The same access model applies to live subscriptions, so the frontend does not need a second policy system.

Default Deny

A shared table with no matching policy returns zero rows on SELECT and rejects writes for user and service. Create at least one policy before those roles can see or mutate data.

USER and STREAM tables stay partitioned by the effective user_id. They do not use CREATE POLICY. See /docs/server/architecture/table-types.

CREATE POLICY

SQL

1CREATE POLICY <name> ON [<namespace>.]<table>2  [AS PERMISSIVE]3  FOR { ALL | SELECT | INSERT | UPDATE | DELETE }4  TO { PUBLIC | user | service | user, service }5  [USING (<boolean_expr>)]6  [WITH CHECK (<boolean_expr>)];

AS RESTRICTIVE is rejected. Policies are always permissive: if any applicable policy allows the row, the row is allowed.

TO selects which roles the policy applies to:

Target	Who it applies to
`TO user`	End-user sessions
`TO service`	Service-account sessions
`TO user, service`	Both authenticated principals
`TO PUBLIC` (or omit `TO`)	Every role subject to RLS (`user` and `service`)

Named principals such as TO alice are not supported. Bind identity in the expression with CURRENT_USER (or CURRENT_USER()).

Policy DDL (CREATE / ALTER / DROP POLICY) requires system, dba, or service. Regular user sessions cannot change policies.

USING vs WITH CHECK

Command	`USING`	`WITH CHECK`
`SELECT`	Required. Filters visible rows.	Not allowed.
`INSERT`	Not allowed.	Required. Must hold for the new row.
`UPDATE`	Existing row must pass.	New row must pass.
`DELETE`	Existing row must pass.	Not allowed.
`ALL`	Existing-row check.	New-row check for insert/update.

Client WHERE clauses, including OR true, cannot bypass RLS. Authorized MVCC winners are selected first, then the query filter runs.

Examples

SQL

1-- End users see only their own documents2CREATE POLICY owner_read ON app.documents3  FOR SELECT TO user4  USING (owner_id = CURRENT_USER);5 6-- Membership subquery (same as EXISTS)7CREATE POLICY member_read ON app.messages8  FOR SELECT TO user9  USING (10    conversation_id IN (11      SELECT conversation_id FROM app.conversation_members12      WHERE user_id = CURRENT_USER13    )14  );15 16-- Service accounts can read published rows17CREATE POLICY service_published_read ON app.documents18  FOR SELECT TO service19  USING (status = 'published');20 21-- Both user and service share one visibility rule22CREATE POLICY tenant_read ON app.events23  FOR SELECT TO user, service24  USING (tenant_id = CURRENT_USER);25 26CREATE POLICY owner_insert ON app.documents27  FOR INSERT TO user28  WITH CHECK (owner_id = CURRENT_USER);29 30CREATE POLICY owner_update ON app.documents31  FOR UPDATE TO user32  USING (owner_id = CURRENT_USER)33  WITH CHECK (owner_id = CURRENT_USER);34 35CREATE POLICY owner_delete ON app.documents36  FOR DELETE TO user37  USING (owner_id = CURRENT_USER);38 39CREATE POLICY service_full ON app.documents40  FOR ALL TO service41  USING (true)42  WITH CHECK (true);

The IN (SELECT …) form and a correlated EXISTS compile to the same membership relation:

SQL

1CREATE POLICY member_read_exists ON app.messages2  FOR SELECT TO user3  USING (4    EXISTS (5      SELECT 16      FROM app.conversation_members m7      WHERE m.conversation_id = messages.conversation_id8        AND m.user_id = CURRENT_USER9    )10  );

ALTER POLICY and DROP POLICY

SQL

1ALTER POLICY owner_read ON app.documents2  USING (owner_id = CURRENT_USER);3 4ALTER POLICY owner_read ON app.documents5  RENAME TO document_owner_read;6 7DROP POLICY owner_read ON app.documents;8DROP POLICY IF EXISTS owner_read ON app.documents;

DROP POLICY CASCADE is not supported.

Supported USING expressions

KalamDB compiles policy SQL into bounded authorization semantics. These shapes are supported:

Shape	Example	Live routing
Column equals current user	`owner_id = CURRENT_USER`	Keyed on that column and principal
Column equals a literal	`visibility = 'public'`	Keyed on that literal
Membership `IN` subquery	`conversation_id IN (SELECT … WHERE user_id = CURRENT_USER)`	Keyed on each membership value
Correlated `EXISTS`	`EXISTS (SELECT 1 FROM members …)`	Same keys as the equivalent `IN`
Allow all	`true`	Every change on the table is a candidate
Deny all	`false`	No live candidates

Membership subqueries may add static predicates on the relation, for example AND role = 'member'. The live index is still the protected key (conversation_id in the examples above).

Give the members table a covering primary key on (principal, relation_key) so lookups can probe the index instead of scanning the relation. When the principal is not the leading PK column, add a scalar CREATE INDEX on user_id (and on the fact table’s conversation_id) — see Indexes:

SQL

1CREATE TABLE app.conversation_members (2  user_id TEXT,3  conversation_id TEXT,4  role TEXT NOT NULL,5  PRIMARY KEY (user_id, conversation_id)6) WITH (TYPE = 'SHARED');

Rejected policy shapes

These fail at CREATE POLICY / ALTER POLICY because they cannot produce a bounded live route:

Row-local NOT, AND, or OR such as NOT (owner_id = CURRENT_USER) or owner_id = CURRENT_USER OR is_public = true
Negated IN / EXISTS
Aggregates in the membership subquery (max(conversation_id))
Membership subqueries that do not restrict CURRENT_USER
Per-user TO <user_id> lists

Keep extra predicates in the client WHERE clause, not in the policy, when they are not a bounded equality or membership key.

Live subscriptions

Shared-table live queries bind RLS once at subscribe time. Each change looks up subscribers by the policy keys (for example conversation_id) instead of evaluating every subscriber on the table.

The bound policy is still the final check. If a grant, revoke, or membership change races an in-flight event, delivery fail-closes: the row is not leaked. The client should resubscribe after a fail-closed gap.

USING (true) broadcasts every table change to those subscribers. Prefer keyed owner or membership policies when many users share one table.

Existing live subscriptions do not pick up a newly created policy until the client resubscribes.

See /docs/server/architecture/row-level-security, /docs/server/architecture/live-query, and /docs/server/sql-reference/subscriptions.

Writes and upserts

INSERT, UPDATE, and DELETE use the same compiled policies. user and service ON CONFLICT DO UPDATE on shared tables is rejected so upsert cannot skip USING / WITH CHECK. Use a plain INSERT or UPDATE instead.

EXECUTE AS does not change shared-table RLS. The acting role and CURRENT_USER still come from the session that opened the statement. See /docs/server/sql-reference/impersonation.