Supabase Row Level Security: anyone with the key can only read
Supabase Row Level Security with a real test: what grant all does to your blog, why RLS without a policy shows nothing, and the read policy that works.
What is Supabase Row Level Security? It's a PostgreSQL feature that lets the database itself decide which rows each person may see and change in a table. With it, the key sitting in the browser can only read published posts. Are you sure nobody can quietly edit your blog? Let's check.
The posts table exists, so why can't the site read it?
In the previous post, I created the posts table for blog-mini with a migration, with constraints that block bad data. The table exists, but the Next.js page can't read it yet. The page calls Supabase with the publishable key, and the database treats every request that uses this key without signing in as the anon role.
According to Supabase's announcement, since 30 October 2026, new tables in the public schema are no longer exposed to the Data API automatically. The anon role hasn't been granted anything on posts, so the page gets a "permission denied" error.
This post grants read access properly. By the end, readers see only published posts, never drafts or scheduled posts, and can't edit or delete anything, even if they pull the key out of the browser and call the API themselves.
Why isn't the publishable key a secret?
The Next.js page reads the key from the NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY environment variable. Next.js puts every variable that starts with NEXT_PUBLIC_ straight into the JavaScript it sends to the browser. Anyone who opens DevTools, goes to the Network tab and looks at a request to Supabase can see this key in a header.
So you have to design as if the whole world has the key. Anyone can open a terminal and send a DELETE request to the posts table with your blog's own key. The only thing standing between them and your data is what the database lets the anon role do.
What happens if you grant everything to anon?
When the page says "permission denied", the fastest fix is one line: grant all on public.posts to anon. I'd guess many of you have done this, because the page works again right away. Let's see what that line costs.
To run this on your machine without a Supabase account, I use PGlite like last time, a real PostgreSQL that runs inside Node.js. Supabase creates the anon and authenticated roles for you, but PGlite doesn't, so the file below creates them. It also has an asAnon function that runs one SQL statement as anon and prints the result, like a request coming from the browser:
// A minimal Supabase-like database with PGlite (real Postgres running inside Node).
// Supabase creates the anon and authenticated roles for you; here I create them so it runs on your machine.
import { readdirSync, readFileSync } from "node:fs";
import { PGlite } from "@electric-sql/pglite";
// migrationCount: how many migration files to run (in name order), then run seed.sql.
export async function createDb(migrationCount) {
const db = new PGlite();
await db.exec("create role anon nologin; create role authenticated nologin;");
for (const file of readdirSync("supabase/migrations").sort().slice(0, migrationCount)) {
await db.exec(readFileSync(`supabase/migrations/${file}`, "utf8"));
}
await db.exec(readFileSync("supabase/seed.sql", "utf8"));
return db;
}
// Run one SQL statement as anon (a reader who isn't signed in), then switch back.
// Prints the rows returned / rows affected, or Postgres's error message.
export async function asAnon(db, label, sql) {
await db.exec("set role anon");
try {
const result = await db.query(sql);
const detail = result.rows.length > 0 ? result.rows.map((r) => Object.values(r).join(" | ")).join("; ") : `${result.affectedRows ?? 0} row${result.affectedRows === 1 ? "" : "s"} affected`;
console.log(`${label}: ${detail}`);
} catch (error) {
console.log(`${label}: ERROR ${error.message}`);
} finally {
await db.exec("reset role");
}
}
export async function countPosts(db) {
const result = await db.query("select count(*)::int as n from public.posts");
return result.rows[0].n;
}Here's the project layout. The first two migrations are from the previous post, and I copy them below so you don't have to open the old post:
04-row-level-security/
package.json
supabase/
migrations/
20261110080000_create_posts_table.sql posts table (previous post)
20261110090000_add_post_tags.sql tags column (previous post)
20261111080000_enable_rls_on_posts.sql grant + RLS + policy (this post)
seed.sql four sample posts
scripts/
fake-supabase.mjs creates the database and the anon role
grant-all.mjs the quick fix and what it costs
rls-without-policy.mjs RLS turned on with no policy
try-rls.mjs tries every operation after doing it right{
"name": "blog-mini-04-row-level-security",
"private": true,
"type": "module",
"devDependencies": {
"@electric-sql/pglite": "0.3.14"
}
}-- The blog-mini posts table. Each constraint below blocks one specific kind of bad data.
create table public.posts (
id bigint generated always as identity primary key,
slug text not null unique
constraint posts_slug_format check (slug ~ '^[a-z0-9]+(-[a-z0-9]+)*$'),
title text not null
constraint posts_title_length check (char_length(title) between 1 and 200),
description text not null default '',
body_markdown text not null default '',
status text not null default 'draft'
constraint posts_status_known check (status in ('draft', 'published')),
published_at timestamptz,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
constraint posts_published_has_date check (status = 'draft' or published_at is not null)
);
comment on table public.posts is 'Blog posts. The content is stored as Markdown.';
-- The home page lists published posts, newest first: an index for exactly that query.
create index posts_published_at_idx on public.posts (published_at desc) where status = 'published';
-- Keep updated_at current on every edit, without relying on client code.
create function public.set_updated_at()
returns trigger
language plpgsql
set search_path = ''
as $$
begin
new.updated_at := now();
return new;
end;
$$;
create trigger posts_set_updated_at
before update on public.posts
for each row execute function public.set_updated_at();-- Add tags to posts. Backward compatible: existing posts get an empty array,
-- and an older app version (which doesn't know this column) can still add posts because the column has a default.
alter table public.posts
add column tags text[] not null default '{}'
constraint posts_tags_limit check (cardinality(tags) <= 10);This time the seed file has four posts instead of two. I added a draft and a post that's finished but scheduled for 2030, because those are the two kinds of posts readers must not see:
-- Four sample posts: two published, one draft, one scheduled for the future.
insert into public.posts (slug, title, description, body_markdown, status, published_at) values
('hello', 'Hello', 'The blog''s first post.',
E'This is the first post.\n\nPosts now live in the posts table on Supabase.',
'published', '2026-11-03 08:00+07'),
('why-i-write', 'Why I write', 'Notes on what I learn at work.',
E'Writing things down helps me understand them better.\n\nAnd it saves the next person some time.',
'published', '2026-11-04 08:00+07'),
('unfinished-post', 'Unfinished post', 'Not done yet, not ready for readers.',
'Just an outline so far.', 'draft', null),
('scheduled-post', 'Scheduled post', 'Finished, scheduled for early 2030.',
'This goes live at 8:00 on 1 January 2030.', 'published', '2030-01-01 08:00+07');Now the script that tries the quick fix. It builds the database with the first two migrations, lets anon try to read, runs grant all, then plays a stranger holding the key:
// The quick fix (don't do this): the page says "permission denied", so grant everything to anon.
import { asAnon, countPosts, createDb } from "./fake-supabase.mjs";
const db = await createDb(2); // the previous post's two migrations: the posts table and the tags column, no RLS yet
await asAnon(db, "Before the grant, anon reads posts", "select slug from public.posts");
await db.exec("grant all on public.posts to anon;");
console.log("\n-- ran: grant all on public.posts to anon --\n");
await asAnon(db, "anon reads posts", "select slug, status from public.posts order by id");
await asAnon(db, "anon edits a title", "update public.posts set title = 'This blog has been edited' where slug = 'hello' returning slug, title");
await asAnon(db, "anon deletes a post", "delete from public.posts where slug = 'why-i-write'");
console.log(`\nPosts left in the table: ${await countPosts(db)}`);Before the grant, anon reads posts: ERROR permission denied for table posts
-- ran: grant all on public.posts to anon --
anon reads posts: hello | published; why-i-write | published; unfinished-post | draft; scheduled-post | published
anon edits a title: hello | This blog has been edited
anon deletes a post: 1 row affected
Posts left in the table: 3The first line is the error the site is getting now. After grant all, the site works, but look at the next three lines. A stranger can read the draft and the post scheduled for 2030. They change the first post's title to "This blog has been edited". They delete a post, and the table is down to three.
Nothing was hacked here. The database did exactly what it was told, because grant all means you may do everything.
How are grant and Row Level Security different?
PostgreSQL has two layers of access control on a table, and I find it easiest to think of them as answering two different questions.
The first layer is grant. It answers the question of which operations this role may run on the table: select, insert, update or delete. It doesn't tell rows apart. Granting select means reading every row.
The second layer is Row Level Security, or RLS. According to Supabase's RLS docs, once RLS is enabled on a table, every operation also has to pass its policies, which are SQL conditions checked on each row. A policy answers the question of which rows this role may touch.
For a blog, the answer I want is that anon may only select, and only rows with status = 'published' whose publish time has come. The first half is the job of grant, the second half is the job of a policy.
What if you enable RLS but forget the policy?
Before doing it right, I tried a very common mistake. You turn on RLS because you read somewhere that you should, grant select to anon, and load the page:
// RLS turned on but no policy yet: anon may select, but sees no rows.
import { asAnon, countPosts, createDb } from "./fake-supabase.mjs";
const db = await createDb(2);
await db.exec(`
grant select on public.posts to anon;
alter table public.posts enable row level security;
`);
await asAnon(db, "anon counts posts", "select count(*)::int as posts from public.posts");
console.log(`Posts actually in the table: ${await countPosts(db)}`);anon counts posts: 0
Posts actually in the table: 4There's no error at all. The home page loads normally, it just has no posts, while the table has all four. When a table has RLS enabled and no policy allows anything, PostgreSQL denies every row by default, and for select "deny" means acting as if the row doesn't exist.
I remember this one well because it sends you looking in the wrong place. With an empty page, you'll suspect the query, the seed data, the environment variables, all before you think of policies. If your Supabase page returns an empty array while the table clearly has data, check the policies first.
The second line shows one more thing. countPosts runs as PGlite's default role, which owns the table, and it still counts four posts. RLS doesn't apply to the table owner or a superuser. The same goes for Supabase's secret key (previously called service_role): it bypasses RLS entirely. That's why the secret key must only live on the server and must never carry the NEXT_PUBLIC_ prefix.
How do you write a policy for readers?
This is the project's third migration, created with npx supabase migration new enable_rls_on_posts:
-- Readers (the anon role) may only READ posts that are published and whose publish time has come.
-- Layer 1: grant decides which role may do which operation on the table.
revoke all on public.posts from anon, authenticated;
grant select on public.posts to anon, authenticated;
-- Layer 2: Row Level Security decides which ROWS that role can touch.
alter table public.posts enable row level security;
create policy "Anyone can read published posts"
on public.posts for select
to anon, authenticated
using (status = 'published' and published_at <= now());The revoke all line comes first so the migration gives the same result no matter what was granted before. If your project once ran grant all to put out a fire, this line cleans it up.
The policy has four parts. for select says it applies to reads. to anon, authenticated says it applies to both signed-out and signed-in users, because signed-in users are readers too. using (...) is the condition checked on each row, and a row is visible when it evaluates to true.
The condition has two halves, and the second is easy to forget. status = 'published' removes drafts. published_at <= now() removes scheduled posts, meaning posts the author has published with a time in the future. Without it, a post scheduled for 8:00 tomorrow morning can be read tonight by anyone who calls the API directly, even though the home page doesn't show it yet.
I don't write any policy for insert, update or delete. That's on purpose. No policy for an operation means that operation is denied on every row.
The real Tech Notes policy is almost identical. This is from the repo's 20261103141232_create_posts.sql, written on 3 November 2026:
alter table public.posts enable row level security;
create policy "Anyone can read published posts"
on public.posts for select
to anon, authenticated
using (status = 'published' and published_at <= now());
-- New tables are not exposed to the Data API automatically (Supabase change, enforced 2026-10-30),
-- so grant exactly what each role needs. RLS above still decides which rows they see.
revoke all on public.posts from anon, authenticated;
grant select on public.posts to anon;The real file has four more policies for the author, which I'm saving for the sign-in part.
Trying every operation as anon
I don't trust a policy until I've tested it. The next script runs all three migrations, then plays a stranger doing five things: reading the list, asking for the draft directly by slug, editing, deleting and adding a post. Then it assumes that one day someone accidentally runs grant all again, and tries everything once more:
// Run all three migrations (with RLS and the policy), then try every operation as anon.
import { asAnon, countPosts, createDb } from "./fake-supabase.mjs";
const db = await createDb(3);
console.log(`The posts table has ${await countPosts(db)} posts: 2 published, 1 draft, 1 scheduled for 2030.\n`);
await asAnon(db, "anon reads posts", "select slug from public.posts order by published_at desc");
await asAnon(db, "anon asks for the draft directly", "select count(*)::int as rows from public.posts where slug = 'unfinished-post'");
await asAnon(db, "anon edits a title", "update public.posts set title = 'This blog has been edited' where slug = 'hello'");
await asAnon(db, "anon deletes a post", "delete from public.posts where slug = 'why-i-write'");
await asAnon(db, "anon adds a post", "insert into public.posts (slug, title) values ('stranger-post', 'Stranger post')");
// Suppose someone accidentally grants everything to anon again one day. RLS is still there.
await db.exec("grant all on public.posts to anon;");
console.log("\n-- accidentally ran: grant all on public.posts to anon --\n");
await asAnon(db, "anon reads posts", "select slug from public.posts order by published_at desc");
await asAnon(db, "anon edits a title", "update public.posts set title = 'This blog has been edited' where slug = 'hello'");
await asAnon(db, "anon deletes a post", "delete from public.posts where slug = 'why-i-write'");
await asAnon(db, "anon adds a post", "insert into public.posts (slug, title) values ('stranger-post', 'Stranger post')");
const title = await db.query("select title from public.posts where slug = 'hello'");
console.log(`\nPosts left: ${await countPosts(db)}. Title of the hello post: ${title.rows[0].title}`);The posts table has 4 posts: 2 published, 1 draft, 1 scheduled for 2030.
anon reads posts: why-i-write; hello
anon asks for the draft directly: 0
anon edits a title: ERROR permission denied for table posts
anon deletes a post: ERROR permission denied for table posts
anon adds a post: ERROR permission denied for table posts
-- accidentally ran: grant all on public.posts to anon --
anon reads posts: why-i-write; hello
anon edits a title: 0 rows affected
anon deletes a post: 0 rows affected
anon adds a post: ERROR new row violates row-level security policy for table "posts"
Posts left: 4. Title of the hello post: HelloThe top half is the correct state. The stranger sees only the two published posts. Asking for the draft directly by slug returns 0 rows, exactly as if the post didn't exist, so they can't even find out which drafts the blog has. The three writes are blocked at the grant layer with "permission denied".
The bottom half is why I want both layers. After someone accidentally grants everything again, the grant layer no longer blocks anything, but RLS is still there. The edit and delete don't raise an error; they just affect 0 rows, because no policy lets anon see any row to edit or delete. Adding a post is rejected outright. The last line confirms the table still has four posts and the title is still "Hello".
Compare it with the output of grant-all.mjs at the top of the post. Same grant all, but this time nothing was lost.
How does the Next.js page read posts?
With the policy in place, the page only needs to call Supabase with the publishable key. This is the core of the post-loading function in Tech Notes, from src/lib/posts-source.ts. I've left out the language selection and the sample-data fallback, since they don't matter for this post:
const { data, error } = await createPublicClient(env)
.from("posts")
.select(POST_META_COLUMNS)
.eq("status", "published")
.lte("published_at", new Date().toISOString())
.order("published_at", { ascending: false });
if (error) throw new Error(`Could not load posts from Supabase: ${error.message}`);createPublicClient creates a Supabase client from the project URL and the publishable key, with no signed-in session, so every query runs as anon. POST_META_COLUMNS is a string listing the columns to fetch for lists, everything except the article body.
You might ask why the code still filters on status and published_at when the policy already does. I keep both because they serve two purposes. The filter in the code states what the page intends and lets PostgreSQL use the posts_published_at_idx index. The policy is the safety net for when someone forgets the filter, or calls the API directly without going through my code.
I can't run this part with PGlite, because it needs a real Supabase project. But you can check it indirectly in this very repo: the Tech Notes content scripts read the post list with the same publishable key, and they only see published posts. To read drafts, a script has to sign in with an author account first.
So how does the author write posts?
The blog is now safe from strangers. But look back at the output of try-rls.mjs and you'll see it's safe from everyone, including the author. No role can add or edit posts through the API. Right now, to publish a new post I have to go to the Dashboard or write another migration, which is exactly what I wanted to get away from when I moved posts out of the code.
What's missing is a way for the database to know "this request comes from the author". That needs sign-in, plus policies that let the authenticated role write, but only when the signed-in person really is the author and not just anyone who managed to create an account. That's the subject of the next post. You can follow the whole series at Build a blog with Next.js + Supabase, and if you're preparing for interviews, the database questions are in the interview question bank.