ACQM Docs

Database Permissions & Row Level Security (RLS)

Purpose: Comprehensive documentation of all RLS policies, role-based access control, and security mechanisms in AcqMarketplace. Audience: Developer, Owner, Admin Prerequisites: Basic understanding of PostgreSQL RLS and Supabase Auth

Data model index: ./schema.md
Tables reference: ./tables.md
Enums: ./enums.md
RPC functions: ./rpc.md

Overview
Role Hierarchy
Authentication Flow
RLS Policy Patterns
Table-Level Permissions
Function-Level Security
Page Access Control
Security Best Practices
Troubleshooting
Next Steps

Overview

AcqMarketplace uses Row Level Security (RLS) to enforce access rules directly at the database layer. This ensures policies are applied regardless of application logic.

Core Security Principles

Defense in Depth: enforced in both UI and DB
Least Privilege: minimal necessary access
Role-Based Access: buyer, seller, admin
Owner Access: users manage their own data
Admin Override: administrators have elevated privileges

Role Hierarchy

User Roles (app_role enum)

sql

-- Role hierarchy (in order of privilege)
CREATE TYPE public.app_role AS ENUM ('admin', 'seller', 'buyer');

Role Definitions

Role	Description	Key Permissions
admin	System administrators	Full access to all data, admin panels, user management
seller	Business sellers	Create listings, manage offers, view buyer profiles in transactions
buyer	Business buyers	Browse listings, make offers, access seller profiles

Role Switching

Users can switch between buyer and seller using:

sql

-- Function: switch_role(p_role text)
SELECT switch_role('seller'); -- or 'buyer'

Note: switching to admin via this function is not allowed; the admin role must be assigned explicitly.

Authentication Flow

Frontend Authentication Guards

typescript

// ProtectedRoute.tsx
// Guards routes requiring authentication; redirects to /auth if not logged in

// AdminProtectedRoute.tsx
// Guards admin routes; checks profiles.role === 'admin'

Auth Context Integration

AuthContext handles:

session state
profile loading
role-based redirects
cache invalidation on session changes

Profile Creation Trigger

sql

-- handle_new_user()
-- Automatically creates a profile on signup; default role: 'buyer'

RLS Policy Patterns

Pattern 1: Owner + Admin Access

sql

CREATE POLICY "table_name_policy" ON table_name
FOR ALL 
USING (user_id = auth.uid() OR is_admin_user())
WITH CHECK (user_id = auth.uid() OR is_admin_user());

Pattern 2: Public Read + Owner Write

sql

-- Public read
CREATE POLICY "public_read" ON table_name
FOR SELECT USING (true);

-- Owner write
CREATE POLICY "owner_write" ON table_name
FOR INSERT/UPDATE/DELETE
USING (user_id = auth.uid())
WITH CHECK (user_id = auth.uid());

Pattern 3: Role-Based Access

sql

CREATE POLICY "role_based_access" ON table_name
FOR SELECT 
USING (
  (user_id = auth.uid()) OR        -- own data
  is_admin_user() OR               -- admin
  (get_current_user_role() = 'seller' AND condition)
);

Pattern 4: Relationship-Based Access

sql

CREATE POLICY "transaction_parties" ON transactions
FOR SELECT
USING (
  auth.uid() IN (buyer_id, seller_id) OR 
  is_admin_user()
);

Table-Level Permissions

Table-Level Policies Summary

The matrix below summarizes RLS behavior per table. For full details and column definitions, see ./tables.md.

