Service Role Usage

Guide to using Supabase service role for operations that need to bypass Row-Level Security.

What is Service Role?

The service role is a special Supabase role that has full database access and bypasses all RLS policies. It should only be used in secure, server-side contexts.

When to Use Service Role

✅ Appropriate Uses

1. Data Seeding

   • Creating initial tenant data
   • Bulk imports
   • Migration scripts

2. Background Jobs

   • Scheduled tasks
   • Data processing
   • Report generation

3. Admin Operations

   • Tenant bootstrap
   • User provisioning
   • System maintenance

4. Edge Functions

   • Server-side API endpoints
   • Webhook handlers
   • Scheduled functions

❌ Never Use For

1. Frontend Code

   • React components
   • Client-side API calls
   • User-facing operations

2. User Requests

   • Direct user actions
   • Form submissions
   • Real-time operations

3. Public APIs

   • Exposed endpoints
   • Third-party integrations (use API keys instead)

Security Warning

⚠️ CRITICAL: The service role key is equivalent to database superuser access. If exposed, an attacker could:

• Read all tenant data
• Modify or delete any data
• Bypass all security policies
• Access other tenants' data

Never commit service role keys to version control or expose them in client-side code.

Creating Service Role Client

In Node.js Scripts

// scripts/seedHelpers.ts
import { createClient } from '@supabase/supabase-js';

export function createAdminClient() {
  const supabaseUrl = process.env.SUPABASE_URL;
  const supabaseServiceKey = process.env.SUPABASE_SERVICE_ROLE_KEY;

  if (!supabaseUrl || !supabaseServiceKey) {
    throw new Error('Missing SUPABASE_URL or SUPABASE_SERVICE_ROLE_KEY');
  }

  return createClient(supabaseUrl, supabaseServiceKey, {
    auth: {
      autoRefreshToken: false,
      persistSession: false,
    },
  });
}

In Edge Functions

// supabase/functions/_shared/db.ts
import { createClient } from '@supabase/supabase-js';

const supabaseUrl = Deno.env.get('SUPABASE_URL')!;
const supabaseServiceKey = Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!;

export const adminClient = createClient(supabaseUrl, supabaseServiceKey, {
  auth: {
    autoRefreshToken: false,
    persistSession: false,
  },
});

Common Use Cases

1. Seeding Tenant Data

// scripts/seed.ts
import { createAdminClient } from './seedHelpers';

const adminClient = createAdminClient();

async function seedTenant(tenantId: string) {
  // Service role bypasses RLS, can insert into any tenant
  await adminClient
    .from('inventory_items')
    .insert({
      tenant_id: tenantId,
      sku: 'ITEM-001',
      name: 'Sample Item',
      // ...
    });
}

2. Tenant Bootstrap

async function bootstrapTenant(name: string, adminUserId: string) {
  const adminClient = createAdminClient();
  
  // Create tenant (requires service role if RLS blocks anon)
  const { data: tenant } = await adminClient
    .from('tenants')
    .insert({ name, active: true })
    .select()
    .single();
  
  // Assign admin (requires service role)
  await adminClient
    .from('tenant_users')
    .insert({
      tenant_id: tenant.id,
      user_id: adminUserId,
      role: 'tenant_admin',
    });
  
  return tenant;
}

3. Bulk Operations

async function migrateData(fromTenant: string, toTenant: string) {
  const adminClient = createAdminClient();
  
  // Copy all shipments from one tenant to another
  const { data: shipments } = await adminClient
    .from('shipments')
    .select('*')
    .eq('tenant_id', fromTenant);
  
  if (shipments) {
    const newShipments = shipments.map(s => ({
      ...s,
      id: undefined, // Generate new ID
      tenant_id: toTenant,
    }));
    
    await adminClient
      .from('shipments')
      .insert(newShipments);
  }
}

4. System Maintenance

