Protect rows with row-level security

Row-level security (RLS) is a Postgres feature that lets the database itself decide which rows each person can read or change. The Console gives you a page to turn it on per table and write policies, and your project's sign-in provides the identity those policies use.

Why it matters

Your publishable key ships inside your app, so anyone can extract it. A table with RLS off is fully open to publishable-key requests. A table with RLS on and no policies is fully closed. Policies are the rules in between.

Turn RLS on for a table

1. In your project, open Databases and then RLS Policies.
2. Find your table. A badge shows RLS ENABLED or RLS DISABLED.
3. Choose Enable RLS. (The same button says Disable RLS when it is on.)

Add a policy

1. On the table, choose + New Policy.
2. Enter a Policy Name, for example notes_select_own.
3. Choose the Command: ALL, SELECT, INSERT, UPDATE or DELETE.
4. Pick a Template, or choose Custom and write your own:
   • Users can only access their own rows: user_id = auth.uid()
   • Any signed-in end-user: auth.role() = 'authenticated'
   • Public read access: true
5. Fill in USING expression (which existing rows the policy applies to) and, if needed, WITH CHECK expression (validates new and updated rows). At least one of the two is required.
6. Choose Create Policy.

Policies appear in a list under the table, each with a Delete button.

The identity helpers

Inside a policy you can use:

• auth.uid(): the signed-in user's id
• auth.role(): authenticated for a signed-in user
• auth.jwt(): the user's token claims

For the template above to work, the table needs a user_id column that stores the user's id, which your app fills in when it creates the row.

How the user's identity reaches the database

Your app signs the user in, then sends requests with your publishable key and the user's access token in the X-PGB-User-Token header. The project checks that the token is valid and the session has not been revoked, and the database evaluates your policies as that user. A publishable-key request with no user token runs as an anonymous visitor, so only policies that allow that apply.

Test it

1. Create two users in Authentication (or with Auth Playground).
2. Insert a row for each, with their own id in user_id.
3. Sign in as one and read the table with that user's token. You should see only their row.

Good to know

• Requests made with your secret key have full access to your data and are not limited by these policies the way end-user requests are, so keep that key on your server. See API keys.
• Test policies with a publishable key and a signed-in user's token. The SQL Editor and the Table Editor run as you, not as an end user.
• Add policies before you put real data in a table, not afterwards.