# Row Level Security (RLS) Implementation

## Overview

Row Level Security (RLS) has been implemented for the SaaS platform to provide fine-grained access control at the database level. This ensures that users can only access data they are authorized to see, with policies enforced directly by PostgreSQL.

## Current Status

**RLS is ENABLED but not FORCED**

This means:
- RLS policies are in place and functional
- The service role (`saas_user`) can currently bypass RLS to maintain application functionality
- When user context is properly set (see Future Enhancements), RLS will be enforced
- This allows for gradual migration to full RLS enforcement

## Architecture

### Database Functions

Two helper functions have been created:

#### `current_user_id()`
Returns the UUID of the current user from the PostgreSQL session context.
```sql
SELECT current_user_id();
```

#### `is_admin()`
Returns true if the current user has the 'admin' role.
```sql
SELECT is_admin();
```

### User Roles

Users in the `__sys_users` table have a `role` column with possible values:
- `admin`: Full access to all data and administrative functions
- `user`: Limited access based on ownership and organization membership

To set a user as admin:
```sql
UPDATE __sys_users SET role = 'admin' WHERE email = 'admin@example.com';
```

## RLS Policies by Table

### __sys_users
- **SELECT**: Users see their own profile; admins see all users
- **UPDATE**: Users can update their own profile; admins can update any user
- **INSERT**: Only admins can create users
- **DELETE**: Only admins can delete users

### __sys_sessions
- **SELECT**: Users see their own sessions; admins see all
- **INSERT**: Users can create their own sessions
- **DELETE**: Users can delete their own sessions; admins can delete any

### __sys_api_keys
- **SELECT**: Users see their own API keys; admins see all
- **INSERT**: Users can create their own API keys
- **UPDATE**: Users can update their own keys; admins can update any
- **DELETE**: Users can delete their own keys; admins can delete any

### __sys_applications
- **SELECT**: Users see their own applications; admins see all
- **INSERT**: Users can create applications
- **UPDATE**: Users can update their own apps; admins can update any
- **DELETE**: Users can delete their own apps; admins can delete any

### __sys_deployments
- **All operations**: Users can manage deployments for their applications; admins can manage all

### __sys_audit_logs
- **SELECT**: Users see their own audit logs; admins see all
- **INSERT**: Anyone can insert (system operations)
- **UPDATE**: No one (immutable)
- **DELETE**: Only admins (for cleanup)

### __sys_email_queue
- **All operations**: Admin only

### __sys_smtp_settings
- **All operations**: Admin only

## Enabling Full RLS Enforcement

To enforce RLS for all roles (including the service role), uncomment these lines in `database/init/05-row-level-security.sql`:

```sql
ALTER TABLE __sys_users FORCE ROW LEVEL SECURITY;
ALTER TABLE __sys_sessions FORCE ROW LEVEL SECURITY;
ALTER TABLE __sys_api_keys FORCE ROW LEVEL SECURITY;
ALTER TABLE __sys_applications FORCE ROW LEVEL SECURITY;
ALTER TABLE __sys_deployments FORCE ROW LEVEL SECURITY;
ALTER TABLE __sys_audit_logs FORCE ROW LEVEL SECURITY;
ALTER TABLE __sys_email_queue FORCE ROW LEVEL SECURITY;
ALTER TABLE __sys_smtp_settings FORCE ROW LEVEL SECURITY;
```

Then apply the migration:
```bash
docker exec saas-postgres psql -U saas_user -d saas_db -f /docker-entrypoint-initdb.d/05-row-level-security.sql
```

## Middleware (Future Enhancement)

The `rlsContext.ts` middleware has been created to set user context in PostgreSQL sessions:

```typescript
import { setRLSContext } from './middleware/rlsContext';

// Apply after authentication middleware
app.use(authenticateToken);
app.use(setRLSContext);
```

This middleware:
1. Acquires a dedicated database client for each request
2. Sets the current user ID in the PostgreSQL session
3. Attaches the client to the request object
4. Automatically releases the client when the response completes

**Note**: Full integration requires refactoring routes to use `req.dbClient` instead of the global `pool`.

