Learn how to restrict access to your database tables by enabling Row Level Security and writing Postgres Policies in the Supabase Dashboard.
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:
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:
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:
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:
or via the Supabase Dashboard, by navigating to Auth > Policies, and clicking the red padlock on the leaderboard table, so that it turns white.
You'll notice that both reading and writing now fail with an error like:
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:
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). And finally the rule itself is
auth.role() = 'anon'.
auth.role() is referring to a SQL function
role that Supabase injects into the
auth schema in your database. The function actually looks like this:
The purpose of the function is to extract the
role claim from any JWTs that are passed to your API via the
Authorization: Bearer header.
Other available functions for use here include:
auth.uid() which will fetch the
sub claims respectively.
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:
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