Row-Level Security, Conceptually

Build the mental model

Even if every API endpoint correctly checks permissions, what happens if one has a bug, a new endpoint forgets the check, or someone queries the database directly? Application-level authorization lives entirely in code a mistake can bypass.

Row-level security moves part of that enforcement into the database itself, as a policy attached to a table, so the database refuses to return rows a query is not allowed to touch — regardless of which code path asked.

This lesson stays conceptual. PostgreSQL's roles-privileges-and-row-security lesson and the Cloud Providers & Platforms course's Supabase lesson both cover the real, hands-on policy syntax.

Row-Level Security

A database feature where access-control policies are attached directly to a table, so the database itself filters which rows a given connection can see or modify, based on who that connection is — independent of and in addition to application-level checks.

ROW-LEVEL SECURITY POLICY LAYER
-------------------------------
NOTES TABLE (raw rows)          RLS POLICY LAYER
+----+-------+-----------+      (owner = current_user)
| id | owner | text      |
+----+-------+-----------+
| 1  | alice | "shopping"|  ---> User A (alice) queries:
| 2  | bob   | "taxes"   |       sees only row 1, 3
| 3  | alice | "recipe"  |
| 4  | carol | "diary"   |  ---> User B (bob) queries:
+----+-------+-----------+       sees only row 2

              Same table, same query shape --
              the policy layer decides what
              each connection is allowed back.

Connect it to a real scenario

Row-level security is most valuable exactly where application-level authorization is easiest to get wrong: multi-tenant apps, user-generated content, and any table where 'my own data' must stay strictly separate from everyone else's.

The code below simulates this outside any real database: given rows tagged with an owner and a 'currently connected' user, it filters down to only the rows that user should see — a miniature of what a real RLS policy enforces automatically.

• Row-level security lets the database itself enforce that each user only sees or edits their own rows, independent of application code.
• Authentication alone does not produce row-level enforcement — a deliberate policy tying identity to row ownership is required.
• RLS backs up application-level authorization rather than replacing it; keeping both layers protects against bugs in either one.

Try the working example

// Simulates what a database row-level-security policy would enforce:
// each connecting user only ever sees rows they own.
function applyRowLevelSecurity(rows, currentUser) {
  return rows.filter((row) => row.owner === currentUser);
}

const notes = [
  { id: 1, owner: "alice", note: "Alice's private note" },
  { id: 2, owner: "bob", note: "Bob's private note" },
  { id: 3, owner: "alice", note: "Alice's second note" },
  { id: 4, owner: "carol", note: "Carol's private note" }
];

console.log("alice sees:", applyRowLevelSecurity(notes, "alice"));
console.log("bob sees:", applyRowLevelSecurity(notes, "bob"));

Filtering the four-row notes table for "alice" returns her two rows (ids 1 and 3), and filtering for "bob" returns only his one row (id 2) — carol's row never appears for either user, exactly mimicking how a real row-level-security policy would scope each connection's results.

Wikipedia: Row-level security — How Databases Work