Auth & Row-Level Security

Two questions that must stay separate

Authentication asks “Who is making this request?” A valid sign-in session identifies a user. Authorisation asks “May that user perform this operation on this record?” Signing in does not give someone permission to read another person's private data.

Imagine a private project notebook. Ari owns two notes and Bo owns one. Ari should see two notes, not all three. Ari must also be prevented from creating a note labelled as Bo's or transferring an existing note to Bo by editing an ownership field. These are different operations that need different checks.

Browser controls are not permission boundaries

Someone can unhide a button, alter localStorage, change the request body or call an endpoint without using your page. A disabled “Delete” button improves the interface but cannot secure the delete request. Put security checks in the server and database, where the caller cannot change them.

A user ID is an identifier, not a password. Knowing Bo's ID must not grant access to Bo's notes. The verified session identifies the caller; an editable user_id in a form does not.

What RLS does—and what it does not do

PostgreSQL row-level security applies policies to rows for ordinary roles. With RLS enabled and no applicable policy, ordinary access is denied. For SELECT, disallowed rows are generally filtered out: an empty result is not necessarily an empty table. For INSERT or an ownership-changing UPDATE, a policy check can reject the operation.

Important correction to a common overstatement: not every database connection is subject to the same policies. Privileged database roles, table owners in some contexts, and service-role credentials can bypass RLS. Never send those credentials to a browser. Moving a privileged client to a server does not magically make its operations safe; that server must still verify callers and enforce permissions.

The public/publishable project key identifies the project. It is designed for restricted clients and does not bypass RLS. A signed-in session supplies the user's identity to the request. Signing in on an unrelated client does not automatically attach that session to every server query.

Follow one policy through a table

The following is a learning example for your own disposable database, not a change to StackFoundry's database. Run it only in a project you control. This private example requires your own app to have working sign-in before it can insert or read notes:

CREATE TABLE public.practice_notes (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  body text NOT NULL
);

REVOKE ALL ON public.practice_notes FROM PUBLIC, anon, authenticated;
GRANT SELECT, INSERT, UPDATE ON public.practice_notes TO authenticated;
GRANT ALL ON public.practice_notes TO service_role;
ALTER TABLE public.practice_notes ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Read own practice notes"
ON public.practice_notes FOR SELECT TO authenticated
USING ((SELECT auth.uid()) = user_id);

CREATE POLICY "Create own practice notes"
ON public.practice_notes FOR INSERT TO authenticated
WITH CHECK ((SELECT auth.uid()) = user_id);

CREATE POLICY "Edit own practice notes"
ON public.practice_notes FOR UPDATE TO authenticated
USING ((SELECT auth.uid()) = user_id)
WITH CHECK ((SELECT auth.uid()) = user_id);

GRANT permits a role to attempt an operation on the table; the RLS policy then limits which rows that operation may touch. Both gates matter. We did not grant anonymous access or DELETE because this exercise does not need them.

auth.uid() is the current authenticated user's ID in the database request context. For an anonymous caller it is null; null does not match an owned row. It is not the user ID someone typed into a form.

• USING checks an existing row: “Is this row yours to read or edit?”
• WITH CHECK checks a proposed new row: “Will the inserted/updated row still belong to you?”
• UPDATE must check both the existing row and its proposed replacement. This example writes both clauses explicitly for clarity; PostgreSQL uses USING as WITH CHECK when an UPDATE policy omits WITH CHECK. An overly permissive explicit WITH CHECK can still allow an unsafe ownership change.

For an INSERT from a signed-in client, use the authenticated user's ID. A missing ownership value violates NOT NULL; a different user's ID fails WITH CHECK. Do not “fix” either problem by disabling RLS.

Trace three attempts before testing

Database-owner tools can see more than an ordinary user and may bypass these restrictions. A successful SQL-editor query does not prove your app's permissions work. Test through ordinary signed-in clients with two accounts in your own practice app, not StackFoundry accounts and not privileged clients.

Rehearse a genuine access test

1. Sign in as practice user A and insert a harmless note owned by A. Read it back.
2. Sign out and confirm the private note is unavailable.
3. Sign in as practice user B and confirm A's note is unavailable. Create B's own note and read that back.
4. Try to insert with A's ID while signed in as B. Expect rejection.
5. Try changing B's row ownership to A. Expect rejection and confirm the original row is unchanged.

Use the service's documented auth setup and session-aware client for your framework. Do not log session tokens or paste credentials into the course. If you have not built sign-in yet, this is a policy reasoning exercise; do not claim you have tested private access.

How this relates to the products activity

Activity 4.2 starts with a disposable, non-sensitive products playground. Its intentionally public read/write policy is shown in the database lesson so beginners can practise a real read and insert without inventing authentication plumbing. That playground is not a secure shop and must not contain private data or be left as a production write service.

When moving to account-owned products, add a verified sign-in flow and an ownership column, then apply the private-row pattern above. Stop anonymous writes and derive ownership from the authenticated session. A secure production design also needs abuse limits and business checks; RLS alone does not validate a price or make a checkout trustworthy. These distinctions are essential even if your assessment checks only the practice integration.

Investigate instead of opening everything

If a read returns an empty array, check whether rows exist, whether the client has a session and whether the owner IDs match. If it reports permission denied, inspect table grants. If an insert violates RLS, inspect the session identity and the proposed ownership value. These are different failures; granting everyone all rows hides the problem rather than solving it.

RLS controls rows, not a guarantee that every column in an allowed row is safe to expose. Do not store passwords, private keys or privileged role flags in a broadly readable profile. Authoritative roles belong in separately protected data, never user-editable metadata or localStorage.

Before the security quiz

Explain authentication versus authorisation, grants versus row policies, USING versus WITH CHECK, and why a publishable key differs from a privileged key. Predict the three attempts in the table without running them, then compare your predictions with a real ordinary-user test when your practice app has auth. The aim is to reason about the boundary, not just recognise the abbreviation RLS.