Live Queries: RLS context + subscription execution model

[ox-harris]

Context

We’ve identified a core tension between:

• RLS implemented via session/connection state (e.g. current_setting('claims.user_id'))
• long-lived live queries that may be multiplexed, deduped, or executed across pooled connections

This creates a mismatch where:

• RLS assumes stable connection identity
• live queries assume decoupled execution context

This can lead to:

• connection fanout under RLS-heavy workloads
• ambiguity in how identity is preserved across a live query lifecycle

---

Problem to solve

We need to define a clear and stable execution model for RLS within live queries that ensures:

• no connection-per-subscription explosion
• deterministic RLS behavior
• no cross-user leakage
• compatibility with pooled execution

---

Key question

What is the correct binding for RLS context in a live query system?

We need to decide whether RLS context should be:

Option A — Query-bound (recommended direction)

RLS is resolved at subscription creation time and becomes part of the query identity.

• RLS context is attached to the live query definition
• execution can move across pooled connections safely
• no reliance on session state

Option B — Connection-bound

RLS continues to rely on session state (current_setting / connection identity)

• requires sticky connections or session pinning
• risks pool exhaustion under long-lived subscriptions

Option C — Hybrid (explicit context propagation)

RLS context is passed explicitly per execution tick

• live query carries identity payload
• engine injects context per WAL evaluation / re-evaluation
• avoids session dependency but preserves dynamic identity

---

What we need to converge on

• where RLS context lives (session vs query vs hybrid)
• whether live queries are allowed to migrate across connections freely
• whether subscription identity includes auth context
• how we prevent connection exhaustion under RLS-heavy usage

---

Why this matters

This decision determines:

• scalability of live queries under RLS workloads
• correctness guarantees for multi-tenant systems
• connection pool behavior under load

Once this is locked, implementation is straightforward and can be shipped quickly.

---

Open question

Given RLS-first architectures, what constraints or failure modes should we assume as non-negotiable?

[hyusetiawan]

i did some shallow due dilligence, i think query based ownership, where each table will have, somehow, a query indicating access back to the user table will be unruly quick. I imagine things like posts / comments / other tables, will have either:

1. polymorphic table that maps userId -> table so that means, this table will be huge because user count x
2. each table has to indicate a foreign key back to the user, like a userId fk back to users table, this will also get unruly quick

Is there a suggested table design that i should look into to support this?

[ox-harris]

Thanks @hyusetiawan

So, this was a real constraint in the original model, and it’s worth saying now that the execution model has evolved a bit.

Where things stand now

One important update since this issue was opened:

Live queries can now execute within a transaction-scoped context. (Docs)

That means patterns like:

current_setting('claims.user_id', true)

are no longer inherently unsafe in a live-query environment — as long as they’re bound within a transaction:

const tx = await db.begin();

await db.query(
  `SELECT set_config('claims.user_id', 'user_abc_123', true)`,
  { tx }
);

const result = await db.query(
  `SELECT id, title FROM public.posts ORDER BY id`,
  { live: true, tx }
);

This gives you a stable execution context for the lifetime of that live query, so RLS behaves deterministically and without cross-user leakage.

Clarifying the execution boundary

To make this explicit:

Live query deduplication is scoped by:
SQL text + query params + execution context (tx)

So even if two subscriptions have identical SQL, they will not be deduped if they differ in transaction context (e.g. different set_config(...) values).

This is the key guarantee that keeps RLS safe under multiplexed execution.

So what’s the model now?

LinkedQL now supports two valid patterns for carrying identity into live queries:

1. Transaction-bound context (stateful)

• Identity is set via set_config(...)
• RLS works exactly as defined in the database
• Execution happens within a transaction context

This is the most natural fit for existing PostgreSQL-style RLS setups.

2. Parameterized context (stateless)

• Identity is passed explicitly as query parameters
• Queries encode their own filtering logic
• No reliance on session or transaction state

This aligns more directly with pooled, fully stateless execution.

Tradeoffs (worth calling out)

Both models are valid — but they behave differently under load.

• Transaction-bound context introduces execution affinity
  (each subscription carries its own context), which can have different scaling characteristics depending on workload.

• Stateless / parameterized queries are easier to dedupe, move across connections, and fan out efficiently.

In other words:

If you want drop-in compatibility with existing RLS → transaction-bound works well.
If you’re optimizing for high fanout and pooling efficiency → stateless patterns scale more naturally.

So to address the original concern now

The original issue called out the mismatch between:

• session-bound RLS
• and pooled/live-query execution

That mismatch is now addressed:

• RLS can be safely bound to a transaction-scoped execution context
• live queries no longer depend on unstable session state
• and deduplication respects execution context boundaries

Now, there isn’t a single “correct” binding anymore — and that’s intentional.

LinkedQL now supports both:

• stateful (transaction-bound) identity
• stateless (parameterized) identity

…and the right choice depends on the shape of your system and the scaling characteristics you need.

[ox-harris]

@hyusetiawan to talk about the schema modelling options you gave, I think there’s a bit of overthinking happening around the there.

“polymorphic table that maps userId → table”

I wouldn’t go in that direction. That kind of “universal ownership table” tends to get messy fast — size blows up (users × resources), joins get heavier, and you lose a lot of the natural structure the relational model gives you.

“each table has to indicate a foreign key back to the user”

This part is actually normal. If a row has a clear owner, a user_id FK on that table is the simplest and most scalable thing you can do. That’s not something to avoid.

The real thing to think about is that there isn’t one single pattern for “ownership”. You typically end up with a mix of:

• Direct ownership
  e.g. posts.user_id

• Indirect ownership (through another table)
  e.g. comments.post_id → posts.user_id

• Membership / shared access
  e.g. project_members(user_id, project_id)

Trying to collapse all of that into one generic structure is where things actually become unruly.

Also worth calling out:

Instead of trying to encode every access rule into a universal table design, you can just express it directly in the query:

SELECT *
FROM tasks
WHERE project_id IN (
  SELECT project_id
  FROM project_members
  WHERE user_id = $user_id
)

That is your access model.

In LinkedQL, that query can be live, scoped per user, and evaluated safely (whether you’re using parameters or transaction-bound context like set_config(...)).

And just to connect this back to the original concern: if this line of thinking was coming from trying to work around the earlier limitations (where live queries couldn’t safely carry session/transaction context), that constraint is no longer there.

With transaction-scoped execution now supported, you don’t need to invent a special “ownership schema” just to make access control work under live queries.

Keep the schema simple, model relationships properly, and let access logic live in queries — the system now supports that cleanly.