Insufficient Security Isolation in Schema

-- The dangerous all-in-one table
CREATE TABLE user_data (
    id BIGINT PRIMARY KEY,
    -- Public profile data
    username VARCHAR(50),
    avatar_url VARCHAR(255),
    bio TEXT,
    -- Sensitive PII
    ssn VARCHAR(11),
    date_of_birth DATE,
    -- Financial data
    credit_card_number VARCHAR(16),
    bank_account VARCHAR(20),
    -- Security credentials
    password_hash VARCHAR(255),
    two_factor_secret VARCHAR(32),
    api_private_key TEXT
);

-- One SQL injection exposes EVERYTHING
-- One backup leak exposes EVERYTHING
-- One insider threat accesses EVERYTHING

-- Schema isolation by security classification
CREATE SCHEMA public_data;
CREATE SCHEMA internal_data;
CREATE SCHEMA sensitive_pii;
CREATE SCHEMA financial_data ENCRYPTED;
CREATE SCHEMA security_vault ENCRYPTED;

-- Public data (no encryption needed)
CREATE TABLE public_data.user_profiles (
    user_id BIGINT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    display_name VARCHAR(100),
    avatar_url VARCHAR(255),
    bio TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Internal operational data
CREATE TABLE internal_data.user_metadata (
    user_id BIGINT PRIMARY KEY,
    account_status VARCHAR(20),
    last_login TIMESTAMPTZ,
    login_count INTEGER DEFAULT 0,
    preferences JSONB
);

-- Sensitive PII (encrypted at rest, limited access)
CREATE TABLE sensitive_pii.user_identity (
    user_id BIGINT PRIMARY KEY,
    ssn_encrypted BYTEA,  -- Application-level encryption
    ssn_hash VARCHAR(64) UNIQUE,  -- For lookups only
    date_of_birth_encrypted BYTEA,
    full_name_encrypted BYTEA,
    audit_log JSONB DEFAULT '[]'::JSONB
);

-- Financial data (PCI compliant isolation)
CREATE TABLE financial_data.payment_methods (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id BIGINT NOT NULL,
    token_id VARCHAR(100),  -- From payment processor
    last_four VARCHAR(4),
    card_type VARCHAR(20),
    expires_month INTEGER,
    expires_year INTEGER
    -- Never store actual card numbers
);

-- Security credentials (highest isolation)
-- Private keys NEVER in database - use HSM
CREATE TABLE security_vault.user_auth (
    user_id BIGINT PRIMARY KEY,
    password_hash VARCHAR(255),  -- bcrypt/argon2
    mfa_enabled BOOLEAN DEFAULT FALSE,
    mfa_backup_codes_hash TEXT[],  -- Hashed backup codes
    failed_attempts INTEGER DEFAULT 0,
    locked_until TIMESTAMPTZ
);

-- Row-level security for additional protection
ALTER TABLE sensitive_pii.user_identity ENABLE ROW LEVEL SECURITY;

CREATE POLICY pii_access ON sensitive_pii.user_identity
    USING (
        current_setting('app.user_role') = 'pii_admin'
        OR (current_setting('app.user_id')::BIGINT = user_id 
            AND current_setting('app.access_reason') IS NOT NULL)
    );

-- Audit all access to sensitive schemas
CREATE OR REPLACE FUNCTION sensitive_access_audit()
RETURNS event_trigger AS $
BEGIN
    INSERT INTO audit.sensitive_data_access (
        accessed_at,
        schema_name,
        table_name,
        user_name,
        application_name,
        client_addr
    ) VALUES (
        NOW(),
        TG_TAG,
        TG_TABLE_NAME,
        current_user,
        current_setting('application_name'),
        inet_client_addr()
    );
END;
$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER audit_sensitive_access
    ON ddl_command_end
    WHEN TAG IN ('ALTER TABLE', 'DROP TABLE')
    EXECUTE FUNCTION sensitive_access_audit();