https://supabase.com/ logo
Join DiscordCommunities
Powered by
how to you create a RLS rule that allows count(*) ...
# sql
b
how to you create a RLS rule that allows count(*) group by primary_key without allowing access to the data in the table?
s
You can use a view instead to get the count only
Or use a postgres function to get the count
b
if I use a view, I have the use the security_barrier option don't I?
just worried about data leaks such as above
s
Use a postgres function then
b
apart from security_barrier - is there anything else I need to worry about in terms of bypassing RLS by creating a view with security_definer?
but a function is inefficient right - as in it has to execute the function for every row
s
No functions are not inefficient
It would be running a single query
Unless you aren't stating your case fully here
b
if it executes the function for every row, then operation becomes O(n^2) operation instead of a O(n) left join right?
ok I will explain usage more
s
This is why I said you aren't stating your case fully here, because there was no mention of a join
b
example with 2 tables: book and reservation
books have a id, referenced as title_id foreign key in reservation
I want to return the num_reservations of all books as a view without exposing who reserved the books
s
Can you show what your sql query would look like
b
sorry one moment
let me give you a valid query
here
SELECT b.id, count(r.member_id) as num_reservations from thebookshelf.book b, thebookshelf.reservation r where b.id = r.title_id group by b.id;
above is what I want
but I don't want to provide public access to all columns of r ( in particular r.member_id column)
sorry will be away for 10 mins
I guess I am better off doing it as a function for now and only worry about performance when its time (i.e. avoid premature optimization)?
Copy code
create or replace function num_reservations(t_id integer) returns integer as $$
declare
   reservation_count integer;
begin
 SELECT count(member_id) into reservation_count
 from thebookshelf.reservation
 where title_id = t_id;

 return reservation_count;
end;
$$ language plpgsql security definer;
going with above for now, then calling the function in another query to create the desired view
s
You can call the function with the 
.rpc('num_reservations', { t_id: 1})
You don't need to call it in a view
b
I want to incorporate num_reservations into a much bigger query
s
Why not just write that bigger query in this one function
b
I have a view full_book_detail that returns all the info about a book, such as title/description, if the logged in user has borrowed it, if the user has purchased it, when its due to be returned, how many other people have reserved it, etc...
in my app, I show list of all the books with above info - so I don't want to do a .rpc for every single book as that is a lot of round trips
want to do it in a single select of a view, for network efficiency (and database query efficiency tool)
s
> but a function is inefficient right - as in it has to execute the function for every row @bh No, functions can be inlined, see: https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions
b
unfortunately I have to use SECURITY DEFINER for this function in order to bypass RLS, so it cannot be inlined according to your link, so it will be called for each row
PreviousNext
Powered by