Support Generic PostgREST 9 Claims

[olirice]

What kind of change does this PR introduce?

• Removes hard coded use of authenticated role
• Replaces realtime.subscription email, and role fields with generic jwt claims jsonb field

at minimum, that the claims must contain a top level key named role e.g. {"role": "authenticated"}, which is verified by a trigger to be a valid role at the time the subscription is made.

• Since multiple subscribers may have different roles, and therefore different column access, the realtime.apply_rls() function now returns setof realtime.wal_rls instead of realtime.wal_rls

• The Unauthorized exception has been expanded from being raised when a role can not access any columns on a table to being raised if any of the primary key columns can not be selected by the role

• A Bad Request error was added and is thrown when the WAL record's table has no primary key (replaces #28)

• When errors are returned, the subscription_ids are return so users can be notified as needed

[w3b6x9]

@olirice Wanted to point out these so far:

1. Making a change to a table that's under the publication but is not currently being subscribed to in realtime.subscription returns null values across the board when using the publication polling query. Not really an issue but in this scenario we should omit so it's one fewer row that needs to be processed by Realtime server.

2. I seem to be getting the following error when I've inserted two subscribers into realtime.subscription where one subscriber is anon role and the other is authenticated role and I try to get changes after inserting into a public schema table:

ERROR: query returned more than one row CONTEXT: query: unnest(result_arr) PL/pgSQL function realtime.apply_rls(jsonb,integer) line 232 at RETURN NEXT

[olirice]

Making a change to a table that's under the publication but is not currently being subscribed to in realtime.subscription returns null values across the board when using the publication polling query. Not really an issue but in this scenario we should omit so it's one fewer row that needs to be processed by Realtime server.

good call. Updated in 5373c07 w/ tests

I seem to be getting the following error when I've inserted two subscribers into realtime.subscription where one subscriber is anon role and the other is authenticated role and I try to get changes after inserting into a public schema table:

ERROR: query returned more than one row CONTEXT: query: unnest(result_arr) PL/pgSQL function realtime.apply_rls(jsonb,integer) line 232 at RETURN NEXT

yeeep, I botched that pretty hard. Added a test and resolved in 8a79ee7

[w3b6x9]

@olirice I'm having an issue where I have three subscribers in the realtime.subscription table and each has a different role (anon, authenticated, service_role) and when I listen to a table in the public schema and the table has RLS disabled then only the authenticated user gets changes while the other two get back error Error 401: Unauthorized.

Here's the key parts of my setup:

create role anon                nologin noinherit;
create role authenticated       nologin noinherit;
create role service_role        nologin noinherit bypassrls;

grant usage                     on schema public to anon, authenticated, service_role;
alter default privileges in schema public grant all on tables to anon, authenticated, service_role;
alter default privileges in schema public grant all on functions to anon, authenticated, service_role;
alter default privileges in schema public grant all on sequences to anon, authenticated, service_role;

create publication supabase_realtime for all tables;

Create a table in public schema.

Add the three different roles to realtime.subscription.

Insert a change to the public schema table.

Get back three records after publication polling query but only the authenticated subscriber gets the changes while the other two get back errors.

[olirice]

I was not able to reproduce that problem. Here's the complete setup:

create role anon                nologin noinherit;
create role authenticated       nologin noinherit;
create role service_role        nologin noinherit bypassrls;

grant usage                     on schema public to anon, authenticated, service_role;
alter default privileges in schema public grant all on tables to anon, authenticated, service_role;
alter default privileges in schema public grant all on functions to anon, authenticated, service_role;
alter default privileges in schema public grant all on sequences to anon, authenticated, service_role;

create publication supabase_realtime for all tables;

create table public.note(id int primary key);

insert into realtime.subscription(id, entity, claims)
values
    (extensions.uuid_generate_v4(), 'public.note', jsonb_build_object('role', 'authenticated')),
    (extensions.uuid_generate_v4(), 'public.note', jsonb_build_object('role', 'anon')),
    (extensions.uuid_generate_v4(), 'public.note', jsonb_build_object('role', 'service_role'));

select * from pg_create_logical_replication_slot('realtime', 'wal2json', false);

insert into public.note(id) values (1);

set search_path = '';

with pub as (
    select
        concat_ws(
            ',',
            case when bool_or(pubinsert) then 'insert' else null end,
            case when bool_or(pubupdate) then 'update' else null end,
            case when bool_or(pubdelete) then 'delete' else null end
        ) as w2j_actions,
        string_agg(realtime.quote_wal2json(format('%I.%I', schemaname, tablename)::regclass), ',') w2j_add_tables
    from
        pg_publication pp
        join pg_publication_tables ppt
            on pp.pubname = ppt.pubname
    where
        pp.pubname = 'supabase_realtime'
    group by
        pp.pubname
    limit 1
)
select
    xyz.wal,
    xyz.is_rls_enabled,
    xyz.subscription_ids,
    xyz.errors
from
    pub,
    lateral (
          select
            *
          from
             pg_logical_slot_get_changes(
                'realtime', null, null,
                'include-pk', '1',
                'include-transaction', 'false',
                'include-timestamp', 'true',
                'write-in-chunks', 'true',
                'format-version', '2',
                'actions', coalesce(pub.w2j_actions, ''),
                'add-tables', pub.w2j_add_tables
            )
    ) w2j,
    lateral (
        select
            x.wal,
            x.is_rls_enabled,
            x.subscription_ids,
            x.errors
        from
            realtime.apply_rls(
                wal := w2j.data::jsonb,
                max_record_bytes := 1048576
            ) x(wal, is_rls_enabled, subscription_ids, errors)
    ) xyz
where
    coalesce(pub.w2j_add_tables, '') <> ''
    and xyz.subscription_ids[1] is not null

could you please post a complete min repro case?

[w3b6x9]

@olirice it works as expected after following your setup.

I got errors for anon and service_role b/c I created the public schema table first and then ran:

alter default privileges in schema public grant all on tables to anon, authenticated, service_role;
alter default privileges in schema public grant all on functions to anon, authenticated, service_role;
alter default privileges in schema public grant all on sequences to anon, authenticated, service_role;

After running the following I got it working:

GRANT ALL ON ALL TABLES IN SCHEMA public TO anon, authenticated, service_role;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO anon, authenticated, service_role;
GRANT ALL ON ALL ROUTINES IN SCHEMA public TO anon, authenticated, service_role;

[TomasHubelbauer]

Hello, relating to supabase/supabase#4342 and my own discussion post here supabase/supabase#4742, is this PR going to enable listening to Realtime changes using the anon API key? And if so, how can one track the rollout of this feature now that it has been merged? Thank you

[olirice]

is this PR going to enable listening to Realtime changes using the anon API key?

yes it is

how can one track the rollout of this feature

It merged into supabase/realtime here https://github.com/supabase/realtime/pull/209 its currently in QA and is estimated to roll out to GA in 1-2 weeks.

The most likely place to see activity between now and when it releases is https://github.com/supabase/supabase/issues/4842