async function cleanupInactiveTenants() {
  const adminClient = createAdminClient();
  
  // Find inactive tenants
  const { data: inactiveTenants } = await adminClient
    .from('tenants')
    .select('id')
    .eq('active', false)
    .lt('updated_at', '2024-01-01'); // Older than date
  
  // Archive or delete (with proper backup first!)
  for (const tenant of inactiveTenants || []) {
    // Backup data first!
    await archiveTenantData(tenant.id);
    
    // Then delete
    await adminClient
      .from('tenants')
      .delete()
      .eq('id', tenant.id);
  }
}

RLS Bypass Policies

For service role to work, tables need service role policies:

-- Example: Allow service role full access
CREATE POLICY <table>_service_role_all ON <table>
  FOR ALL
  TO service_role
  USING (true)
  WITH CHECK (true);

Note: Service role automatically bypasses RLS, but explicit policies make the intent clear and can be useful for documentation.

Environment Variables

Development

# .env.local (never commit!)
SUPABASE_URL=https://your-project.supabase.co
SUPABASE_SERVICE_ROLE_KEY=your-service-role-key

Production

Store in secure environment:

• Supabase Dashboard: Project Settings → API → Service Role Key
• CI/CD: Encrypted secrets
• Edge Functions: Deno environment variables
• Server: Secure key management (AWS Secrets Manager, etc.)

Best Practices

1. Minimal Scope

Only use service role for operations that absolutely require it:

// ❌ Bad: Using service role for regular queries
const adminClient = createAdminClient();
const { data } = await adminClient.from('shipments').select('*');

// ✅ Good: Use regular client with RLS
const { data } = await supabase.from('shipments').select('*');

2. Explicit Functions

Create clearly named functions that use service role:

// scripts/adminOperations.ts
export async function createTenantAsAdmin(name: string) {
  const adminClient = createAdminClient();
  // ... service role operations
}

3. Audit Logging

Log all service role operations:

async function adminOperation(operation: string, details: any) {
  const adminClient = createAdminClient();
  
  // Log operation
  await adminClient.from('admin_audit_log').insert({
    operation,
    details,
    performed_by: 'system', // or user ID if available
    performed_at: new Date().toISOString(),
  });
  
  // Perform operation
  // ...
}

4. Error Handling

Service role operations should have robust error handling:

async function safeAdminOperation() {
  const adminClient = createAdminClient();
  
  try {
    // Operation
  } catch (error) {
    console.error('Admin operation failed:', error);
    // Log to monitoring service
    // Alert administrators
    throw error;
  }
}

Testing Service Role Operations

Unit Tests

// tests/adminOperations.test.ts
describe('Admin Operations', () => {
  it('should create tenant with service role', async () => {
    const adminClient = createAdminClient();
    
    const { data, error } = await adminClient
      .from('tenants')
      .insert({ name: 'Test Tenant' })
      .select()
      .single();
    
    expect(error).toBeNull();
    expect(data).toBeDefined();
    
    // Cleanup
    await adminClient.from('tenants').delete().eq('id', data.id);
  });
});

Integration Tests

Test that service role bypasses RLS:

it('should bypass RLS with service role', async () => {
  const adminClient = createAdminClient();
  
  // Should be able to see all tenants
  const { data } = await adminClient
    .from('tenants')
    .select('*');
  
  expect(data.length).toBeGreaterThan(0);
});

Troubleshooting

Service Role Not Working

1. Check Key: Verify SUPABASE_SERVICE_ROLE_KEY is correct
2. Check Environment: Ensure variable is loaded
3. Check Client: Verify using service role key, not anon key

RLS Still Blocking

Service role should automatically bypass RLS. If still blocked:

• Verify you're using service role key (not anon key)
• Check client creation code
• Verify key has correct format

Related Documentation

• Tenant Bootstrap - Using service role for tenant creation
• Seed Strategy - Seeding data with service role
• RLS Policies - Understanding RLS that service role bypasses