Presence track() write-authorization probe also probes 'broadcast', logging noisy-but-benign RLS errors on Broadcast-from-Database setups

[JuanCamiloDorado]

Bug description (operational noise — functionality is correct)

On a private channel, the first presence track() of each subscription runs the
write-authorization probe, and the probe test-INSERTs one row into realtime.messages
per extension — broadcast and presence — not just the extension being exercised:

• presence_handler.ex#L85-L92 — the first track() with presence.write == nil calls get_write_authorizations/3 with no scoping opts
• authorization.ex#L296-L302 — extensions_to_check/1 returns [:broadcast, :presence] unless presence_enabled?: false
• authorization.ex#L323-L342 — check_write_policies/4 inserts one probe row per extension, each in its own savepoint; a denied insert (42501) is caught and correctly mapped to write: false

The savepoint handling is silent on the Realtime side, but Postgres has already emitted an
ERROR-level log line before the savepoint rolls back:

ERROR: new row violates row-level security policy for table "messages"

For projects on the documented Broadcast from Database
pattern, asymmetric write policies are the intended setup: clients write presence
(who's-online/cursors) but must not be able to write broadcast — server events are
emitted by a trigger via realtime.send() (which bypasses RLS), and an INSERT policy
permissive enough to let the probe through would let any authenticated client forge
server-emitted events. The result is a guaranteed, recurring RLS ERROR in the project's
Postgres logs on every first track() per subscription (editor open, reconnect, token
refresh), polluting the logs and masking real errors.

To reproduce

RLS on realtime.messages for a private topic, allowing INSERT only for presence:

create policy "demo_reads" on realtime.messages
for select to authenticated
using (
  realtime.messages.extension in ('broadcast', 'presence')
  and realtime.topic() = 'room:demo'
);

create policy "demo_presence_only_writes" on realtime.messages
for insert to authenticated
with check (
  realtime.messages.extension = 'presence'
  and realtime.topic() = 'room:demo'
);

const channel = supabase.channel('room:demo', { config: { private: true } })
channel.subscribe((status) => {
  if (status === 'SUBSCRIBED') channel.track({ user: 'me' })
})

Presence works, broadcast.write is correctly false — and the Postgres logs show the RLS
ERROR above for the broadcast probe row on every first track() of each subscription.

Expected behavior

A presence track() on a channel where broadcast writes are deliberately denied shouldn't
generate recurring ERROR-level Postgres log lines — ideally the write probe only exercises
the extension actually being used.

Suggested direction (aware of the tradeoff)

Both handlers already lazily re-probe, keyed on nil:

• broadcast_handler.ex#L170-L176 pattern-matches %BroadcastPolicies{write: nil} and re-probes on the first actual broadcast
• presence_handler.ex#L85-L92 does the same for presence on the first track()

so scoping each handler's probe to its own extension looks safe: the other extension would
simply be probed on its first actual use. There's precedent for scoping the probe in #1711
(presence_enabled?: false already skips the presence check via extensions_to_check/1).

One caveat we hit while reading the code: check_write_policies/4 currently sets extensions
that are not in the checked list to write: false rather than leaving them nil (the
else branch of the reduce). A scoped probe would need to leave unchecked extensions
untouched, otherwise the cached Policies struct would wrongly deny the other extension
outright instead of triggering its lazy re-check on first use.

We understand probing both extensions in one transaction saves a DB roundtrip for clients
that use both — but it costs a guaranteed recurring ERROR log line (plus one extra
INSERT + savepoint per probe) for every project on the recommended Broadcast-from-Database
pattern, where clients never broadcast by design. If scoping isn't acceptable, even an
opt-out would help keep production Postgres logs clean.

System information

• Observed on hosted Supabase (cloud), two production projects.
• Code references verified against main @ dae4aa3.
• Setup: private channels (doc:{uuid}), presence for co-editing awareness +
  Broadcast-from-Database via trigger → realtime.send().

Happy to provide more detail or test a patch.

[edgurgel]

Hey thanks for the issue. We are analysing options here!