https://supabase.com/ logo
Join Discord
Powered by
Any idea why this doesn't work? I just don't under...
# sql
u
Any idea why this doesn't work? I just don't understand SQL well enough. Could anyone help me? I sort of laid this thing aside for a week or so and now I struggle. What I want is basically create a public table based on the values from a private one. I heard of 
sets
and 
queries
. Maybe they're the things I desperately need. I don't know! 🙏
Copy code
sql
create or replace function get_random_prices()
returns table (item_id int, value int)
as $$
declare
    random_prices table (item_id int, value int);
begin
    select id, floor(random() * (max_value - min_value + 1) + min_value)
    insert into random_prices  
    as value 
    from prices_ranges; -- private table, no policies set
    return random_prices;
end
$$ language plpgsql;
g
These links give good examples of returning tables from functions: https://sqlserverguides.com/postgresql-function-return-table/ https://www.postgresqltutorial.com/postgresql-plpgsql/plpgsql-function-returns-a-table/ If your prices_ranges is private then your function will only work from the sql window for testing or with the service key. If you are going to call it as part of a user request or rpc call then you will need to declare it "security definer" and do any protection you need with an if statement or where clause.
Also when you say you want to create a public table do you mean with an rpc call on demand? Or you want a real table in the database with this info?
u
Yeah, so what I want is this. Basically, I have this 
private
table called 
prices_ranges
. I want all of the ranges to stay private, no one should be able to access them at any time, outside of me in my Supabase panel, of course. What I also want is an 
rpc
that can be called from within my React application so that the user receives just the randomly generated values and has no knowledge about the ranges whatsoever.
That code works:
Copy code
sql
create or replace function get_random_prices()
returns table (item_id int, value int)
as $$
begin
    return query
    select
      id::int as item_id, 
      floor(random() * (max_value - min_value + 1) + min_value)::int as value
    from prices_ranges;
end
$$ language plpgsql;
But it still cannot be accessed 😦
What definer should I define? Also, will this not allow other people to see the ranges? I only want the result to be public.
g
If price_ranges has RLS on and no policies then no one can access it. Your rpc call would need to be “security definer” to use. That means anyone can call the rpc logged in or not. If you want to limit to logged in users you can add an if then and return empty table or maybe null for an error.
u
So this "security definer" allows anyone to access that returned data, but no one will ever be able to see 
prices_ranges
because it has RLS on. Correct?
g
yes
Anyone can see/use rpc you can't hide them. You can only put checks in them for protection, or rely on the RLS of the tables they use to protect that data. The security definer feature says the function acts with the privileges of the creator of the function, which usually is usually a super user, so bypasses the RLS. As long as you only return the data you want (protected or not), the user can't change your function to get at protected data, just what you return.
PreviousNext
Powered by