appserver/database/init/05-row-level-security.sql

-- =====================================================
-- Row Level Security (RLS) Policies
-- =====================================================
-- This migration adds row-level security policies to protect data access
-- based on user roles and ownership

-- Enable Row Level Security on all system tables
ALTER TABLE __sys_users ENABLE ROW LEVEL SECURITY;
ALTER TABLE __sys_sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE __sys_api_keys ENABLE ROW LEVEL SECURITY;
ALTER TABLE __sys_applications ENABLE ROW LEVEL SECURITY;
ALTER TABLE __sys_deployments ENABLE ROW LEVEL SECURITY;
ALTER TABLE __sys_audit_logs ENABLE ROW LEVEL SECURITY;
ALTER TABLE __sys_email_queue ENABLE ROW LEVEL SECURITY;
ALTER TABLE __sys_smtp_settings ENABLE ROW LEVEL SECURITY;

-- Add role column to users table if it doesn't exist
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                   WHERE table_name='__sys_users' AND column_name='role') THEN
        ALTER TABLE __sys_users ADD COLUMN role VARCHAR(50) DEFAULT 'user';
    END IF;
END $$;

-- Update existing users to have admin role (for the first user)
UPDATE __sys_users SET role = 'admin' WHERE role IS NULL LIMIT 1;

-- Create function to get current user ID from JWT claims
CREATE OR REPLACE FUNCTION current_user_id() RETURNS UUID AS $$
BEGIN
    -- Try to get user_id from current_setting
    -- This will be set by the application layer
    RETURN current_setting('app.current_user_id', true)::UUID;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;

-- Create function to check if current user is admin
CREATE OR REPLACE FUNCTION is_admin() RETURNS BOOLEAN AS $$
DECLARE
    user_role VARCHAR(50);
BEGIN
    SELECT role INTO user_role
    FROM __sys_users
    WHERE id = current_user_id();

    RETURN user_role = 'admin';
EXCEPTION
    WHEN OTHERS THEN
        RETURN false;
END;
$$ LANGUAGE plpgsql STABLE;

-- =====================================================
-- USERS TABLE POLICIES
-- =====================================================

-- Users can see their own profile, admins can see all users
CREATE POLICY users_select_policy ON __sys_users
    FOR SELECT
    USING (id = current_user_id() OR is_admin());

-- Users can update their own profile, admins can update any user
CREATE POLICY users_update_policy ON __sys_users
    FOR UPDATE
    USING (id = current_user_id() OR is_admin());

-- Only admins can insert users
CREATE POLICY users_insert_policy ON __sys_users
    FOR INSERT
    WITH CHECK (is_admin());

-- Only admins can delete users
CREATE POLICY users_delete_policy ON __sys_users
    FOR DELETE
    USING (is_admin());

-- =====================================================
-- SESSIONS TABLE POLICIES
-- =====================================================

-- Users can see their own sessions, admins can see all
CREATE POLICY sessions_select_policy ON __sys_sessions
    FOR SELECT
    USING (user_id = current_user_id() OR is_admin());

-- Users can insert their own sessions
CREATE POLICY sessions_insert_policy ON __sys_sessions
    FOR INSERT
    WITH CHECK (user_id = current_user_id() OR is_admin());

-- Users can delete their own sessions, admins can delete any
CREATE POLICY sessions_delete_policy ON __sys_sessions
    FOR DELETE
    USING (user_id = current_user_id() OR is_admin());

-- =====================================================
-- API KEYS TABLE POLICIES
-- =====================================================

-- Users can see their own API keys, admins can see all
CREATE POLICY api_keys_select_policy ON __sys_api_keys
    FOR SELECT
    USING (created_by = current_user_id() OR is_admin());

-- Users can create their own API keys
CREATE POLICY api_keys_insert_policy ON __sys_api_keys
    FOR INSERT
    WITH CHECK (created_by = current_user_id() OR is_admin());

-- Users can update their own API keys, admins can update any
CREATE POLICY api_keys_update_policy ON __sys_api_keys
    FOR UPDATE
    USING (created_by = current_user_id() OR is_admin());

-- Users can delete their own API keys, admins can delete any
CREATE POLICY api_keys_delete_policy ON __sys_api_keys
    FOR DELETE
    USING (created_by = current_user_id() OR is_admin());

-- =====================================================
-- APPLICATIONS TABLE POLICIES
-- =====================================================

-- Users can see their own applications, admins can see all
CREATE POLICY applications_select_policy ON __sys_applications
    FOR SELECT
    USING (created_by = current_user_id() OR is_admin());

-- Users can create applications
CREATE POLICY applications_insert_policy ON __sys_applications
    FOR INSERT
    WITH CHECK (created_by = current_user_id() OR is_admin());

-- Users can update their own applications, admins can update any
CREATE POLICY applications_update_policy ON __sys_applications
    FOR UPDATE
    USING (created_by = current_user_id() OR is_admin());

-- Users can delete their own applications, admins can delete any
CREATE POLICY applications_delete_policy ON __sys_applications
    FOR DELETE
    USING (created_by = current_user_id() OR is_admin());

-- =====================================================
-- DEPLOYMENTS TABLE POLICIES
-- =====================================================

