Security & RLS

AdRecon uses Supabase Row-Level Security (RLS) to enforce user isolation and access control. All tables have RLS enabled, and policies restrict access based on auth.uid() and app_metadata.user_type.

Core Principles

Authenticated-only access — Anonymous users have no access to ads, saved data, or feeds
User isolation — Users can only read/write their own saved ads, projects, and project links
Service role for admin operations — Admin endpoints use the Supabase service role key to bypass RLS
Admin-only tables — Fanbasis integration tables and webhook logs are restricted to admin users

apify_ads_raw

RLS Enabled: Yes

Policies

Authenticated can read ads raw

Operation: SELECT
Role: authenticated
Policy: using (true)All authenticated users can read all ads. No user-scoping on the raw table.

Revocations

revoke all on public.apify_ads_raw from anon;

Anonymous users cannot access the raw ad table.

The ads_feed_v2 view uses security_invoker = true, so this policy applies when querying the view as well.

user_saved_ads

RLS Enabled: Yes

Policies

Users can read own saved ads

Operation: SELECT
Role: authenticated
Policy: using (auth.uid() = user_id)Users can only see their own saved ads.

Users can create own saved ads

Operation: INSERT
Role: authenticated
Policy: with check (auth.uid() = user_id)Users can only insert rows where user_id = auth.uid().

Users can update own saved ads

Operation: UPDATE
Role: authenticated
Policy: using (auth.uid() = user_id) and with check (auth.uid() = user_id)Users can only update their own saved ads and cannot change user_id to another user.

Users can delete own saved ads

Operation: DELETE
Role: authenticated
Policy: using (auth.uid() = user_id)Users can only delete their own saved ads.

Revocations

revoke all on public.user_saved_ads from anon;

user_projects

RLS Enabled: Yes

Policies

Users can read own projects

Operation: SELECT
Role: authenticated
Policy: using (auth.uid() = user_id)

Users can create own projects

Operation: INSERT
Role: authenticated
Policy: with check (auth.uid() = user_id)

Users can update own projects

Operation: UPDATE
Role: authenticated
Policy: using (auth.uid() = user_id) and with check (auth.uid() = user_id)

Users can delete own projects

Operation: DELETE
Role: authenticated
Policy: using (auth.uid() = user_id)

Revocations

revoke all on public.user_projects from anon;

user_saved_ad_projects

RLS Enabled: Yes

Policies

Users can read own saved ad project links

Operation: SELECT
Role: authenticated
Policy: using (auth.uid() = user_id)

Users can create own saved ad project links

Operation: INSERT
Role: authenticated
Policy: with check (auth.uid() = user_id)

Users can update own saved ad project links

Operation: UPDATE
Role: authenticated
Policy: using (auth.uid() = user_id) and with check (auth.uid() = user_id)

Users can delete own saved ad project links

Operation: DELETE
Role: authenticated
Policy: using (auth.uid() = user_id)

Foreign Key Enforcement

RLS policies alone don’t prevent malicious project ID injection. The table’s foreign keys enforce ownership:

foreign key (project_id, user_id) references user_projects(id, user_id) on delete cascade

If a user tries to insert a project link with a project_id they don’t own, the foreign key constraint fails before RLS even evaluates.

Revocations

revoke all on public.user_saved_ad_projects from anon;

fanbasis_enabled_offers

RLS Enabled: Yes

Policies

Authenticated can read fanbasis_enabled_offers

Operation: SELECT
Role: authenticated
Policy: using (true)All authenticated users can see which offers are enabled (needed for admin UI).

Admins can insert fanbasis_enabled_offers

Operation: INSERT
Role: authenticated
Policy: with check ((auth.jwt() -> 'app_metadata' ->> 'user_type') = 'admin')

Admins can update fanbasis_enabled_offers

Operation: UPDATE
Role: authenticated
Policy: using ((auth.jwt() -> 'app_metadata' ->> 'user_type') = 'admin')

Admins can delete fanbasis_enabled_offers

