My website consists of a page with a form that is gathering information about people who want to take part in a webinar organized by our company. Via simple REST API call we save information such as: name, last name, email, phone number, profession, company and city.
Since we perform only write action to Supabase the given user doesn’t have to be authenticated.
All RLS are in place.
What I want to achieve:
We want to know who will be really taking part in a webinar (since we do not have this information after the webinar in Google Meet has finished (Google Meet doesn’t store the list of participants after the session has finished).
My thinking is like that: on the day of the webinar we will send to each registered participant a link with a new page in WeWeb. In this page there will be a simple input form with only one field to enter a participant’s email address. After the participant clicks a button ‘Enter the webinar’ we want to check if the provided email address already exists in our supabase database and if the provided email exists in our Supabase database we want to automatically redirect this user to Google Meet with a link in a workflow (Custom JS executed).
The problem here is that I cannot query the supabase database since the user is not allowed to read the data (he/she is not authenticated / authorized).
The question is how to do it either on WeWeb and on supabase side to provide WeWeb with the answer whether this given user already exists in the database.
Hello @lamalarek there are many ways to achieve this:
HAVE A PUBLIC NO-RLS TABLE WHERE ONLY EMAILS ARE STORED: This way the only information you’re storing on that new table is email, reducing the exposed data. So you when you insert the new row in ‘participants’ table when the user registered, you also insert an email in the email-only table. Now when the user is back to verify, when they enter their email, you fetch the collection (so DO NOT AUTOMATICALLY FETCH this collection) of that email-only table, and you USE A BACKEND FILTER where only email in the list = email in the input field. Now if the collection count = 1 then you redirect to the webinar page.
The backend filter ensure you don’t load other people’s emails into the front end for the participant (even though the public nature of the table means anyone can still go get the emails if they know your supabase url, but I think it’s a reasonable tradeoff).
Because to be very honest with you the only ‘standard secure’ way to do this is
2.
A. You write a backend endpoint ‘verify_email’
B. Write a Postgres function that does the verification (or use ‘pg’ in node.js)
C. You call the backend function with the email as a payload. The function will call the postgres function or uses pg (in node.js) to run the check
D. Function will then return the result to the client along with the response
Yeah I wouldn’t even suggest this as an option. I can come and wipe your table.
The second approach @AgentD mentions, would be the most viable one. There is too many ways to do this, you don’t need auth, you can create your own.
For your usecase, I would suggest a very simple logic, where when you’re sending the mails, you also attach an UUID to them, and use that to validate the calls from your function.
This way, you can do a search for the UUID, and if it is in the invited people table, you can allow that person to insert, and insert only under his UUID, or something similar. Sky is the limit.
I wrote a postgres function to check for the existence of a value in a table, that returns true/false.
But I can only call the function if the user is authenticated, which defeats the purpose of the function, which needs to be a public check.
I tried running the function privilege mentioned here Database Functions | Supabase Docs, but modified with anon (seemed like the only unauthenticated role?)
ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC; GRANT EXECUTE ON FUNCTION validationFunction TO anon;
So to clarify how this works. I’ll have to write a bit more. To do it correctly requires some technical knowledge
You’re trying to verify data in your data table for an UNAUTHENTICATED USER. So you SHOULD NOT do that via a direct front end call. Ideally. That’s baseline. Now the correct way you’d do this is:
A. You create a backend endpoint (Google cloud function, cloud run, Supabase edge function, fastgen endpoint, even bubble backend API WF would work tbh).
B. Now this backend endpoint will call a Supabase Database function which does the actual verification. The database Postgres function will be written with Security set as DEFINER (hence why you should not be calling it from the front end directly).
C. When you want to verify the user you call your backend endpoint with the email address as payload. This endpoint will then use the payload to call the database function which does the verification and returns a response to the client.
SOME SECURITY FOR THE ENDPOINT
CORS, so you turn on CORS to ensure ONLY your domain e.g. example.com can call the endpoint, also you ensure the user MUST be on the page you expect the call to come from for the endpoint to respond (so check the ‘referrer’ header before responding it must be example.com/sign-in or you don’t respond)
IP whitelisting. You white list ONLY the IP address of your front end. Ensuring only that IP can call the endpoint.
Use HTTPS at all times
The database function itself you define a role with narrow the scope to limit it to a single table and so on.
If you do this, you’ll have a fairly solid endpoint.
The easiest way I found was to set a Postgrest function with Security Invoker accepting strict params. Then you just call it from the front end and avoid the middleware you mention in the A. part. Like, the PostgREST which Supabase relies on is super safe, even against injections and stuff, and you expose it anyway by using Supabase in your project, so I think there is no need to gate it behind a backend endpoint, as you make the endpoint by exposing the postgres functions via PostgREST. For the rest, I agree If you don’t expose any vectors of attack, then you don’t need to take it through the Cloud middleware.
Fantastic point. But I see the problem he will have with your approach bro will be
A. The user is unauthenticated, he/she hasn’t logged in to the app yet, so setting up the Postgres function with security invoker will mean unless he has RLS policies that allow read permissions for unauthenticated anon roles (which is what I think the OP doesn’t want), the function will not return any values.
B. The reason for the Middleware is because the OP wants to read gated database information but wants to read it as an unauthenticated user. I can’t think of a safe way to do that from the frontend directly.
You’ve made a very solid suggestion, the OP has enough information to move forward with.
If you don’t expose any vectors of attack, then you don’t need to take it through the Cloud middleware.