Protect data with row-level security

Why you need it

Your publishable key sits inside your app, so anyone can copy it. If your tables are open, anyone with the key can read and change every row. Row-level security (RLS) fixes this inside the database: each request runs as a specific user, and policies decide which rows that user can see and change.

Remember the three states: a table with RLS off is fully open to publishable-key requests. With RLS on and no policies, it is fully closed. Policies are the rules in between.

Make a table that belongs to users

You will use a notes table. In Databases, open the SQL Editor and run:

CREATE TABLE notes (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  body text NOT NULL,
  created_at timestamptz DEFAULT now()
);

The important column is user_id. Your app stores the signed-in user's id there when it creates a row.

Turn on RLS

1. Open Databases, then RLS Policies.
2. Find notes. It shows RLS DISABLED in red.
3. Choose Enable RLS. The badge changes to RLS ENABLED.

The table is now closed to your app until you add policies.

Add policies

Choose + New Policy and create these:

1. Name notes_select_own, Command SELECT, Template "Users can only access their own rows". The form fills USING expression with user_id = auth.uid(). Choose Create Policy.
2. Name notes_insert_own, Command INSERT, same template. WITH CHECK expression user_id = auth.uid() stops users inserting rows for someone else.
3. Name notes_update_own, Command UPDATE, same template.
4. Name notes_delete_own, Command DELETE, same template.

auth.uid() is the id of the signed-in user. auth.role() is authenticated for any signed-in user, which is what the Any signed-in end-user template uses. Public read access is true, for data anyone may read.

Prove it works

1. In Authentication, create two users with + Create User, or use the Playground. Note their ids.
2. Insert one note for each, with their id in user_id.
3. Sign in as the first user and read notes with the publishable key and that user's token:

await pgb.auth.signIn({ email: "[email protected]", password });
const mine = await pgb.table("notes").find({});
console.log(mine.length); // only Ana's notes

You should see only Ana's rows. Without a signed-in user, the same request runs as an anonymous visitor and sees nothing, because no policy allows it.

1. Try to insert a note with the other user's id. The policy refuses it.

Under the hood your app sends the publishable key plus the user's token in the X-PGB-User-Token header, and the SDK does this once you have signed in.

Mistakes to avoid

• Testing with your secret key or in the SQL Editor. Those run with your own access and are not limited by end-user policies. Test with a publishable key and a user token.
• Leaving RLS off "until later". Do it before real data arrives.
• Trusting a value users type for their own role. Store roles on your server side.

Recap

• RLS makes the database enforce who sees which rows.
• Enable it per table, then add policies with + New Policy and a template.
• auth.uid() and auth.role() read the signed-in user's identity.
• Test as a real end user with a publishable key and a token.