subscription_check_filters accepts filter operators apply_rls cannot evaluate, and refuses some it can

[hamodywe]

Describe the bug

realtime.subscription_check_filters decides whether a filter's operator can be applied to a column type by looking the operator up in pg_catalog.pg_operator. That lookup disagrees with what check_equality_op actually does at WAL time, in both directions.

Accepted at subscribe time, raises at WAL time. The comparison operators are not checked against the column type at all — the else branch only checks that the value casts:

else
    -- eq/neq/lt/lte/gt/gte: value must be coercable to the type
    perform realtime.cast(filter.value, col_type);
end if;

So body=eq.1 on a json column registers happily, and apply_rls then raises operator does not exist: json = json. is_visible_through_filters runs in apply_rls' subscription loop, so this aborts the whole list_changes call: every subscription on the tenant loses the batch, including subscriptions on other tables with no filters at all, and the WAL is not replayed. One client is enough to stop postgres_changes for a whole project.

ilike on a bytea column has the same effect for a different reason: the guard checks oprname = '~~' for both like and ilike, and bytea has ~~ but no ~~*.

Refused at subscribe time although it works. varchar carries no ~~/~/~* entry of its own — it resolves them through text — so the oprleft = col_type test fails and like, ilike, match, imatch are rejected on varchar columns that handle them perfectly well.

To Reproduce

On supabase/postgres:17.6.1.127 with the tenant migrations applied:

create table public.docs (id int primary key, body json);
create table public.notes (id int primary key, details text);
grant all on table public.docs, public.notes to anon;
create publication supabase_realtime_test for all tables;

-- one filtered subscription on the json column, two ordinary ones
insert into realtime.subscription (subscription_id, entity, claims, filters) values
  ('11111111-1111-1111-1111-111111111111', 'public.docs'::regclass, '{"role":"anon"}',
   array[row('body', 'eq', '{"a":1}', false)::realtime.user_defined_filter]),   -- accepted
  ('22222222-2222-2222-2222-222222222222', 'public.docs'::regclass, '{"role":"anon"}', '{}'),
  ('33333333-3333-3333-3333-333333333333', 'public.notes'::regclass, '{"role":"anon"}', '{}');

select pg_create_logical_replication_slot('blast_slot', 'wal2json');
insert into public.docs  (id, body) values (1, '{"a":1}');
insert into public.notes (id, details) values (1, 'unrelated table');

select subscription_ids, errors from realtime.list_changes('supabase_realtime_test', 'blast_slot', 100, 1048576);

ERROR:  operator does not exist: json = json
QUERY:  select '{"a": 1}'::json = ('{"a":1}'::json)
CONTEXT:  PL/pgSQL function realtime.check_equality_op(realtime.equality_op,regtype,text,text,boolean) line 53 at EXECUTE
          SQL function "is_visible_through_filters" statement 1
          PL/pgSQL function realtime.apply_rls(jsonb,integer) line 191 at FOR over SELECT rows
          SQL function "list_changes" statement 1

Nobody receives anything — not the unfiltered subscription on docs, and not the one on notes.

Full sweep

I ran every operator against a table with 17 column types, comparing what subscription_check_filters accepts against what check_equality_op can evaluate:

Column type	Operators accepted that raise at WAL time
json	eq neq lt lte gt gte isdistinct
xml	eq neq lt lte gt gte isdistinct
point	eq lt lte gt gte isdistinct
bytea	ilike

Column type	Operators refused although they evaluate fine
character varying	like ilike match imatch

21 accepted-but-raises, 4 refused-but-works.

Expected behavior

A filter that registers should be evaluable, and a filter that is evaluable should register. Anything that can raise inside apply_rls needs to be caught at subscribe time, where it can be reported to the one client responsible instead of taking down the tenant's stream — which is the reason the is and match guards were added in the first place.

System information

• supabase/postgres:17.6.1.127, tenant migrations through 20260709120000
• Reproduced against realtime.list_changes directly

I have a fix ready and will open a PR against this issue.