FleetOS Row Level Security (RLS) Setup

Critical Security Notice

⚠️ DO NOT deploy to production without applying these RLS policies!

Without RLS, any authenticated user can read/modify ALL data in the database.

---

What is Row Level Security?

RLS is PostgreSQL's built-in feature that restricts which rows users can access in database tables. It's like having a WHERE clause automatically applied to every query based on who's making the request.

Example:

• Without RLS: User A can see User B's service requests

• With RLS: User A can only see their own service requests

---

How to Apply RLS Policies

Option 1: Via Supabase SQL Editor (Recommended)

1. Go to your Supabase Dashboard: https://supabase.com/dashboard

2. Select your project

3. Click "SQL Editor" in the left sidebar

4. Click "+ New query"

5. Copy the entire contents of rls-policies.sql

6. Paste into the SQL Editor

7. Click "Run" (bottom right)

8. Verify success message appears

Option 2: Via psql CLI

psql -h db.xxx.supabase.co -U postgres -d postgres -f database/fleetos/rls-policies.sql

---

Verification

After applying, run this query to verify policies are active:

SELECT tablename, COUNT(*) as policy_count
FROM pg_policies
WHERE schemaname = 'public'
AND (tablename LIKE 'fleetos_%' OR tablename LIKE 'viral_%' OR tablename IN ('users', 'user_profiles', 'suppliers', 'service_requests'))
GROUP BY tablename
ORDER BY tablename;

Expected result:

tablename                    | policy_count
----------------------------|-------------
fleetos_communication_log   | 2
fleetos_invites            | 4
fleetos_relationships      | 4
service_requests          | 6
suppliers                 | 3
users                    | 2
user_profiles           | 3
viral_conversions       | 3
viral_events           | 3
viral_feedback         | 2
viral_preferences     | 1

---

Testing RLS Policies

Test 1: Verify Users Can't See Each Other's Data

-- As User A (fleet_user_id = 'user-a-123')
SELECT * FROM service_requests WHERE fleet_user_id = 'user-b-456';
-- Expected: 0 rows (even if user-b-456 has requests)

-- As User A
SELECT * FROM service_requests WHERE fleet_user_id = 'user-a-123';
-- Expected: All of User A's requests

Test 2: Verify Suppliers Can Only See Assigned Requests

-- As Supplier S (supplier_id = 'supplier-s-789')
SELECT * FROM service_requests WHERE supplier_id = 'supplier-s-789';
-- Expected: Only requests assigned to this supplier

SELECT * FROM service_requests WHERE supplier_id = 'another-supplier-xyz';
-- Expected: 0 rows

Test 3: Verify Magic Link Tracking Works (No Auth)

-- As anonymous user (not logged in)
SELECT * FROM service_requests WHERE magic_link_token = 'valid-token-here';
-- Expected: The specific request with that token

---

RLS Policy Summary

users table

• Users can view/update their own profile only

user_profiles table

• Users can view/update/insert their own profile only

suppliers table

• Public read access (needed for request form lookup)

• Suppliers can update their own profile

service_requests table

• Fleets see their own requests

• Suppliers see assigned requests

• Anyone can track via magic_link_token (no auth required)

fleetos_invites table

• Users see invites they sent

• Users see invites they received (matched by email/phone)

• Recipients can update status (accept/decline)

fleetos_relationships table

• Fleets see their relationships

• Suppliers see their relationships

• Both parties can create/update

viral_* tables

• Users see their own events/conversions/feedback

• Admins (ROBO_HUB tier) can see all data for analytics

---

Important Notes

Public Access Exceptions

Two tables have intentional public access:

1. suppliers - Public read access allows:

   • Public request forms to load supplier data

   • Anyone to view /request/:supplierSlug pages

   • No authentication required for first request

2. service_requests (via magic_link_token) - Allows:

   • Fleet customers to track requests without logging in

   • Real-time status updates via magic link

   • No account required for basic tracking

These are intentional design choices aligned with FleetOS's frictionless UX strategy.

Admin Access

Users with auth_tier = 'ROBO_HUB' or auth_tier = 'ROBO_DAPP' or is_admin = true can:

• View all viral_events (for analytics dashboard)

• View all viral_conversions (for growth metrics)

• Calculate R₀ and network effects

This enables the ViralAnalyticsDashboard while maintaining user privacy.

---

Rollback (Emergency Use Only)

If RLS causes issues in production:

-- Disable RLS on all tables (UNSAFE - only for emergency debugging)
ALTER TABLE users DISABLE ROW LEVEL SECURITY;
ALTER TABLE user_profiles DISABLE ROW LEVEL SECURITY;
ALTER TABLE suppliers DISABLE ROW LEVEL SECURITY;
ALTER TABLE service_requests DISABLE ROW LEVEL SECURITY;
ALTER TABLE fleetos_invites DISABLE ROW LEVEL SECURITY;
ALTER TABLE fleetos_relationships DISABLE ROW LEVEL SECURITY;
ALTER TABLE fleetos_communication_log DISABLE ROW LEVEL SECURITY;
ALTER TABLE viral_events DISABLE ROW LEVEL SECURITY;
ALTER TABLE viral_conversions DISABLE ROW LEVEL SECURITY;
ALTER TABLE viral_preferences DISABLE ROW LEVEL SECURITY;
ALTER TABLE viral_feedback DISABLE ROW LEVEL SECURITY;

⚠️ Only use in emergency. Re-enable RLS ASAP.

---

Troubleshooting

"No rows returned" but I know data exists

Cause: RLS is working correctly and you don't have permission

Solution: Verify you're authenticated as the correct user:

SELECT auth.uid(); -- Should match user_id in the table

"Permission denied for table X"

Cause: RLS is enabled but no policies grant access

Solution: Check if policies exist:

SELECT * FROM pg_policies WHERE tablename = 'X';

"Function auth.uid() does not exist"

Cause: Supabase Auth schema not set up

Solution: Ensure you're using Supabase (not vanilla PostgreSQL)

---

Next Steps After Applying RLS

1. ✅ Apply RLS policies (this file)

2. ⏭️ Rotate Supabase service role key (Phase 1.2)

3. ⏭️ Apply auth tier migrations (Phase 1.3)

4. ⏭️ Test with real user accounts

5. ⏭️ Deploy to production

---

Security Status: 🔐 PRODUCTION READY (after applying these policies)