https://supabase.com/ logo
Join Discord
Powered by
<@!764524500162445402> I've been experimenting som...
# help
j
@User I've been experimenting some after our previous discussions, and I managed to make a simple/static RLS rule on update (WITH CHECK): 
(111 = client_id)
This would allow me to update only if the 
client_id
field/part of the new data (i.e. the POST data) was equal to "111". I have not been able to make any functions within Supabase at all, based on the code you suggested and based on the examples in the tutorials. Supabase only gives me syntax errors "near create" etc...
@User I've been experimenting some after our previous discussions, and I managed to make a simple/static RLS rule on update (WITH CHECK): 
(111 = client_id)
This would allow me to update only if the 
client_id
field/part of the new data (i.e. the POST data) was equal to "111". I have not been able to make any functions within Supabase at all, based on the code you suggested and based on the examples in the tutorials. Supabase only gives me syntax errors "near create" etc...
So I thought, why not just have the project_id and passhash in the POST body, thus avoiding having to deal with making a function to extract same data from the HTTP header?
If this is all SSL/HTTPS, then body will be encrypted, just like the header, right?
Since the sensor (client) making the post requests will not be logged in (i.e. not have a session/token) I figured each request would have to include the ID/pass, and then make a policy which would check this against the ID/pass -- in a different table than the one the policy is actually written "for"
Is that possible? Say table event contains fields 
id, project_id, client_id, sensor_value
and table project contains 
id, project_id, user_id, passhash
An insert/post would then have body, 
{ "project_id": 33, "client_id": 77, "sensor_value": "on", "passhash": "adshahsdjahsd" }
for example
and I thought it might be possible to make an RLS policy (for update on event) something along the logic of,
Copy code
(project_id = ( SELECT project.project_id
   FROM project
  WHERE (project.passhash)::text = passhash AND project.project_id = project_id)
)
g
I think you would still need the passhash col in the event table for insert/update to work, but not sure. You really don't care what the value is there so if you change passhash you just have to update your project table. It is only there so you can pass in post to event table. This only works for inserts/update and not for delete or select for the devices but you can have policies allowing users to read/delete.
j
hey 🙂
but I'd have to have a join (or subquery) in the policy, right?
I mean, since the (current) passhash is in the project table...
Can't seem to get the syntax right. I'm from an Oracle background
g
Project table is the way you would do it no matter what. Very common for roles/chat room permission etc.
RLS would be something like:
Copy code
for update using (
    true
  );
  for update with check (
    passhash in (
      select passhash from project
      where project_id = project.project_id
    )
  );
