Authentication and RLS in Supabase: Roles and Access

This is the central article of the guide: the whole Supabase security model rests on row access rules. A project where they are wrong looks like it works and does not.

How authentication works

A user signs up or signs in and the service issues a token. The application sends that token with every request, and the database uses it to know who is asking.

The detail to grasp immediately: queries go to the database directly from the application. There is no intermediate layer checking rights - rules in the database itself play that role.

Hence two keys:

• The public key is used in the application and is by definition available to any user. It grants nothing by itself: rights come from the token and the policies.
• The service key bypasses every rule. It must live only on a server and never appear in client code. Leaking it means full access to all data.

What RLS is

Row Level Security is a PostgreSQL mechanism restricting access at the level of individual rows.

Instead of a check in application code, you describe a rule once in the database: “a user sees only orders where the owner identifier matches their own”. That rule applies to every query, whatever its origin.

Two mandatory conditions, and both get skipped:

1. Row level security is enabled on the table. Without it the table is open to everyone.
2. Policies exist. A table with RLS enabled and no policies is fully closed, which is more honest but also wrong.

Typical policies

A set covering most projects:

Own rows visible, others not. The user reads and modifies rows where they are the owner.

Public read, restricted write. A catalogue visible to everyone, editable only by the record owner.

Separate read and write. Distinct policies for select, insert, update and delete. A common mistake is one policy for everything: the user gains the right to modify what they should only read.

Roles. The role marker lives in the database rather than in a token the user could forge. The policy checks the role by querying the users table.

Soft delete instead of real delete. The record is flagged as deleted and the policy hides flagged rows. Cheaper than recovering deleted data.

Where MVPs break

Five mistakes, by frequency:

1. RLS not enabled. The table exists, data is in it, everything works. It works because access is open to anyone holding the public key. The most common and most expensive mistake.

2. The service key in client code. It bypasses every rule, and a key in frontend code is visible to everyone.

3. Permission checks only in the interface. The delete button is hidden while the delete request succeeds: the user can send it directly.

4. An overly broad policy. A read-everything rule added “during debugging” that stays forever.

5. Role taken from the token. Tokens are formed on the client and prove nothing. Roles are checked against data in the database.

How to verify everything is closed

Verification has to be deliberate rather than inferred from the app working:

1. List tables with RLS disabled. One query. Every such table is a potential leak.
2. Query as another user. Create a second account and try to read their data directly, bypassing the interface.
3. Query with no token at all. See what an anonymous user with the public key can read.
4. Attempt to modify somebody else’s row.
5. Search for the service key in client code and in the repository.

The practical rule: enable RLS the moment you create a table, before putting data in it. Coming back to it later means auditing every table by hand, and you will miss one.

Logic that cannot live in the client belongs in edge functions. The overview is in the Supabase guide.