Table	SELECT	INSERT	UPDATE	DELETE	Notes
profiles	Owner; Admin full; Service full	Owner	Owner; Admin	Admin	Counterparty visibility in transactions
listings	Public (active + approved); Owner/Admin broader	Authenticated (owner)	Owner; Admin	Admin	Field blurring via secure views
listing_categories	Public	Admin	Admin	Admin	Used for category selection
listing_views	Seller (owner) + Admin	Service/Triggers	Service	Admin/Service	Aggregated via views
listing_questions	Public on active listings	Authenticated	Seller/Admin (answer)	Admin	Q&A on listings
offers	Parties (buyer/seller) + Admin	Buyer (not own listing)	Seller (status/response)	Admin	Offer workflow
transactions	Parties + Admin	System functions	Parties/Admin (status-bound)	Admin	Escrow lifecycle
conversations	Participants only	Authenticated	Participants	Admin	Created via RPC/helper
conversation_participants	Participants/Admin	System	System/Participants	Admin	Composite PK (conversation, user)
messages	Participants	Participants (limits apply)	Participants	Admin/Service	Subscription limits apply
notifications	Owner; Admin/Service	System	System	Admin	Realtime notifications
subscriptions	Owner/Admin/Service	Service/Webhooks	Owner/Admin/Service	Admin	Stripe-driven updates
subscription_features	Public	Admin	Admin	Admin	Feature catalog
subscription_feature_usage	Owner/Admin/Service	Service	Service	Admin	Usage counters
billing_plans	Public	Admin	Admin	Admin	Stripe Price mapping
price_alerts	Owner	Owner	Owner	Owner	Per-user alerts
favorites	Owner	Owner	Owner	Owner	Unique (user, listing)
saved_searches	Owner	Owner	Owner	Owner	JSON criteria
admin_listing_comments	Seller (read own listing) + Admin	Admin	Admin	Admin	Moderation feedback
admin_messages	Sender/Admin	Admin	Admin	Admin	Admin→User messaging
admin_contact_messages	Sender + Admin	Authenticated	Admin	Admin	Contact form
reports	Reporter + Admin	Authenticated	Admin	Admin	Abuse reports
blurred_fields	Admin (read/write)	Admin	Admin	Admin	Controls secure listing view
translations	Public	Admin	Admin	Admin	i18n content
tech_stack	Public	Admin	Admin	Admin	Filter options
email_logs	Admin + Service	Service	Admin/Service	Admin	Outbound email audit
listing_view_secure (VIEW)	Public/Authenticated	n/a	n/a	n/a	Applies field visibility rules
listing_views_summary (VIEW)	Seller + Admin	n/a	n/a	n/a	Aggregated analytics
subscriptions_view (VIEW)	Owner + Admin	n/a	n/a	n/a	Safe subscription fields
seller_profiles_public (VIEW)	Public	n/a	n/a	n/a	Public seller subset

profiles

RLS: users can view/update their own profile; admin full; service full.

Transaction visibility: buyers can see seller profiles and vice versa where applicable.

listings

RLS: public SELECT for active + approved; owners and admins have broader access.

Writes: INSERT authenticated; UPDATE owner/admin; DELETE admin.

offers

RLS: targeted policies

Insert: buyer only (not on own listing)
Select: buyer + seller + admin
Update: seller (status/response)

transactions

RLS: based on is_deal_party()

Select: parties and admin only
Update: parties (status-bound) and admin

conversations

RLS: participants only; conversation creation allowed for authenticated users.

messages

RLS: participants can read/send; sending may be gated by subscription.

notifications

RLS: users see their own notifications; admin/service can manage all.

subscriptions

RLS: owner + admin + service (Stripe webhooks update rows).

invoice_history

RLS: owner can view own invoices; service manages writes.

admin_listing_comments

RLS: admin manages; sellers can view comments on their listings.

admin_contact_messages

RLS: sender can view own messages; admin can view all.

reports

RLS: reporter can view own reports; admin can view/update all.

Function-Level Security

Critical functions run with elevated privileges (SECURITY DEFINER) to avoid RLS recursion and to implement atomic transactions.

Role & Permission Checks

sql

-- is_admin_user()
-- get_current_user_role()
-- get_user_subscription_level()
-- can_access_sensitive_data()

Business Logic Functions

sql

-- seller_accept_offer()
-- seller_reject_offer()
-- create_transaction()
-- initiate_conversation()

Access Control Helpers

sql

-- is_deal_party()
-- check_conversation_participant()
-- can_view_field()

See details and signatures in ./rpc.md.

Page Access Control

Frontend Route Guards

The app uses route guards (ProtectedRoute, AdminProtectedRoute) and optional dynamic rules to control page access.

Page Access Rules Table

sql

-- page_access_rules (optional)
CREATE TABLE page_access_rules (
  id UUID PRIMARY KEY,
  role TEXT NOT NULL,      -- 'buyer' | 'seller'
  page_key TEXT NOT NULL,  -- stable identifier
  blocked BOOLEAN NOT NULL DEFAULT false
);