Operation: DELETE
Role: authenticated
Policy: using ((auth.jwt() -> 'app_metadata' ->> 'user_type') = 'admin')

Revocations

revoke all on public.fanbasis_enabled_offers from anon;

fanbasis_webhook_log

RLS Enabled: Yes

Policies

Admins can read fanbasis_webhook_log

Operation: SELECT
Role: authenticated
Policy: using ((auth.jwt() -> 'app_metadata' ->> 'user_type') = 'admin')Only admin users can view webhook logs.

Revocations

revoke all on public.fanbasis_webhook_log from anon;

The webhook endpoint (/api/fanbasis/webhook) uses the service role key to insert logs, bypassing RLS.

page_rip_log

RLS Enabled: Yes

Policies

None. This table is service-role only. No policies defined for authenticated or anon.

Access Pattern

The Page Ripper API (/api/download-page) uses the Supabase service role key to:

Query recent captures for rate-limit enforcement
Insert new capture log entries

Regular authenticated users cannot query or insert into this table directly.

Admin vs. Member Access

Admin Detection

AdRecon uses auth.users.raw_app_meta_data.user_type to distinguish admins:

(auth.jwt() -> 'app_metadata' ->> 'user_type') = 'admin'

This field is set via:

Migration 20260224212000_set_admin_user_type.sql for initial admin accounts
Admin API (/api/admin/users) for subsequent role changes

Admin-Only Operations

Database-level:

Insert/update/delete on fanbasis_enabled_offers
Read fanbasis_webhook_log

API-level (enforced in serverless functions):

GET /api/admin/users — list all users
PATCH /api/admin/users — change user roles
DELETE /api/admin/users — delete users
POST /api/admin/users — send password reset emails
GET /api/admin/fanbasis — Fanbasis integration status, offers, logs
POST /api/admin/fanbasis — test connection, sync products, register webhook, toggle offers

Frontend Gating

The React app (src/App.tsx) checks user.user_metadata.user_type and renders:

<AdminDashboard /> for admins on /admin or /app/admin
<CenteredMessage> (access denied) for non-admins on admin routes
<Dashboard /> for all users on main routes

Service Role Operations

The service role key (SUPABASE_SERVICE_ROLE_KEY) bypasses RLS entirely. It’s used in:

Admin API endpoints (/api/admin/users, /api/admin/fanbasis)
- Query auth.users via Supabase Auth Admin API
- Query fanbasis_enabled_offers, fanbasis_webhook_log
- Update user metadata
Fanbasis webhook (/api/fanbasis/webhook)
- Insert into fanbasis_webhook_log
- Create/update auth.users on payment events
Page Ripper (/api/download-page)
- Query page_rip_log for rate-limit enforcement
- Insert new capture entries

Never expose the service role key to the frontend. It grants full database access and bypasses all RLS policies.

Authenticated vs. Anonymous

Role	Can Access Feeds	Can Save Ads	Can Create Projects	Can View Admin Pages
`anon`	No	No	No	No
`authenticated` (member)	Yes	Yes	Yes	No
`authenticated` (admin)	Yes	Yes	Yes	Yes

Revocations Summary

All tables revoke permissions from anon:

revoke all on public.ads_feed_v2 from anon;
revoke all on public.apify_ads_raw from anon;
revoke all on public.user_saved_ads from anon;
revoke all on public.user_projects from anon;
revoke all on public.user_saved_ad_projects from anon;
revoke all on public.user_saved_ads_feed_v1 from anon;
revoke all on public.user_project_ads_feed_v1 from anon;
revoke all on public.fanbasis_enabled_offers from anon;
revoke all on public.fanbasis_webhook_log from anon;

Anonymous users are forced to the Auth component in src/App.tsx and cannot query any data.

RPC Function Security

All RPC functions use security invoker mode, meaning they run with the caller’s permissions and respect RLS:

set_saved_ad_projects(ad_archive_id, project_ids[])

Security: security invoker
Checks:

Throws if auth.uid() is null
Validates all project_ids belong to the calling user via join to user_projects
Inserts into user_saved_ads and user_saved_ad_projects scoped to auth.uid()

