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:

// see full api reference here: /docs/reference/javascript/auth-signup
supabase.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:

http://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:

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

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

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

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:

select * 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:

console.log(supabase.auth.getSession())

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:

create table my_scores (
    name text,
    score int,
    user_id uuid not null
);
ALTER TABLE my_scores ENABLE ROW LEVEL SECURITY;
insert into my_scores(name, score, user_id)
values
  ('Paul', 100, '5a4365e7-7c7d-4eaf-a8ee-9ec9432917ca'),
  ('Paul', 200, '5a4365e7-7c7d-4eaf-a8ee-9ec9432917ca'),
  ('Leto', 50,  '9ec94326-2e2d-2ea2-22e3-3a535a4365e7');
-- use UUIDs from the auth.users table if you want to try it
-- 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:

CREATE POLICY user_update_own_scores ON my_scores
    FOR ALL
    USING (auth.uid() = user_id);

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

supabase.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:

curl 'https://sjvwsaokcugktsdaxxze.supabase.co/rest/v1/my_scores?select=*' \
-H "apikey: <ANON_KEY>" \
-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:

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

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 a new query.

create or replace function user_agent()
returns text
language sql
as $$
  select nullif(current_setting('request.headers', true)::json->>'user-agent', '')::text;
$$;

See the full PostgreSQL policy docs here: https://www.postgresql.org/docs/12/sql-createpolicy.html

You can get as creative as you like with these policies.

Resources#

JWT debugger: https://jwt.io
PostgeSQL Policies: https://www.postgresql.org/docs/12/sql-createpolicy.html
PostgREST Row Level Security: https://postgrest.org/en/v7.0.0/auth.html

Next steps#

Edit this page on GitHub