Supabase•2y ago

service_role has issues fetching complete data from views (not tables)

We are creating a client on our server that uses the SERVICE_ROLE_KEY Whenever the service role queries a view (not a table), it seems to be missing some data. When we use a regular server client with cookies, we are able to retrieve the data. The service role, otherwise, seems to have admin privileges and no issues accessing data. Is this a known issue?

34 Replies

garyaustin•2y ago

No. How are you setting up your service-role client and what is the environment?

chrysbOP•2y ago

Hi @garyaustin We're using node, nextjs13. On the server-side, we create an "admin client".

createSupabaseClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_ROLE_KEY!
  )

createSupabaseClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_ROLE_KEY!
  )

And we didn't do anything special on the supabase database UI.

garyaustin•2y ago

Is this auth-helpers?

chrysbOP•2y ago

We noticed funky behavior with two of our views. In one view (which joins a couple tables together), it would only return partial rows. No, this is just our own.

import { createClient as createSupabaseClient } from '@supabase/supabase-js'

export const createAdminClient = () => {
  if (typeof window !== 'undefined') {
    throw new Error('createAdminClient must be used server-side only')
  }

  return createSupabaseClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_ROLE_KEY!
  )
}

import { createClient as createSupabaseClient } from '@supabase/supabase-js'

export const createAdminClient = () => {
  if (typeof window !== 'undefined') {
    throw new Error('createAdminClient must be used server-side only')
  }

  return createSupabaseClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_ROLE_KEY!
  )
}

I can confirm invoking the client this way has full read / write access to all tables otherwise.

garyaustin•2y ago

I don't think this has anything to do with your "error" but you should go ahead and set persistSession and refreshSession options false. I was mainly concerned if you ever share the client with a user session (which auth-helpers does) and the user session takes over from the service_role init. Were your views created in the Dashboard UI or SQL editor? Anything like Prisma involved?

chrysbOP•2y ago

We created in the supabase dashboard using ths SQL editor

garyaustin•2y ago

Anything common about the missing data? From same table?

chrysbOP•2y ago

A related note, is that when you create views you must add WITH (security_invoker) otherwise the view won't respect RLS at all. Not sure if this is in supabase docs, but pretty big security hole.

garyaustin•2y ago

That is a new feature to Postgres and I believe it is documented now and before warned about them ignoring RLS.

chrysbOP•2y ago

It was just missing new rows. Didn't observe it long enough to see if it's a time delay thing. For example: With authenticated role: Record 170 - 169 - 168 - 167 Service role: - 168 - 167

garyaustin•2y ago

Do your views doing any WHERE logic with auth.uid() Wait. You updated the data and then it was missing?

chrysbOP•2y ago

No, here's an example:

create view
  public.avatar_assigns as
select distinct
  c."postId",
  c.avatar,
  s.signature
from
  comments c
  left join comments_signatures s on s."commentId" = c.id
where
  c.avatar is not null;

create view
  public.avatar_assigns as
select distinct
  c."postId",
  c.avatar,
  s.signature
from
  comments c
  left join comments_signatures s on s."commentId" = c.id
where
  c.avatar is not null;

garyaustin•2y ago

next.js does caching.

chrysbOP•2y ago

Yeah, I would make a new post and then it wouldn't show up in the direct result of the supabase.from... query on the server-side.

garyaustin•2y ago

https://github.com/orgs/supabase/discussions/19084

GitHub

Next.js 13/14 stale data when changing RLS or table data. · supabas...

Next.js caches urls in certain cases. This is causing users to lose lots of time debugging early on with RLS changes. Changing the table data in the UI will also not be returned in these cases. You...

chrysbOP•2y ago

Interesting. The queries are happening on the NextJS server, though. Does that apply here? And why would it only apply to the service role client vs. the authenticated role client

garyaustin•2y ago

I don't use next.js but I would think so. This is a next.js thing. It caches based on URL If the URL changes then that would bust the cache.

chrysbOP•2y ago

I can try loading up TablePlus and connecting to the db directly as the service role if that's possible

garyaustin•2y ago

I would look into the two methods shown or next.js docs on how to turn off caching. You can also look at the API Edge log in the dashboard and see if you are getting the request from service_role.

chrysbOP•2y ago

Let me check. Thank you, btw, really appreciate this depth of dialog

garyaustin•2y ago

If you are only missing "new" data this almost certainly is it.

chrysbOP•2y ago

How can I identify the service role user? In the request metadata?

garyaustin•2y ago

I don't think you can. All you have is the URL to work from. Another way to test is slightly change the js query to include or drop a column in the select. Those are part of the URL so bust the cache.

chrysbOP•2y ago

Let me try that Yep that seemed to do it — very interesting that it only caches the requests from the service role client?

garyaustin•2y ago

No. You just happen to not have the other's cached when they ran. It may look at the headers too, not sure. If so then authorization header is different for each.

chrysbOP•2y ago

It's weird that it only does it on views

garyaustin•2y ago

I doubt it. Probably just luck on testing or using filters, etc.

chrysbOP•2y ago

We just switched to the service role today. We've been using the authenticated role for weeks now without encountering this issue.

garyaustin•2y ago

To the REST API a view is the same as a table. It does not know the difference.

chrysbOP•2y ago

It's suspect that it's only happening with views with the service role client, all other tables are working fine.

garyaustin•2y ago

The service role certainly would not impact which rows are received on its own. It would be all or nothing if grants were messed up on a view or table. As it ignores RLS that leaves your WHERE and what you showed does not depend on auth.uid().

chrysbOP•2y ago

Hmm restarting NextJS doesn't resolve the issue. Where on earth are they caching this? lol

garyaustin•2y ago

Also changing the select column busted the cache, so it surely is that. I don't know next.js and how it caches and when it caches, you'll have to investigate that. Or ask for help on that specifically, there are many next.js users here... although this weekend will be very slow.

chrysbOP•2y ago

Thanks so much @garyaustin — I will report back any findings from our toiling @garyaustin I crafted a solution. Thankfully, supabase allows you to pass in a custom fetch function when initializing a client. I leverage that to add the additional parameters to tell next not to cache.

  return createClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_ROLE_KEY!,
    {
      auth: {
        persistSession: false,
        autoRefreshToken: false,
      },
      global: {
        fetch: (input, init) =>
          fetch(input, { ...init, next: { revalidate: 0 } }),
      },
    }
  )

  return createClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_ROLE_KEY!,
    {
      auth: {
        persistSession: false,
        autoRefreshToken: false,
      },
      global: {
        fetch: (input, init) =>
          fetch(input, { ...init, next: { revalidate: 0 } }),
      },
    }
  )

Gaming

Programming

service_role has issues fetching complete data from views (not tables)

Did you find this page helpful?