-- Users can see deployments of their applications, admins can see all
CREATE POLICY deployments_select_policy ON __sys_deployments
    FOR SELECT
    USING (
        EXISTS (
            SELECT 1 FROM __sys_applications
            WHERE __sys_applications.id = __sys_deployments.application_id
            AND (__sys_applications.created_by = current_user_id() OR is_admin())
        )
    );

-- Users can create deployments for their applications
CREATE POLICY deployments_insert_policy ON __sys_deployments
    FOR INSERT
    WITH CHECK (
        EXISTS (
            SELECT 1 FROM __sys_applications
            WHERE __sys_applications.id = application_id
            AND (__sys_applications.created_by = current_user_id() OR is_admin())
        )
    );

-- Users can update deployments of their applications, admins can update any
CREATE POLICY deployments_update_policy ON __sys_deployments
    FOR UPDATE
    USING (
        EXISTS (
            SELECT 1 FROM __sys_applications
            WHERE __sys_applications.id = __sys_deployments.application_id
            AND (__sys_applications.created_by = current_user_id() OR is_admin())
        )
    );

-- Users can delete deployments of their applications, admins can delete any
CREATE POLICY deployments_delete_policy ON __sys_deployments
    FOR DELETE
    USING (
        EXISTS (
            SELECT 1 FROM __sys_applications
            WHERE __sys_applications.id = __sys_deployments.application_id
            AND (__sys_applications.created_by = current_user_id() OR is_admin())
        )
    );

-- =====================================================
-- AUDIT LOGS TABLE POLICIES
-- =====================================================

-- Users can see their own audit logs, admins can see all
CREATE POLICY audit_logs_select_policy ON __sys_audit_logs
    FOR SELECT
    USING (user_id = current_user_id() OR is_admin());

-- Only the system can insert audit logs (through application)
CREATE POLICY audit_logs_insert_policy ON __sys_audit_logs
    FOR INSERT
    WITH CHECK (true);

-- No one can update audit logs (immutable)
CREATE POLICY audit_logs_update_policy ON __sys_audit_logs
    FOR UPDATE
    USING (false);

-- Only admins can delete audit logs (for cleanup)
CREATE POLICY audit_logs_delete_policy ON __sys_audit_logs
    FOR DELETE
    USING (is_admin());

-- =====================================================
-- EMAIL QUEUE TABLE POLICIES
-- =====================================================

-- Only admins can see email queue
CREATE POLICY email_queue_select_policy ON __sys_email_queue
    FOR SELECT
    USING (is_admin());

-- Only admins can insert into email queue
CREATE POLICY email_queue_insert_policy ON __sys_email_queue
    FOR INSERT
    WITH CHECK (is_admin());

-- Only admins can update email queue
CREATE POLICY email_queue_update_policy ON __sys_email_queue
    FOR UPDATE
    USING (is_admin());

-- Only admins can delete from email queue
CREATE POLICY email_queue_delete_policy ON __sys_email_queue
    FOR DELETE
    USING (is_admin());

-- =====================================================
-- SMTP SETTINGS TABLE POLICIES
-- =====================================================

-- Only admins can see SMTP settings
CREATE POLICY smtp_settings_select_policy ON __sys_smtp_settings
    FOR SELECT
    USING (is_admin());

-- Only admins can insert SMTP settings
CREATE POLICY smtp_settings_insert_policy ON __sys_smtp_settings
    FOR INSERT
    WITH CHECK (is_admin());

-- Only admins can update SMTP settings
CREATE POLICY smtp_settings_update_policy ON __sys_smtp_settings
    FOR UPDATE
    USING (is_admin());

-- Only admins can delete SMTP settings
CREATE POLICY smtp_settings_delete_policy ON __sys_smtp_settings
    FOR DELETE
    USING (is_admin());

-- =====================================================
-- BYPASS RLS FOR SERVICE ROLE
-- =====================================================
-- Allow the service user to bypass RLS for system operations
-- This is important for the application to function while RLS infrastructure is being built

-- Note: RLS is ENABLED but not FORCED, meaning:
-- - The service role (saas_user) can bypass RLS and access all data
-- - When we set up proper user context in the future, RLS will be enforced
-- - This allows gradual migration to full RLS enforcement

-- If you want to FORCE RLS (enforce for all roles including service role),
-- uncomment the lines below:

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

-- Create indexes for better RLS performance
CREATE INDEX IF NOT EXISTS idx_users_role ON __sys_users(role);
CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON __sys_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_api_keys_created_by ON __sys_api_keys(created_by);
CREATE INDEX IF NOT EXISTS idx_applications_created_by ON __sys_applications(created_by);
CREATE INDEX IF NOT EXISTS idx_deployments_application_id ON __sys_deployments(application_id);
CREATE INDEX IF NOT EXISTS idx_audit_logs_user_id ON __sys_audit_logs(user_id);

-- Grant necessary permissions to the service user
GRANT ALL ON ALL TABLES IN SCHEMA public TO saas_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO saas_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO saas_user;

COMMENT ON FUNCTION current_user_id() IS 'Returns the current user ID from session context';
COMMENT ON FUNCTION is_admin() IS 'Returns true if the current user has admin role';