-- PostgreSQL Enterprise Security Configuration for RateRight
-- Execute these commands on your Fly.io PostgreSQL database
-- App: rateright-db

-- ============================================
-- PART 1: USER SECURITY & LEAST PRIVILEGE
-- ============================================

-- Create application-specific user with limited privileges
-- Generate a secure password using: openssl rand -base64 32
CREATE USER rateright_app WITH ENCRYPTED PASSWORD 'CHANGE_THIS_TO_SECURE_PASSWORD';

-- Create read-only user for analytics/reporting
CREATE USER rateright_readonly WITH ENCRYPTED PASSWORD 'CHANGE_THIS_TO_SECURE_PASSWORD';

-- Grant minimal required permissions to app user
GRANT CONNECT ON DATABASE rateright TO rateright_app;
GRANT USAGE ON SCHEMA public TO rateright_app;

-- Grant table-level permissions for app user
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO rateright_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO rateright_app;

-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO rateright_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT USAGE, SELECT ON SEQUENCES TO rateright_app;

-- Grant read-only permissions
GRANT CONNECT ON DATABASE rateright TO rateright_readonly;
GRANT USAGE ON SCHEMA public TO rateright_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO rateright_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT SELECT ON TABLES TO rateright_readonly;

-- Revoke dangerous public permissions
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE rateright FROM PUBLIC;

-- ============================================
-- PART 2: ROW LEVEL SECURITY (RLS)
-- ============================================

-- Enable RLS on sensitive tables
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE contracts ENABLE ROW LEVEL SECURITY;
ALTER TABLE payments ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_logs ENABLE ROW LEVEL SECURITY;

-- Create security policies for users table
CREATE POLICY users_isolation ON users
    FOR ALL 
    TO rateright_app
    USING (
        -- Users can only see their own record or public info of others
        id = current_setting('app.current_user_id', true)::INTEGER
        OR 
        current_setting('app.current_user_role', true) = 'admin'
    );

-- Create security policies for contracts table
CREATE POLICY contracts_isolation ON contracts
    FOR ALL
    TO rateright_app
    USING (
        -- Only parties to the contract can access it
        contractor_id = current_setting('app.current_user_id', true)::INTEGER
        OR 
        worker_id = current_setting('app.current_user_id', true)::INTEGER
        OR
        current_setting('app.current_user_role', true) = 'admin'
    );

-- Create security policies for payments table
CREATE POLICY payments_isolation ON payments
    FOR ALL
    TO rateright_app
    USING (
        -- Only payment parties can access
        payer_id = current_setting('app.current_user_id', true)::INTEGER
        OR 
        payee_id = current_setting('app.current_user_id', true)::INTEGER
        OR
        current_setting('app.current_user_role', true) = 'admin'
    );

-- Create security policies for audit_logs table
CREATE POLICY audit_logs_admin_only ON audit_logs
    FOR ALL
    TO rateright_app
    USING (current_setting('app.current_user_role', true) = 'admin');

-- Grant BYPASSRLS to admin operations (use sparingly)
-- This should only be used for administrative tasks
-- GRANT BYPASSRLS TO rateright_admin;

-- ============================================
-- PART 3: FUNCTION SECURITY
-- ============================================

-- Remove dangerous functions in production
DROP FUNCTION IF EXISTS dblink_connect(text);
DROP FUNCTION IF EXISTS dblink_connect_u(text);
DROP FUNCTION IF EXISTS dblink_connect_u(text, text);

-- Revoke EXECUTE on sensitive functions
REVOKE EXECUTE ON FUNCTION pg_read_file(text) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION pg_read_binary_file(text) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION pg_ls_dir(text) FROM PUBLIC;

-- ============================================
-- PART 4: SCHEMA SECURITY
-- ============================================

-- Set secure search path
ALTER DATABASE rateright SET search_path TO public;

-- Ensure only authorized schemas exist
DO $$
DECLARE
    unauthorized_schema RECORD;
BEGIN
    FOR unauthorized_schema IN 
        SELECT schema_name 
        FROM information_schema.schemata 
        WHERE schema_name NOT IN ('public', 'pg_catalog', 'information_schema', 'pg_toast')
    LOOP
        RAISE WARNING 'Unauthorized schema found: %', unauthorized_schema.schema_name;
    END LOOP;
END $$;

-- ============================================
-- PART 5: AUSTRALIAN COMPLIANCE REQUIREMENTS
-- ============================================

-- Create compliance audit trigger function
CREATE OR REPLACE FUNCTION audit_compliance_changes() 
RETURNS TRIGGER AS $$
BEGIN
    -- Log changes to compliance-critical fields
    IF TG_TABLE_NAME = 'users' THEN
        IF OLD.abn_number IS DISTINCT FROM NEW.abn_number OR
           OLD.gst_registered IS DISTINCT FROM NEW.gst_registered OR
           OLD.public_liability_insurance IS DISTINCT FROM NEW.public_liability_insurance OR
           OLD.workers_comp_insurance IS DISTINCT FROM NEW.workers_comp_insurance THEN
            
            INSERT INTO audit_logs (
                user_id, 
                action, 
                table_name, 
                record_id, 
                old_values, 
                new_values, 
                created_at
            ) VALUES (
                current_setting('app.current_user_id', true)::INTEGER,
                TG_OP,
                TG_TABLE_NAME,
                NEW.id,
                row_to_json(OLD),
                row_to_json(NEW),
                NOW()
            );
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Apply audit trigger to compliance-critical tables
CREATE TRIGGER audit_user_compliance
    AFTER UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION audit_compliance_changes();

CREATE TRIGGER audit_payment_compliance
    AFTER INSERT OR UPDATE OR DELETE ON payments
    FOR EACH ROW
    EXECUTE FUNCTION audit_compliance_changes();

-- ============================================
-- PART 6: DATA ENCRYPTION AT REST
-- ============================================

-- Create extension for encryption (if not exists)
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Example: Encrypt sensitive payment data
-- ALTER TABLE payments ADD COLUMN encrypted_account_details BYTEA;
-- UPDATE payments SET encrypted_account_details = pgp_sym_encrypt(account_details, 'encryption_key');

-- ============================================
-- PART 7: CONNECTION SECURITY
-- ============================================

-- Force SSL connections (handled by Fly.io, but verify)
-- These are typically set in postgresql.conf, but we verify them:
DO $$
BEGIN
    IF current_setting('ssl') != 'on' THEN
        RAISE WARNING 'SSL is not enabled! Enable it in postgresql.conf';
    END IF;
END $$;

-- ============================================
-- VERIFICATION QUERIES
-- ============================================

-- Verify user permissions
SELECT 
    grantee,
    table_name,
    string_agg(privilege_type, ', ') as privileges
FROM information_schema.table_privileges
WHERE grantee IN ('rateright_app', 'rateright_readonly')
GROUP BY grantee, table_name
ORDER BY grantee, table_name;

-- Verify RLS is enabled
SELECT 
    schemaname,
    tablename,
    rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
    AND tablename IN ('users', 'contracts', 'payments', 'invoices', 'audit_logs');

-- Check for dangerous permissions
SELECT 
    grantee,
    privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'PUBLIC'
    AND table_schema = 'public';
