Security Advisor: Security Definer View false positive

[markledwich2]

To make RLS perform acceptably, I often make views with security definer and then filter the data with filters that use auth.uid() or auth.jwt() , or joins to other views that do. This performs drastically better than raw queries against RLS tables for us ( I am assuming because it will filter with optimizations as part of the whole query rather than a row-by-row if it is RLS).

For example, the following query is detected as a security error

create view profile_my with (security_barrier = true, security_invoker=off) as (
  select p.* from profile p
  where p.user_id = (select auth.uid()) and not p.deleted
);

Supabase treats errors as very serious in its communication via the weekly email, and there is no way to ignore them individually. So it makes these false positives frustrating to see permanently against the project.

[olirice]

performs drastically better than raw queries against RLS tables for us ... optimizations as part of the whole query rather than a row-by-row

so long as you use the recommendations from here https://supabase.github.io/splinter/0003_auth_rls_initplan/ it shouldn't be meaningfully different, but if you're happy with the views then that sounds perfectly valid too

no way to ignore them

the feature to ignore project lints is currently under development

While the approach you're taking makes great sense, users accidentally exposing data through security definer views is a gotcha that people may not be aware of so we have to flag it very aggressively. We're also limited in auto-detection in that we perform all checks through static analysis of the schema.

Its difficult to confirm that the filters in a view behave as expected, but its straightforward and safe to confirm that views are security invoker

I can see that its irritating in your case since you know what you're doing, but removing that default would make the platform less secure for most users.

In the short term I'd recommend ignoring the error. Mid term, the feature will be available for you to disable the check to avoid the emails. Sorry for the hassle

[markledwich2]

I'll wait for the ignore feature. I tried that optimization on my function has_role(role, workspace) marked as stable and it still evaluated every row, even ones with identical parameters to it.