Access Control Deep Dive (RLS)

Purpose: Summarize Row Level Security (RLS) patterns used by the app and provide canonical policy examples. Audience: Developer, Admin Prerequisites: Supabase project; confirm actual policies in migrations.

Principles

• Principle of least privilege: public can view only public/approved data
• Ownership-based access for writes (seller_id, user_id)
• Role checks for admin-only tables/actions

Listings

• Public SELECT for active listings; owners/admins have broader access
• Sensitive fields protected via blur policy: see docs/architecture/blur-policy.md (blurred_fields table, can_view_field function, listing_view_secure view)
• Critical: Public visibility requires BOTH status='active' AND verification_status='approved'/'verified'
• Example policies (actual from DB):

-- Consolidated policy for all operations
CREATE POLICY "listings_consolidated" ON listings
FOR ALL TO public
USING (
  (seller_id = auth.uid()) OR 
  is_admin_user() OR 
  ((status = 'active') AND (verification_status = ANY (ARRAY['approved', 'verified']))) OR
  (auth.role() = 'service_role')
);

Important: The dual-condition requirement means:

• Admin approval alone (status='active') is insufficient for public visibility
• Must also update verification_status='approved' for verified sellers
• Unverified sellers' listings remain hidden from public even when approved

Offers

• Participants (buyer_id or seller_id) can see offers; buyer inserts; seller updates status

-- Participants can view
create policy view_own_offers on offers
for select to authenticated using (buyer_id = auth.uid() or seller_id = auth.uid());

-- Buyer can insert
create policy buyer_insert on offers
for insert to authenticated with check (buyer_id = auth.uid());

-- Seller updates (status/response)
create policy seller_update on offers
for update to authenticated using (seller_id = auth.uid());

Messaging

• Only conversation participants can read messages; sender inserts

-- Participants read
create policy read_messages on messages
for select to authenticated using (
  exists (
    select 1 from conversation_participants cp
    where cp.conversation_id = messages.conversation_id
      and cp.user_id = auth.uid()
  )
);
-- Sender inserts
create policy insert_messages on messages
for insert to authenticated with check (sender_id = auth.uid());

Notifications

• Users can see their own notifications

create policy view_own_notifications on notifications
for select to authenticated using (user_id = auth.uid());

Admin tables

• Admin-only access (managed through Postgres roles or custom function checks)

-- Example: block public; grant explicit to admin role
alter table admin_messages enable row level security;
create policy deny_all on admin_messages for all to public using (false);
-- Assign separate role (admin) at connection or enforce via SECURITY DEFINER RPCs

Subscriptions

• Users can read their own subscription rows

create policy read_own_subscription on subscriptions
for select to authenticated using (user_id = auth.uid());

Indexes & performance for RLS

• Messaging participant checks: index conversation_participants (conversation_id, user_id)
• Listings public read: partial index on listings(status) where status = 'active'
• Offers participants: composite (buyer_id), (seller_id)

Audit & logging

• Use email_logs, admin_* tables to keep audit trails
• Consider triggers to stamp updated_at consistently

Notes

• These examples are canonical patterns; confirm actual policies in supabase/migrations.
• Prefer SECURITY DEFINER functions for admin actions exposed to authenticated clients.

Related

• Data model: docs/data-model/schema.md
• Admin tables: docs/architecture/admin-tables.md