appserver/docs/ROW_LEVEL_SECURITY.md

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.

SELECT current_user_id();

is_admin()

Returns true if the current user has the 'admin' role.

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:

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:

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:

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:

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

-- 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

-- 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

-- 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:

   SELECT tablename, rowsecurity FROM pg_tables
   WHERE schemaname = 'public' AND tablename LIKE '__sys_%';
   

2. Check user role:

   SELECT id, email, role FROM __sys_users WHERE email = 'user@example.com';
   

3. Verify current context:

   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:

   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:

   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
• RLS Best Practices
• Migration file: database/init/05-row-level-security.sql
• Middleware: services/api/src/middleware/rlsContext.ts