Prepare for the PgBouncer and IPv4 deprecations on 26th January 2024

Learn more
Home

Part Two: Row Level Security

About

Learn how to restrict access to your database tables by enabling Row Level Security and writing Postgres Policies in the Supabase Dashboard.

Watch

Securing Your Tables

In Supabase, you can access your data directly from the client (often the web browser), you do this by passing your Supabase URL and Anon key to supabase-js like so:


_10
const supabase = createClient(
_10
'https://qwertyuiop.supabase.co',
_10
'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiaWF0IjoxNTE2MjM5MDIyfQ.SflKxwRJSMeKKF2QT4fwpMeJf36POk6yJV_adQssw5c'
_10
)

This raises an interesting question however: "if my anon key is in the client, then can't someone read my javascript and steal my key?", the answer is yes. And this is where Postgres policies come in.

Using Postgres's "Row-Level-Security" policies, we can set rules on what data the anon key is allowed or not allowed to access by default.

We can say for example that the anon key should only be able to read from a particular table, but not write, update, nor delete.

And these rules can be as complex as we want. We could say that the anon key can only delete rows which were inserted on a Thursday afternoon between 4 and 6pm, and where the id column is an even number. Pretty strange, but it shows the power of policies.

Let's say we create a leaderboard table. We want people on our website to be able to read the leaderboard, but not write, update, or delete from it. We start by defining our table in SQL and adding some dummy data:


_11
create table leaderboard (
_11
name text,
_11
score int
_11
);
_11
_11
insert into leaderboard
_11
(name, score)
_11
values
_11
('Paul', 100),
_11
('Leto', 50),
_11
('Chani', 200);

Now let's set up a client to read the data, I've created a repl here to show a living example: https://replit.com/@awalias/supabase-leaderboard-demo#index.js. If you copy the snippet you can plug in your own Supabase URL and anon key.

You can see that it's possible to freely read from and write to the table by using:


_10
// Writing
_10
const { data, error } = await supabase.from('leaderboard').insert({ name: 'Bob', score: 99999 })
_10
_10
// Reading
_10
const { data, error } = await supabase
_10
.from('leaderboard')
_10
.select('name, score')
_10
.order('score', { ascending: false })

Now let's restrict access. We'll start by fully restricting the table. We can do this in the SQL editor by making a query:


_10
ALTER TABLE leaderboard ENABLE ROW LEVEL SECURITY;

or via the Supabase Dashboard, by navigating to Auth > Policies, and clicking the red padlock on the leaderboard table, so that it turns white.

Enable row level security in Supabase

You'll notice that reading will return no rows and writing will now fail with an error like:


_10
{
_10
hint: null,
_10
details: null,
_10
code: '42501',
_10
message: 'new row violates row-level security policy for table "leaderboard"'
_10
}

Now we need to add a policy to enable reading of the table, for everyone who sends the anon key (JWT) in the Authorization: Bearer header.

In SQL this can be done with:


_10
CREATE POLICY anon_read_leaderboard ON leaderboard
_10
FOR SELECT
_10
TO 'anon'
_10
USING (true);

anon_read_leaderboard here is just a name that you choose for your policy. leaderboard is the table name. FOR SELECT says that we only want this policy to apply for reads (or rather "selects" in SQL). TO means that this policy will only apply to the anon Postgres role. And finally the rule itself is true', which means it will allow any selects to the anon user.

If you'd prefer to use the dashboard to add your policy you can do so by clicking "Add Policy" in the Policies tab and making a policy like this:

Add a read only policy in Supabase

You should now be able to read from your leaderboard, but will still not be able to write, update, or delete from it, which is exactly what we wanted!

A quick reminder that you can always use your service_role API key to bypass these row level security policies. But be extra careful that you don't leak this key by including it in the client. This can be useful if you're building internal admin tools, or if you need to bulk insert or delete data via the API.

In the next guide we will look at using Policies in combination with User Accounts, so that you can restrict access to data on a User by User basis: Watch Part Three: Policies

Resources

Next steps