## Testing RLS Policies

### Test as a Regular User

```sql
-- Set context as a regular user
SET LOCAL app.current_user_id = 'user-uuid-here';

-- Try to see all users (should only see own profile)
SELECT * FROM __sys_users;

-- Try to see all API keys (should only see own keys)
SELECT * FROM __sys_api_keys;
```

### Test as an Admin

```sql
-- Set context as an admin user
SET LOCAL app.current_user_id = 'admin-uuid-here';

-- Should see all users
SELECT * FROM __sys_users;

-- Should see all API keys
SELECT * FROM __sys_api_keys;
```

### Verify Policies

```sql
-- List all RLS policies
SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual
FROM pg_policies
WHERE schemaname = 'public'
ORDER BY tablename, policyname;
```

## Performance Considerations

Indexes have been created to optimize RLS policy checks:
- `idx_users_role` on `__sys_users(role)`
- `idx_sessions_user_id` on `__sys_sessions(user_id)`
- `idx_api_keys_created_by` on `__sys_api_keys(created_by)`
- `idx_applications_created_by` on `__sys_applications(created_by)`
- `idx_deployments_application_id` on `__sys_deployments(application_id)`
- `idx_audit_logs_user_id` on `__sys_audit_logs(user_id)`

## Troubleshooting

### Users Can't See Expected Data

1. Check if RLS is enabled:
   ```sql
   SELECT tablename, rowsecurity FROM pg_tables
   WHERE schemaname = 'public' AND tablename LIKE '__sys_%';
   ```

2. Check user role:
   ```sql
   SELECT id, email, role FROM __sys_users WHERE email = 'user@example.com';
   ```

3. Verify current context:
   ```sql
   SELECT current_user_id(), is_admin();
   ```

### System Operations Failing

If background jobs or system operations fail with permission errors:

1. Use the `bypassRLS()` helper function:
   ```typescript
   import { bypassRLS } from './middleware/rlsContext';

   const result = await bypassRLS(async () => {
     // System operation that needs full access
     return await pool.query('SELECT * FROM __sys_email_queue');
   });
   ```

2. Or use `executeAsUser()` for operations on behalf of a specific user:
   ```typescript
   import { executeAsUser } from './middleware/rlsContext';

   const result = await executeAsUser(userId, async (client) => {
     return await client.query('SELECT * FROM __sys_users');
   });
   ```

## Security Best Practices

1. **Always use HTTPS** - RLS protects data at the database level, but authentication tokens should be protected in transit

2. **Validate JWT tokens** - Ensure the `authenticateToken` middleware properly validates tokens before setting user context

3. **Regular audits** - Monitor `__sys_audit_logs` for suspicious activities

4. **Least privilege** - Grant users the minimum role needed for their tasks

5. **Test policies** - Thoroughly test RLS policies before enforcing them in production

## Migration Path

### Phase 1: Foundation (Current)
✅ RLS policies created
✅ Helper functions implemented
✅ Indexes created for performance
✅ User roles added
✅ Service role can bypass RLS

### Phase 2: Middleware Integration (TODO)
- [ ] Add `setRLSContext` middleware to API service
- [ ] Refactor routes to use `req.dbClient`
- [ ] Test with different user contexts
- [ ] Update public API to use API key context

### Phase 3: Enforcement (TODO)
- [ ] Enable FORCE ROW LEVEL SECURITY for all tables
- [ ] Monitor error logs for permission issues
- [ ] Fix any queries that need special handling
- [ ] Update documentation with lessons learned

### Phase 4: Advanced Features (Future)
- [ ] Organization-based policies
- [ ] Team/group-based access control
- [ ] Time-based access restrictions
- [ ] Data classification and labeling

## References

- [PostgreSQL RLS Documentation](https://www.postgresql.org/docs/current/ddl-rowsecurity.html)
- [RLS Best Practices](https://www.postgresql.org/docs/current/sql-createpolicy.html)
- Migration file: `database/init/05-row-level-security.sql`
- Middleware: `services/api/src/middleware/rlsContext.ts`