Is it more efficient to include "where" condition in query with RLS?

[tawjaw]

Hello,

If I have a policy on select for a table (auth.uid() = user_id)

this

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

and this

supabase.from('table_name').select('*').eq('user_id', user_id)

should return the same rows. But is including the equal condition makes the query more efficient in any way?

[steve-chavez]

Hey @tawja,

You can always check the postgres planning/execution time with EXPLAIN ANALYZE on the SQL Editor.

explain analyze select * from table_name where user_id = <val>;
-- Planning Time: 0.312 ms
-- Execution Time: 0.116 ms

For your question, RLS acts like an implicit where, so you'd be essentially doing:

select * from table_name where user_id = auth.uid() and user_id = <val>;

Which should give you more or less the same performance.

[tawjaw]

How can I execute a query from the sql editor as a specific user?

To run the analyze with specific auth.uid()

[steve-chavez]

Hm, I think that cannot be done from the Supabase SQL Editor for now. But you can do it through another pg client, like psql.

The main idea is to switch the role inside a transaction:

begin;

-- switch role
set local role authenticated;

-- set the jwt attributes 

-- this is the auth.uid() value
set local "request.jwt.claim.sub" to <val>;

-- do the query you want to test
explain analyze select * from table_name;
-- you should see the filtered rows(maybe index scan) from the RLS policy

commit;

[tawjaw]

Thanks for the help @steve-chavez
I'll try that out