Part Three: Policies

About

How to restrict table access to authenticated users, row level policies, and email domain based access.

Watch

User based row level policies

Now that we know how to restrict access to tables based on JWT roles, we can combine this with user management to give us much more control over what data your users can read to and write from your database.

We'll start with how user sessions work in Supabase, and later move on to writing user-centric policies.

Let's say we're signing a user up to our service for the first time. The typical way to do this is by invoking the following method in supabase-js:


_10// see full api reference here: /docs/reference/javascript/auth-signup
_10supabase.auth.signUp({ email, password })

By default this will send a confirmation email to the user. When the user clicks the link in the email, they will be redirected to your site (you need to provide your site url in Auth > Settings on the dashboard. By default this is http://localhost:3000) and the full URL including query params will look something like this:


_10http://localhost:3000/#access_token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJhdWQiOiJhdXRoZW50aWNhdGVkIiwiZXhwIjoxNjE2NDI5MDY0LCJzdWIiOiI1YTQzNjVlNy03YzdkLTRlYWYtYThlZS05ZWM5NDMyOTE3Y2EiLCJlbWFpbCI6ImFudEBzdXBhYmFzZS5pbyIsImFwcF9tZXRhZGF0YSI6eyJwcm92aWRlciI6ImVtYWlsIn0sInVzZXJfbWV0YWRhdGEiOnt9LCJyb2xlIjoiYXV0aGVudGljYXRlZCJ9.4IFzn4eymqUNYYo2AHLxNRL8m08G93Qcg3_fblGqDjo&expires_in=3600&refresh_token=RuioJv2eLV05lgH5AlJwTw&token_type=bearer&type=signup

Let's break this up so that it's easier to read:


_18// the base url - whatever you set in the Auth Settings in supabase.com/dashboard dashboard
_18http://localhost:3000/
_18
_18// note we use the '#' (fragment) instead of '?' query param
_18// the access token is a JWT issued to the user
_18#access_token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJhdWQiOiJhdXRoZW50aWNhdGVkIiwiZXhwIjoxNjE2NDI5MDY0LCJzdWIiOiI1YTQzNjVlNy03YzdkLTRlYWYtYThlZS05ZWM5NDMyOTE3Y2EiLCJlbWFpbCI6ImFudEBzdXBhYmFzZS5pbyIsImFwcF9tZXRhZGF0YSI6eyJwcm92aWRlciI6ImVtYWlsIn0sInVzZXJfbWV0YWRhdGEiOnt9LCJyb2xlIjoiYXV0aGVudGljYXRlZCJ9.4IFzn4eymqUNYYo2AHLxNRL8m08G93Qcg3_fblGqDjo
_18
_18// valid for 60 minutes by default
_18&expires_in=3600
_18
_18// use to get a new access_token before 60 minutes expires
_18&refresh_token=RuioJv2eLV05lgH5AlJwTw
_18
_18// can use as the Authorization: Bearer header in requests to your API
_18&token_type=bearer
_18
_18// why was this token issued? was it a signup, login, password reset, or magic link?
_18&type=signup

If we put the access_token into https://jwt.io we'll see it decodes to:


_11{
_11  "aud": "authenticated",
_11  "exp": 1616429064,
_11  "sub": "5a4365e7-7c7d-4eaf-a8ee-9ec9432917ca",
_11  "email": "ant@supabase.io",
_11  "app_metadata": {
_11    "provider": "email"
_11  },
_11  "user_metadata": {},
_11  "role": "authenticated"
_11}

The authenticated role is special in Supabase, it tells the API that this is an authenticated user and will know to compare the JWT against any policies you've added to the requested resource (table or row).

The sub claim is usually what we use to match the JWT to rows in your database, since by default it is the unique identifier of the user in the auth.users table (as a side note - it's generally not recommended to alter the auth schema in any way in your Supabase database since the Auth API relies on it to function correctly).

For the curious, try heading to the SQL editor and querying:


_10select * from auth.users;

If supabase-js is loaded on your site (in this case http://localhost:3000) it automatically plucks the access_token out of the URL and initiates a session. You can retrieve the session to see if there is a valid session:


_10console.log(supabase.auth.getSession())

Note that auth.getSession reads the auth token and the unencoded session data from the local storage medium. It doesn't send a request back to the Supabase Auth server unless the local session is expired.

You should never trust the unencoded session data if you're writing server code, since it could be tampered with by the sender. If you need verified, trustworthy user data, call auth.getUser instead, which always makes a request to the Auth server to fetch trusted data.

Now that we can use methods to issue JWTs to users, we want to start fetching resources specific to that user. So let's make some. Go to the SQL editor and run:


_16create table my_scores (
_16    name text,
_16    score int,
_16    user_id uuid not null
_16);
_16
_16ALTER TABLE my_scores ENABLE ROW LEVEL SECURITY;
_16
_16insert into my_scores(name, score, user_id)
_16values
_16  ('Paul', 100, '5a4365e7-7c7d-4eaf-a8ee-9ec9432917ca'),
_16  ('Paul', 200, '5a4365e7-7c7d-4eaf-a8ee-9ec9432917ca'),
_16  ('Leto', 50,  '9ec94326-2e2d-2ea2-22e3-3a535a4365e7');
_16
_16-- use UUIDs from the auth.users table if you want to try it
_16-- for yourself

Now we'll write our policy, again in SQL, but note it's also possible to add via the dashboard in Auth > Policies:


_10CREATE POLICY user_update_own_scores ON my_scores
_10    FOR ALL
_10    USING ((select auth.uid()) = user_id);

Now, assuming you have an active session in your javascript/supabase-js environment you can do:


_10supabase.from('my_scores').select('*').then(console.log)

and you should only receive scores belonging to the current logged in user. Alternatively you can use Bash like:


_10curl 'https://sjvwsaokcugktsdaxxze.supabase.co/rest/v1/my_scores?select=*' \
_10-H "apikey: <ANON_KEY>" \
_10-H "Authorization: Bearer <ACCESS_TOKEN>"

Note that the anon key (or service role key) is always needed to get past the API gateway. This can be passed in the apikey header or in a query param named apikey. It is passed automatically in supabase-js as long as you used it to instantiate the client.

There are some more notes here on how to structure your schema to best integrate with the auth.users table.

Once you get the hang of policies you can start to get a little bit fancy. Let's say I work at Blizzard and I only want Blizzard staff members to be able to update people's high scores, I can write something like:


_10create policy "Only Blizzard staff can update leaderboard"
_10  on my_scores
_10  for update using (
_10    right((select auth.jwt() ->> 'email'), 13) = '@blizzard.com'
_10  );

Supabase comes with two built-in helper functions: auth.uid() and auth.jwt().

To create your own functions, navigate to the SQL editor and create a new query.


_10create or replace function user_agent()
_10returns text
_10language sql
_10as $$
_10  select nullif(current_setting('request.headers', true)::json->>'user-agent', '')::text;
_10$$;