Realtime RLS Problems - Cannot INSERT or UPDATE, only DELETE

[rexhibition]

Bug report

Describe the bug

My Supabase table when I set the primary key column to be NanoID caused a problem with Realtime detecting changes.

Basically, it only registers DELETE. When I INSERT or UPDATE anything in the table, Realtime doesn't detect it. When I set the primary key back to UUID by default, it's working fine.

However, when I disable RLS completely from that table. The Realtime works normally with NanoID. So I think there might be some problem with the RLS or authentication.

Screenshots

My VueJS code for subscribing & submiting data.

I even tried to create a public RLS but still not working.

System information

• OS: Windows (Supabase on VPS Server)
• Version of supabase-js: supabase/studio:20240301-0942bfe

[barrownicholas]

I am experiencing this also, I was just coming to report this behavior... here's a video (below).
I can also confirm that I am seeing 100% of what @rexhibition is reporting.

As seen in the video below:

• if primary key exists, no realtime updates occur in the table to the left (I am positive it is not the table, I have used it in prod for over 2 years with realtime without issues)
• if primary key does not exist, everything works exactly as it has been for 2 years

Screen.Recording.2024-08-04.at.1.35.55.PM.1.online-video-cutter.com.1.mp4

  
        SERVICE IMAGE      │      LOCAL       │ LINKED
  ─────────────────────────┼──────────────────┼─────────
    supabase/postgres      │ 15.1.1.78        │ -
    supabase/gotrue        │ v2.151.0         │ -
    postgrest/postgrest    │ v12.2.0          │ -
    supabase/realtime      │ v2.29.15         │ -
    supabase/storage-api   │ v1.0.6           │ -
    supabase/edge-runtime  │ v1.55.0          │ -
    supabase/studio        │ 20240729-ce42139 │ -
    supabase/postgres-meta │ v0.83.2          │ -
    supabase/logflare      │ 1.4.0            │ -
    supabase/supavisor     │ 1.1.56           │ -

[barrownicholas]

@filipecabaco just wanted to flag this on your end. Another weird thing about this bug... I am only experiencing this on the newest CLI version, with tables created after updating to v 1.187.10. All of my old tables, created prior to updating, have no issues whatsoever.

[barrownicholas]

@filipecabaco major update to help debug this:

I am experiencing this issue as early as supabase cli 1.187.10 (which, in turn, uses realtime 2.29.15 (via https://github.com/supabase/cli/releases/tag/v1.181.2)

I am not experiencing this issue on supabase cli 1.178.6 (which, in turn, uses realtime 2.29.13 (via https://github.com/supabase/cli/releases/tag/v1.178.6)

My best guess is that 2.29.15 broke how Realtime interprets RLS policies (since 1.178.6 used 2.29.13 and 1.187.10 used 2.29.15). This actually makes sense, because 2.29.15 does in fact change how RLS is interpreted by realtime: #1085 (I think #1085 might have broken something with how postgres_changes are interpreted against RLS).

As a temporary work-around @rexhibition , you can use npx -y [email protected] ... (such as npx -y [email protected] start) to revert back to a working RLS version while this gets straightened out.

[rexhibition]

@filipecabaco major update to help debug this:

I am experiencing this issue as early as supabase cli 1.187.10 (which, in turn, uses realtime 2.29.15 (via https://github.com/supabase/cli/releases/tag/v1.181.2)

I am not experiencing this issue on supabase cli 1.178.6 (which, in turn, uses realtime 2.29.13 (via https://github.com/supabase/cli/releases/tag/v1.178.6)

My best guess is that 2.29.15 broke how Realtime interprets RLS policies (since 1.178.6 used 2.29.13 and 1.187.10 used 2.29.15). This actually makes sense, because 2.29.15 does in fact change how RLS is interpreted by realtime: #1085 (I think #1085 might have broken something with how postgres_changes are interpreted against RLS).

As a temporary work-around @rexhibition , you can use npx -y [email protected] ... (such as npx -y [email protected] start) to revert back to a working RLS version while this gets straightened out.

Thank you for your thorough report and suggested workaround. I hope this issue is resolved soon! 😊 I was a bit worried since hardly anyone had reported this before.

[filipecabaco]

thank you so much @barrownicholas 🙏

@rexhibition was the bug happening in an existing project, if so indeed one of the migrations could have broken something.

Plus the subcriber_pool_size is indeed wrong, not even sure how it changed 🤦

[Moe03]

Same exact issue with me, disabling RLS makes realtime work without issues but enabling it break insert/update events, running version 2.45.4

[filipecabaco]

Hi 👋

Could you provide a repository or gist that is able to replicate the issue?

[Moe03]

Sure @filipecabaco ty for fast response:
https://github.com/Moe03/supabase-realtime-bug

Example using prisma to replicate the exact setup, enables RLS & adds the tables to realtime table in the postinstall script so you can run the dev server > got to /test and it should log the issue, sometimes it gives an event but its "unauthorized" even though we're using the service_role key which should bypass RLS and sometimes it just doesn't catch the trigger, also I want to ask if realtime even works properly in server or is it supposed to only work on browser env?

Moreover when we disable RLS the realtime updates work without issues so it doesn't seem to be an environment issue, you coudl try yourself in the postmigration.ts file to disable RLS and it should then work without issues whatsoever

[Moe03]

@filipecabaco sorry for being annoying but did you get any time to take a quick look at that?
https://github.com/Moe03/supabase-realtime-bug

[filipecabaco]

@Moe03 sorry for the delay, will check this Monday 👍

[kadengriffith]

I ran into a potentially related issue with RLS and realtime yesterday. I was receiving eventType: 'DELETE' events as an anon role, when I only have service_role and authenticated role policies defined. anon was not receiving eventType: 'INSERT' | 'UPDATE'as expected.

I haven't found any documentation suggesting realtime DELETE events bypass RLS, but if that's intended feel free to ignore.

If it sounds related I can reproduce the issue in a simplified example and share it here.

[filipecabaco]

@kadengriffith please do share, the more examples the better as it will help better understand it. I will work on this today still 🙏 sorry for the delay as there are multiple tracks on Realtime at the moment 😓

[barrownicholas]

I was receiving eventType: 'DELETE' events as an anon role, when I only have service_role and authenticated role policies defined.

This is almost the exact issue I was facing. 'anon' role only got DELETE events, but I believe I actually did have policies for 'anon' because I could SELECT and INSERT as 'anon'.

[kadengriffith]

@kadengriffith please do share, the more examples the better as it will help better understand it. I will work on this today still 🙏 sorry for the delay as there are multiple tracks on Realtime at the moment 😓

No problem. I'll get an example together 🫡

[kadengriffith]

I was receiving eventType: 'DELETE' events as an anon role, when I only have service_role and authenticated role policies defined.

This is almost the exact issue I was facing. 'anon' role only got DELETE events, but I believe I actually did have policies for 'anon' because I could SELECT and INSERT as 'anon'.

Interesting. That seems more like an entire RLS rule problem, but unsure. I'd have to look more closely at the example.

It was really difficult for me to debug a separate JWT issue I was creating on my side because DELETE was coming through on anon when I didn't expect it. I thought my RLS was just wrong, but really I wasn't even acting as the authenticated role to begin with.

[kadengriffith]

@kadengriffith please do share, the more examples the better as it will help better understand it. I will work on this today still 🙏 sorry for the delay as there are multiple tracks on Realtime at the moment 😓

@filipecabaco, please see https://github.com/kadengriffith/supabase-realtime-1114 for reproduction 🙂

[filipecabaco]

Actually after talking with a colleague I remembered of something really important 🤦 :

Unfortunately we can't apply RLS policies to DELETE which is why it works for DELETE but not for INSERT and UPDATE.

That's why DELETE always appears 😞

It's here in the documentation https://docs-git-docs-new-realtime-limitations-view-supabase.vercel.app/docs/guides/realtime/postgres-changes#receiving-old-records but I will add the feedback that this should be way more visible to users.

[kadengriffith]

@filipecabaco Gotcha! No issues on my end then 😊. Thanks for pointing to the docs. That's very helpful to know. Sorry to waste cycles!

[filipecabaco]

No waste! clearly that warning need to be more prominent in the docs

[filipecabaco]

@Moe03 I will still look at your scenario as it seems that there are more scenarios to take into consideration

[filipecabaco]

@Moe03 can you point me in the demo where you are creating the RLS policies for each table? I do see that you enable RLS but I don't see the settings of each RLS policy for each table.

[Moe03]

@Moe03 can you point me in the demo where you are creating the RLS policies for each table? I do see that you enable RLS but I don't see the settings of each RLS policy for each table.

We only enable RLS without specifying any rules & I assume the service role bypassed these rules all together isn't that the case?

[Moe03]

@filipecabaco Alright I understand now, adding this line in the script:

grant all on table "${tableName}" to service_role;

fixes the issue and i'm able now to recieve updates, ty for your help!

[Monu18765]

I am facing exactly opposite issue recently supabase/supabase#30027