Home

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
_10
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:


_10
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:


_18
// the base url - whatever you set in the Auth Settings in supabase.com/dashboard dashboard
_18
http://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:


_10
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:


_10
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:


_16
create table my_scores (
_16
name text,
_16
score int,
_16
user_id uuid not null
_16
);
_16
_16
ALTER TABLE my_scores ENABLE ROW LEVEL SECURITY;
_16
_16
insert into my_scores(name, score, user_id)
_16
values
_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:


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

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


_10
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:


_10
curl '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:


_10
create policy "Only Blizzard staff can update leaderboard"
_10
on my_scores
_10
for update using (
_10
right(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.


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

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#

Next steps#

© Supabase Inc—ContributingAuthor StyleguideOpen SourceSupaSquad