j
but that's where I get confused. If this were an update policy (i.e. update existing row in event) that code would check that the user could only update/change a row where the passhash matches the passhash in the body
I understand the left hand side (
passhash in (
) refers to the field in the body. But what about a policy for insert, where the is no current row/data/passhash to compare against?
g
So on update there is USING and WITH CHECK... USING is what is in the table and WITH CHECK is what is being inserted. I updated to show both. Insert would use the with check only.
The policy is comparing against the project table NOT the table you are inserting into.
It is just using the inserted values from post to do the comparison.
j
Yeah 🙂 This sort of works, but this WITH CHECK doesn't check that the passhash is actually the one belonging to the project_id (in the body/input)
so if I make two projects (project_id 1 and 2) with passhash "aaa" and "bbb", then any insert (post) will succeed with either of those passhashes. Surely, I've got this messed up, but I need to check the 
project_id
as well
It only gives 401 if I provide a passhash that isn't in the project table at all (on any project_id)
g
The using should probably be project_id = event.project_id. You want to make sure to only update the correct row.
j
Seems like the Supabase UX only allows to edit the CHECK part of INSERT policies
g
that is postgres only check. Not sure why you need to check project_id on insert as long as you have a valid passhash
You are checking with that with check clause the id and passhash match.
j
well, if there are multiple projects then the policy needs to check that the (unauthenticated) request has a valid user ID and passhash
If you recall, the user (i.e. the sensor) isn't logged in. Thus I want each POST (insert) to contain both the project_id and the passhash. Then, the policy should check that this combination exists in the project table
g
Yes that is what the with check I showed does.
j
but I'm able to insert 
project_id: 1, passhash: bbb
and 
project_id: 2, passhash: bbb
g
do they both have the same passhash?
j
when only the first combination exists in the project table... Sorry for taking so much time here 😉
no
in project, I tried putting two rows:
project_id 1, passhash aaa
and 
project_id 2, passhash bbb
(if you see what I mean)
g
yes. the policy I gave should select aaa if project_id is 1 and bbb if project_id is 2. Then it compares the provided passhash from body to that result
j
I think this is the snag: Supabase changes it to this:
Copy code
(passhash IN ( SELECT project.passhash
   FROM project
  WHERE (project.project_id = project.project_id)))
g
yeah that won't work
j
see? It adds the prefix 
project.
on both sides of the where clause
but there's no way around it?
if I edit it to 
WHERE (project_id = project.project_id)
then it just gets changed back
strange, right?
g
Here is the example:https://supabase.com/docs/guides/auth/row-level-security#policies-with-joins
basically auth.uid() is passhash
j
hmm... Is it because that's an update example, and with USING?
g
A quick fix is to not have same project_id name in the project table, but I'm puzzled why it is doing that. I don't really use the UI much and just generate the sql though.
Using should not matter.
j
let me try that! Change it to just 
project.id
or something
g
maybe it is project.project_id = project_id but I don't think it matters
j
ouch; now it does this:
Copy code
(passhash IN ( SELECT project.passhash
   FROM project
  WHERE (event.project_id = project.id)))
which is also not what I want 🙂
I mean, I want to compare against the values "coming in", right? Not what's in event
maybe that's why these things (id/pass) should be in the header after all
g
That might be fine. It is checking on that table.
j
you're right 🙂
g
So it might have worked before with event.project_id.....
j
probably! I've been confused by the USING/CHECK etc parts, and what's to go where...
the logic is so simple though
g
With Check is the only thing to look at incoming data.
j
yes 🙂
great!
g
update requires both but they can (and in your case) should be different
j
yeah
g
Part of this is because you are inserting a "fake" column versus passing at as header, but the end result should be the same.
j
I would like to be able to make a function (like the 
getDevId
you proposed) but that seems to have a whole new level of syntax (or UX) issues
also, SB has some warnings on the whole functions part
Alpha preview This is not suitable for production
g
No functions are great, that is only for the UI to generate them. functions are part of postgres and work fine.
j
oh sure!
with UX, though, it's the question of what's supposed to be entered where
as you of course know
but functions are neat! Done a lot of PL/SQL in Oracle
g
I mainly use the sql editor except to set up a basic table.
j
haven't seen that yet... They provide a general sql editor?
like, for the whole schema or something?
g
The >_ icon. I actually use datagrip as part of my IDE for my project and just use the UI for quick tests
j
The SQL icon on the left menu, I guess?
right...
funny thing; to "get into" the editor, you have to choose some template
g
No just start typeing
j
then I can delete all SQL there, and just attempt to CREATE OR REPLACE the function
oh ok
g
YES. If you look at that supabase example page I just posted that is all done thru the sql editor, not the ui
j
Ah , New Query first
from the >_ and Welcome page
g
Dinner calls, good luck
j
yes!
bon apetit!
thanks again!
g
@User Really want to see you not have to have a fake hash column not used in your data tables.... I sent a hash-header thru supabase.js as a test using the {header:{'hash-header':'abc'}} option. I have this function (thru sql editor not ui) 😎 :
Copy code
create or replace function get_hash_header() 
returns text 
language sql stable
as $$
  select 
      (current_setting('request.headers', true)::json->>'hash-header')::text
$$;
Tested with a supabase .js rpc call: const {data} = await supabase.rpc('get_hash_header') data comes back as 'abc' So you could use get_hash_header() in either the USING or the WITH CHECK in place passhash coming in the post body and you don't have to have a "fake" column that is not used and deal with it being in the table or not... You just do the project table select on id to see if passhash matches...
PreviousNext
Powered by