remove_saved_ad(ad_archive_id)

Security: security invoker
Checks:

Throws if auth.uid() is null
Deletes only where user_id = auth.uid()

remove_ad_from_project(ad_archive_id, project_id)

Security: security invoker
Checks:

Throws if auth.uid() is null
Deletes only where user_id = auth.uid() and project_id matches

get_project_counts_for_ads(ad_ids[])

Security: security invoker
Scope:

Joins to user_saved_ad_projects with user_id = auth.uid()
Returns project counts only for the calling user’s saved ads

All these functions enforce ownership checks before operating on data, preventing privilege escalation.

Migration Hardening

Migration 20260223091000_harden_legacy_security_objects.sql sets search_path = public on all functions to prevent search path attacks:

alter function public.set_updated_at() set search_path = public;
alter function public.classify_network(text,text) set search_path = public;
alter function public.classify_niche(text) set search_path = public;

This ensures functions only reference objects in the public schema, blocking malicious schema injection.

Security Best Practices

User isolation is enforced at multiple layers:

RLS policies on user_id = auth.uid()
Foreign key constraints that validate ownership
RPC functions that throw on invalid auth.uid() or mismatched user IDs
Frontend route guards that hide admin UI from non-admins

Common pitfalls:

Do not use the service role key in frontend code
Do not disable RLS on tables with user data
Do not trust user_id from client input — always use auth.uid() in policies and functions
Do not allow anon role to access any user or ad data

Testing RLS

To test RLS policies, create a test user and verify:

User A cannot see User B’s saved ads
User A cannot insert a saved ad with user_id = User B
User A cannot add an ad to User B’s project (foreign key constraint fails)
Anonymous users get empty results when querying ads_feed_v2
Admin users can query fanbasis_webhook_log, members cannot

Use the Supabase SQL Editor to impersonate roles:

set role authenticated;
set request.jwt.claims.sub to '<user_uuid>';
select * from user_saved_ads; -- should only return rows for this user

Endpoints

Database

Core Principles

apify_ads_raw

Policies

Revocations

user_saved_ads

Policies

Revocations

user_projects

Policies

Revocations

user_saved_ad_projects

Policies

Foreign Key Enforcement

Revocations

fanbasis_enabled_offers

Policies

Revocations

fanbasis_webhook_log

Policies

Revocations

page_rip_log

Policies

Access Pattern

Admin vs. Member Access

Admin Detection

Admin-Only Operations

Frontend Gating

Service Role Operations

Authenticated vs. Anonymous

Revocations Summary

RPC Function Security

Migration Hardening

Security Best Practices

Testing RLS

Build docs developers (and LLMs) love

Endpoints

Database

​Core Principles

​apify_ads_raw

​Policies

​Revocations

​user_saved_ads

​Policies

​Revocations

​user_projects

​Policies

​Revocations

​user_saved_ad_projects

​Policies

​Foreign Key Enforcement

​Revocations

​fanbasis_enabled_offers

​Policies

​Revocations

​fanbasis_webhook_log

​Policies

​Revocations

​page_rip_log

​Policies

​Access Pattern

​Admin vs. Member Access

​Admin Detection

​Admin-Only Operations

​Frontend Gating

​Service Role Operations

​Authenticated vs. Anonymous

​Revocations Summary

​RPC Function Security

​Migration Hardening

​Security Best Practices

​Testing RLS

Build docs developers (and LLMs) love

Core Principles

apify_ads_raw

Policies

Revocations

user_saved_ads

Policies

Revocations

user_projects

Policies

Revocations

user_saved_ad_projects

Policies

Foreign Key Enforcement

Revocations

fanbasis_enabled_offers

Policies

Revocations

fanbasis_webhook_log

Policies

Revocations

page_rip_log

Policies

Access Pattern

Admin vs. Member Access

Admin Detection

Admin-Only Operations

Frontend Gating

Service Role Operations

Authenticated vs. Anonymous

Revocations Summary

RPC Function Security

Migration Hardening

Security Best Practices

Testing RLS