Using authentication policies with views

[Nick-Mazuk]

It appears that row-level policies set in the dashboard don't apply to views.

Let's say I have the following table and view in Supabase.

CREATE TABLE posts (
    id uuid primary key,
    title varchar(100),
    author uuid not null,
);

CREATE VIEW sorted_titles as SELECT title FROM posts ORDER BY title;

Now I only want users to be able to read their own posts. So I added a policy to the Authentication > Policies tab in the Supabase dashboard for SELECT on "posts".

auth.uid() = author

Great! Now if a user tries to access posts in the table, they'll only get their own posts.

However, this query will now returns everyone's posts, not just a user's own post.

supabase.from('sorted_titles').select('*')

Is there a way to still use the row level policies with the view?

P.S. Sorry if this is common knowledge in postgres, I'm learning postgres at the same time I'm exploring Supabase.

[steve-chavez]

Edit: For PostgreSQL 15, this now is supported natively with security invoker views:

CREATE VIEW sorted_titles WITH (security_invoker) as SELECT title FROM posts ORDER BY title;

---

Edit: The below answer is for projects with PostgreSQL <= 14

However, this query will now returns everyone's posts, not just a user's own post.
CREATE VIEW sorted_titles as SELECT title FROM posts ORDER BY title;

Yes, this is a quirk of how views work. They're called with the privileges of the role that created them(the owner). If you did CREATE VIEW through the SQL Editor, then the owner is supabase_admin and that role is a SUPERUSER(it bypasses RLS).

To fix this you can change the view's owner with:

ALTER VIEW sorted_titles OWNER TO authenticated;

Then the view will respect the RLS policies you've defined.

[steve-chavez]

@charlie17 You could change the view's owner in a transaction, select it and then rollback:

begin;
alter view items_view owner to postgres;
select * from items_view;
rollback;

Though that might be prone to error. I think it would be simpler to just create another view of the query and not alter its owner.

[vishwasnavadak]

Late for the conversation but in the latest supabase postgres editor, postgres is the default user and it fails to run the command with below error, is there any way to get around it?

Failed to run sql query: must be member of role "authenticated"

[steve-chavez]

@vishwasnavadak On newest projects the ALTER .. OWNER.. shouldn't be needed because they come with PostgreSQL 15 and this has the security invoker views feature.

So instead this should be solved like:

CREATE VIEW sorted_titles WITH (security_invoker) as SELECT title FROM posts ORDER BY title;

[vishwasnavadak]

@vishwasnavadak On newest projects the ALTER .. OWNER.. shouldn't be needed because they come with PostgreSQL 15 and this has the security invoker views feature.

So instead this should be solved like:

CREATE VIEW sorted_titles WITH (security_invoker) as SELECT title FROM posts ORDER BY title;

Thanks, will try this one out!

Edit: This worked 🙌 Had to make sure postgres was running v15 or above.

[igorlanko]

This is a great answer, but for anyone reading this please be aware there's a relatively serious security-related oddity at the moment in how Supabase manages view's schemas with security_invoker. TLDR: it ignores it.

#21268

[allezxandre]

Hello, any news on when we can expect Supabase instances to be upgraded to Postgres 15 to get the security invoker feature on views?

[vishwasnavadak]

If your old project is not having postgres 15, pause the project and start it (don't hit restart, Pause and Start), you should get updated postgres.

[srossross]

I'm still having issues. where is the pause setting?

[vishwasnavadak]

@srossross
go to https://app.supabase.com/project/<project-id>/settings/general, you'l be able to find the Pause under Infrastructure.

[srossross]

ah thanks, I don't seem to have that option:

[srossross]

I can confirm that this is working locally with the same migration files

create or replace view "public"."docs_with_associated_users" with (security_invoker) as select
    p.id, ...

402fff8cd54a   public.ecr.aws/supabase/postgres:15.1.0.90        "sh -c 'cat <<'EOF' …"   2 weeks ago   Up 3 minutes (healthy)   0.0.0.0:54322->5432/tcp                                                     supabase_db_****

---

To be clear

the migration using with (security_invoker) succeeds, it just does not respect my policies on supabase cloud, but does respect them on my local machine

[RiccardoCherchi]

Hi, after some digging about this topic (views and RLS on them) i ended up with two main concepts:

• for public and not secured views just do not use the security invoker
• for RLS security use the security invoker (but it will copy the policies of the referenced table)

But i have problems to understand one think: For example if i have a table with a RLS policy that enable only the owner to view his rows and i have a view (with fewer columns redacted from the table) and i want to show them ONLY to the authenticated group or only a set of users (private subscribers maybe like instagram), how can i achieve this?

TL;DR: How can i apply different RLS policies to the table and the views separately?

Thanks you all for the support❤️