At a Glance
all 49 → 40
Columns anon could read
raw ID-document OCR
Worst exposed field
9
Gated columns relocated behind auth
4 routes
Readers converted to service-role
2
Latent bugs caught pre-ship
What Broke
OnlineMihna's public jobseeker profiles are supposed to gate the sensitive bits — phone / WhatsApp, the six social URLs, the portfolio link — so only paid (or trial) employers can see them. The app did gate them: the server component and the public API simply didn't render those fields for un-privileged viewers.
The problem is that the gating was only in the rendering code. The underlying Postgres table, public.jobseekers, granted SELECT on every column to the anon and authenticated roles, and its row-security policy was USING (true). Supabase exposes that table over PostgREST, and the anon API key ships in every browser bundle. So a logged-out visitor who simply asked the database directly —
GET /rest/v1/jobseekers?select=phone,linkedin_url,id_proof_ocr_data
Apikey: <public anon key>
— got back every jobseeker's phone, all their social profiles, their portfolio URL, and worse: id_proof_ocr_data, the raw text extracted from their uploaded government ID document. The 'paywall' was a sticker over a door that was never locked.
Nothing in the UI exposed this — you had to know to query the table directly — so it had sat latent in production. It surfaced not from an alert but from a routine SET ROLE anon; SELECT ... sanity check while planning an unrelated UI consolidation.
Root Cause
Three things compounded.
1. RLS is row-level, not column-level. Row-Level Security decides which rows a role can see; it has no say over which columns. A USING (true) SELECT policy means 'every row is visible' — and with a table-level GRANT SELECT, that means every column of every row. Postgres's only mechanism for restricting columns is the column-level GRANT itself, which is completely separate from RLS.
2. App-layer stripping is cosmetic against a directly-queryable table. The team had been hiding fields by leaving them out of the server select(...) strings. That stops the app's own routes from leaking — but PostgREST lets a client pick its own column list. The protection was real for the front door and absent for the side door.
3. A confident, wrong comment. The public profile page carried a comment: // Anon key is blocked by RLS policies on jobseekers. It wasn't. has_table_privilege('anon','public.jobseekers','SELECT') returned true; has_column_privilege('anon', ..., 'phone', 'SELECT') returned true. The page used the service-role key for an unrelated reason, and the comment rationalized it with a false premise that masked the hole for everyone who read the file afterward.
The correct fix is a column-allowlist grant: revoke the table-level SELECT from anon/authenticated and re-grant SELECT on only the public columns, omitting the gated ones. Crucially, you cannot do this with a column-level REVOKE — a pre-existing table-level grant implies all columns and a column REVOKE is a no-op against it. You must revoke the table grant first, then grant the explicit allowlist. phone stays physically on the table (the profile-quality-score trigger reads it), so only the grant changes.
Timeline
A sanity probe shows the gate is cosmetic
While scoping a contact-card UI feature, a read-only audit ran
has_column_privilege('anon','public.jobseekers','phone','SELECT')→ true, andSET ROLE anon; SELECT count(*) FROM ...confirmed theanonrole could readphone, all six socials,portfolio_url,quality_score, andid_proof_ocr_data. The leak was broader than the contact fields — it included raw ID-verification data — and the page comment claiming 'anon is blocked by RLS' was false.Column-allowlist migration + service-role reader conversion
Migration
079revokes the table-levelSELECTfromanon/authenticatedand re-grants only the 40 public columns. The app's own routes that need the gated columns (owner profile GET/POST/PUT read-backs,/api/profile/status, the CV builder) were switched to the service-role admin client with retained ownership checks; public routes now return presence flags only.Caught a dead-on-arrival RLS policy before apply
The new
contact_access_logsuperadmin SELECT policy had been mirrored from an existing audit table's inlineEXISTS (SELECT 1 FROM auth.users ...). Becauseauthenticatedcannot readauth.users, that subquery raises42501for every caller — the policy never works. Replaced withusing ( public.is_superadmin() )(aSECURITY DEFINERhelper that readsauth.usersas its owner). The original policy carries the same latent bug; logged to AUDIT, not touched (shipped-migration rule).Migration 079 applied to production
Applied via the Supabase SQL editor (manual apply is the repo's migration convention).
get_advisorsreturned no new findings. No git timestamp for the apply step — it happened after the final code commit; left null intentionally.Post-migration smoke + execution review GREEN
Direct anon-key query on
jobseekersnow errors;has_column_privilege('anon', ..., 'phone')→ false. The 'no 500s anywhere' smoke (authed owner save, CV generation, applicant modal, profile-status) passed — confirming no gated-column reader was missed. Independent execution review returned GREEN. No git timestamp; left null.
The Fix
Move the gate from the render layer into the data layer, then give privileged viewers an authorized path back to the values.
The core fix: RLS can't restrict columns, so switch anon/authenticated from a table-level SELECT grant to a column allowlist. A column-level REVOKE would be a no-op against the table-level grant — you must revoke the table grant first, then grant the explicit list. before_code is the prior live grant (verified via has_table_privilege; it was never in a tracked migration file).
-- prior live grant (verified via has_table_privilege; not in any tracked file):
grant select on public.jobseekers to anon, authenticated;
-- + jobseekers_select_public USING (true) → every column of every row, public.revoke select on public.jobseekers from anon, authenticated;
grant select (
id, user_id, avatar_url, name, job_title, description, country, address,
intro_video_url, age, gender, verified, id_proof_verified,
verification_percentage, experience_history, education_history, profile_cv_id
/* ...40 public cols total... */
) on public.jobseekers to anon, authenticated;
-- OMITTED (gated): phone, portfolio_url, linkedin_url, twitter_url,
-- facebook_url, instagram_url, github_url, tiktok_url, quality_score
notify pgrst, 'reload schema'; -- so the grant change is immediate, not ~10 min laterThe dead-on-arrival policy, caught before apply. The inline EXISTS over auth.users (copied from a sibling audit table) raises 42501 because authenticated has no SELECT on auth.users. The SECURITY DEFINER helper reads it as its owner and actually works.
create policy "contact_access_log_superadmin_select"
on public.contact_access_log for select
to authenticated
using (
exists (
select 1 from auth.users
where auth.users.id = (select auth.uid())
and (auth.users.raw_user_meta_data->>'user_type') = 'superadmin'
)
); -- 42501: authenticated cannot read auth.users → dead for every callercreate policy "contact_access_log_superadmin_select"
on public.contact_access_log for select
to authenticated
using ( public.is_superadmin() ); -- SECURITY DEFINER: reads auth.users as ownerAfter the grant change, the owner's own profile read would 42501 on the authed client (it selects the gated columns to prefill the edit form). Convert that read to the service-role admin client, keeping the getUser() + eq(user_id) ownership guard as the sole gate. Same conversion applied to the POST/PUT read-backs, /api/profile/status, and the CV builder.
const supabase = await createClient(); // authed (RLS-bound) — will 42501 on phone/socials
const { data: profile } = await supabase
.from("jobseekers")
.select("... phone, linkedin_url, portfolio_url, ...")
.eq("user_id", userId)
.single();// Service-role: gated columns are no longer SELECTable by authenticated after 079.
// Ownership is enforced by getUser() + .eq("user_id", userId), not RLS.
const adminForRead = createAdminClient();
if (!adminForRead) return NextResponse.json({ error: "server_configuration_error" }, { status: 500 });
const { data: profile } = await adminForRead
.from("jobseekers")
.select("... phone, linkedin_url, portfolio_url, ...")
.eq("user_id", userId)
.single();Migration 079 flips anon/authenticated to a 40-column allowlist (the 9 gated columns omitted) and reloads PostgREST's schema cache in-transaction so the change is immediate. The app's own routes that legitimately need the gated columns — the owner's profile read/save, the CV builder, the profile-quality input — were converted to the service-role client (which keeps getUser() + an eq(user_id) ownership check). Public payloads now carry only boolean presence flags (has_phone, …). A new audited endpoint, POST /api/employer/jobseeker/[userId]/contact, serves the real values to owners, paid/trial employers, a candidate's job-owner, or a superadmin — mirroring the existing CV-reveal pattern. The scattered phone/social/CV UI collapsed into one locked/unlocked 'Contact & Links' card.
Verification
All checks run against the live project after Belal applied migration 079.
Anon can no longer read the gated columns
select has_column_privilege('anon','public.jobseekers','phone','SELECT')→ false (and same for the 6 socials, portfolio_url, quality_score);... 'name' ...→ true.Direct anon-key PostgREST query is rejected
An anon-key
GET /rest/v1/jobseekers?select=phone,linkedin_urlreturns a permission error immediately — the in-transactionnotify pgrstreload means there's no stale-cache window.Public payload carries presence flags only
The public profile page and
GET /api/jobseeker/profile/[userId]returnhas_phone/has_*booleans and no raw values for any viewer; the PIIconsole.logof verification fields was removed.No 500s anywhere (the missed-reader backstop)
Authed owner save (POST + PUT), owner CV generation, the applicant modal, and
/api/profile/statusall return 200 post-migration — proving every gated-column reader was converted, since an unconverted one would 42501.Profile-quality scoring untouched
phonestays physically on the table, socompute_jobseeker_quality_scoreand itsUPDATE OF phonetrigger are unaffected; only the SELECT grant changed.
Lessons Learned
RLS hides rows, not columns. A `USING(true)` policy plus a table-level grant is `SELECT *` for the public internet.
- RLS is not column security. If a column must not reach a role, that's a
GRANTconcern, not a policy concern. Reach for column-level grants (or a separate RLS-locked table) — never assume a row policy protects a column. - App-layer field-stripping is cosmetic the moment the table is reachable over PostgREST with a client-chosen
select. Verify the data layer, not the route. - Don't trust the comment — run the probe.
has_column_privilege(role, table, col, 'SELECT')andSET ROLE anon; SELECT ...are two-second truths. The file said anon was blocked; the database said otherwise. - A revoked-column read RAISES
42501, it does not return NULL. An early draft of this fix assumed a missing column grant would silently null the field; an empiricalSET ROLE anontest proved it errors hard. That's actually a gift: a reader you forgot to convert fails loudly with a 500 the instant the migration lands, so the post-migration smoke ('no 500s anywhere') becomes the real backstop. - Mirroring a 'known-good' production policy can propagate a latent bug. The new audit table's superadmin policy was copied from an existing one that used an inline
EXISTS (SELECT 1 FROM auth.users ...). Butauthenticatedcan't readauth.users, so that subquery raises42501for every caller — the policy is dead on arrival. Caught it before apply and switched to theSECURITY DEFINERis_superadmin()helper. The source policy it was copied from has the same bug, still latent in prod.