Page Definitions (example)

typescript

// src/constants/pageAccess.ts (example)
export const BUYER_PAGES = [
  { key: 'buyer_dashboard', path: '/buyer-dashboard' },
  { key: 'messages', path: '/messages' },
  { key: 'my_offers', path: '/my-offers' },
];

export const SELLER_PAGES = [
  { key: 'seller_dashboard', path: '/seller-dashboard' },
  { key: 'create_listing', path: '/create-listing' },
  { key: 'my_listings', path: '/my-listings' },
];

Dynamic Access Control

Admin-configured (block/unblock per role)
UI hook to check access (e.g., usePageAccess)
ProtectedRoute enforces rules on navigation
Graceful redirect when blocked

Access Control Flow

mermaid

graph TD
    A[User navigates to page] --> B[ProtectedRoute component]
    B --> C[Check authentication]
    C -->|Not authenticated| D[Redirect to /auth]
    C -->|Authenticated| E[Get user role]
    E --> F[usePageAccess hook]
    F --> G[Query page_access_rules]
    G --> H[Check if page blocked for role]
    H -->|Blocked| I[Redirect to home]
    H -->|Allowed| J[Render page]

Security Best Practices

Database Level

Enable RLS on all tables
Minimal permissions per role
SECURITY DEFINER for sensitive functions
Audit trails for critical actions

Application Level

Frontend route guards
Input validation
Error messages without sensitive data
Proper session management (refresh, cleanup)

Anti‑Patterns to Avoid

❌ RLS recursion (policy reading from the same table) ✅ SECURITY DEFINER helpers: is_admin_user() etc.

❌ Client-side guards only ✅ Defense in Depth (UI + DB)

❌ Scattered hardcoded admin checks ✅ Centralized helper functions for checks

Troubleshooting

RLS Policy Violations

sql

-- Error: "new row violates row-level security policy"
-- Fix: ensure user_id is set correctly on INSERT
INSERT INTO table_name (user_id, ...) VALUES (auth.uid(), ...);

Infinite Recursion

sql

-- Bad: policy reads from the same table
CREATE POLICY "bad_policy" ON profiles
USING ((SELECT role FROM profiles WHERE id = auth.uid()) = 'admin');

-- Good: helper function
CREATE POLICY "good_policy" ON profiles  USING (is_admin_user());

Missing Permissions

sql

-- Check if RLS is enabled
SELECT schemaname, tablename, rowsecurity 
FROM pg_tables WHERE schemaname = 'public';

-- Existing policies
SELECT * FROM pg_policies WHERE schemaname = 'public';

Next Steps

See ./schema.md for the complete data model
See ../architecture/routing.md for frontend guards
See ../admin/guide.md for admin operations
See ../security-privacy.md for broader security considerations

Last updated: 2025-09-15

Database Permissions & Row Level Security (RLS) ​

Table of Contents ​

Overview ​

Core Security Principles ​

Role Hierarchy ​

User Roles (app_role enum) ​

Role Definitions ​

Role Switching ​

Authentication Flow ​

Frontend Authentication Guards ​

Auth Context Integration ​

Profile Creation Trigger ​

RLS Policy Patterns ​

Pattern 1: Owner + Admin Access ​

Pattern 2: Public Read + Owner Write ​

Pattern 3: Role-Based Access ​

Pattern 4: Relationship-Based Access ​

Table-Level Permissions ​

Table-Level Policies Summary ​

profiles ​

listings ​

offers ​

transactions ​

conversations ​

messages ​

notifications ​

subscriptions ​

invoice_history ​

admin_listing_comments ​

admin_contact_messages ​

reports ​

Function-Level Security ​

Role & Permission Checks ​

Business Logic Functions ​

Access Control Helpers ​

Page Access Control ​

Frontend Route Guards ​

Page Access Rules Table ​

Page Definitions (example) ​

Dynamic Access Control ​

Access Control Flow ​

Security Best Practices ​

Database Level ​

Application Level ​

Anti‑Patterns to Avoid ​

Troubleshooting ​

RLS Policy Violations ​

Infinite Recursion ​

Missing Permissions ​

Next Steps ​