316 lines
9.3 KiB
PL/PgSQL
316 lines
9.3 KiB
PL/PgSQL
-- GDPR Compliance Migration
|
||
-- Adds consent tracking, IP logging, audit log, and data export functionality
|
||
|
||
-- 1. Create user_consents table for tracking consent
|
||
CREATE TABLE IF NOT EXISTS user_consents (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
||
consent_type TEXT NOT NULL, -- 'terms', 'privacy', 'marketing', 'analytics'
|
||
consent_given BOOLEAN NOT NULL DEFAULT false,
|
||
consent_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
ip_address INET,
|
||
user_agent TEXT,
|
||
consent_version TEXT, -- Track which version of terms/privacy was accepted
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
-- 2. Create audit_logs table for tracking all user actions
|
||
CREATE TABLE IF NOT EXISTS audit_logs (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
||
action TEXT NOT NULL, -- 'create', 'update', 'delete', 'view', 'export', 'login', 'logout'
|
||
resource_type TEXT NOT NULL, -- 'trip', 'place', 'user', 'provider', etc.
|
||
resource_id UUID,
|
||
old_data JSONB,
|
||
new_data JSONB,
|
||
ip_address INET,
|
||
user_agent TEXT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
-- 3. Create data_export_requests table for GDPR data export
|
||
CREATE TABLE IF NOT EXISTS data_export_requests (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
||
status TEXT NOT NULL DEFAULT 'pending', -- 'pending', 'processing', 'completed', 'failed'
|
||
export_data JSONB, -- Stores the exported data
|
||
requested_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
completed_at TIMESTAMPTZ,
|
||
expires_at TIMESTAMPTZ, -- Export link expires after 7 days
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
-- 4. Create account_deletion_requests table for right to be forgotten
|
||
CREATE TABLE IF NOT EXISTS account_deletion_requests (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
||
reason TEXT,
|
||
status TEXT NOT NULL DEFAULT 'pending', -- 'pending', 'approved', 'completed', 'cancelled'
|
||
requested_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
scheduled_deletion_at TIMESTAMPTZ, -- 30 days grace period
|
||
completed_at TIMESTAMPTZ,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
-- 5. Add indexes for performance
|
||
CREATE INDEX IF NOT EXISTS idx_user_consents_user_id ON user_consents(user_id);
|
||
CREATE INDEX IF NOT EXISTS idx_user_consents_type ON user_consents(consent_type);
|
||
CREATE INDEX IF NOT EXISTS idx_audit_logs_user_id ON audit_logs(user_id);
|
||
CREATE INDEX IF NOT EXISTS idx_audit_logs_resource ON audit_logs(resource_type, resource_id);
|
||
CREATE INDEX IF NOT EXISTS idx_audit_logs_created_at ON audit_logs(created_at DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_data_export_requests_user_id ON data_export_requests(user_id);
|
||
CREATE INDEX IF NOT EXISTS idx_data_export_requests_status ON data_export_requests(status);
|
||
CREATE INDEX IF NOT EXISTS idx_account_deletion_requests_user_id ON account_deletion_requests(user_id);
|
||
CREATE INDEX IF NOT EXISTS idx_account_deletion_requests_status ON account_deletion_requests(status);
|
||
|
||
-- 6. Create RLS policies
|
||
|
||
-- user_consents policies
|
||
ALTER TABLE user_consents ENABLE ROW LEVEL SECURITY;
|
||
|
||
CREATE POLICY "Users can view their own consents"
|
||
ON user_consents FOR SELECT
|
||
USING (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can insert their own consents"
|
||
ON user_consents FOR INSERT
|
||
WITH CHECK (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can update their own consents"
|
||
ON user_consents FOR UPDATE
|
||
USING (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Admins can view all consents"
|
||
ON user_consents FOR SELECT
|
||
USING (
|
||
EXISTS (
|
||
SELECT 1 FROM profiles
|
||
WHERE profiles.id = auth.uid()
|
||
AND profiles.role = 'admin'
|
||
)
|
||
);
|
||
|
||
-- audit_logs policies (read-only for users, admins can see all)
|
||
ALTER TABLE audit_logs ENABLE ROW LEVEL SECURITY;
|
||
|
||
CREATE POLICY "Users can view their own audit logs"
|
||
ON audit_logs FOR SELECT
|
||
USING (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Admins can view all audit logs"
|
||
ON audit_logs FOR SELECT
|
||
USING (
|
||
EXISTS (
|
||
SELECT 1 FROM profiles
|
||
WHERE profiles.id = auth.uid()
|
||
AND profiles.role = 'admin'
|
||
)
|
||
);
|
||
|
||
CREATE POLICY "Service role can insert audit logs"
|
||
ON audit_logs FOR INSERT
|
||
WITH CHECK (true);
|
||
|
||
-- data_export_requests policies
|
||
ALTER TABLE data_export_requests ENABLE ROW LEVEL SECURITY;
|
||
|
||
CREATE POLICY "Users can view their own export requests"
|
||
ON data_export_requests FOR SELECT
|
||
USING (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can create their own export requests"
|
||
ON data_export_requests FOR INSERT
|
||
WITH CHECK (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Admins can view all export requests"
|
||
ON data_export_requests FOR SELECT
|
||
USING (
|
||
EXISTS (
|
||
SELECT 1 FROM profiles
|
||
WHERE profiles.id = auth.uid()
|
||
AND profiles.role = 'admin'
|
||
)
|
||
);
|
||
|
||
-- account_deletion_requests policies
|
||
ALTER TABLE account_deletion_requests ENABLE ROW LEVEL SECURITY;
|
||
|
||
CREATE POLICY "Users can view their own deletion requests"
|
||
ON account_deletion_requests FOR SELECT
|
||
USING (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can create their own deletion requests"
|
||
ON account_deletion_requests FOR INSERT
|
||
WITH CHECK (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can update their own deletion requests"
|
||
ON account_deletion_requests FOR UPDATE
|
||
USING (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Admins can view all deletion requests"
|
||
ON account_deletion_requests FOR SELECT
|
||
USING (
|
||
EXISTS (
|
||
SELECT 1 FROM profiles
|
||
WHERE profiles.id = auth.uid()
|
||
AND profiles.role = 'admin'
|
||
)
|
||
);
|
||
|
||
CREATE POLICY "Admins can update deletion requests"
|
||
ON account_deletion_requests FOR UPDATE
|
||
USING (
|
||
EXISTS (
|
||
SELECT 1 FROM profiles
|
||
WHERE profiles.id = auth.uid()
|
||
AND profiles.role = 'admin'
|
||
)
|
||
);
|
||
|
||
-- 7. Create function to log audit events
|
||
CREATE OR REPLACE FUNCTION log_audit_event(
|
||
p_user_id UUID,
|
||
p_action TEXT,
|
||
p_resource_type TEXT,
|
||
p_resource_id UUID,
|
||
p_old_data JSONB DEFAULT NULL,
|
||
p_new_data JSONB DEFAULT NULL,
|
||
p_ip_address INET DEFAULT NULL,
|
||
p_user_agent TEXT DEFAULT NULL
|
||
) RETURNS UUID AS $$
|
||
DECLARE
|
||
v_audit_id UUID;
|
||
BEGIN
|
||
INSERT INTO audit_logs (
|
||
user_id,
|
||
action,
|
||
resource_type,
|
||
resource_id,
|
||
old_data,
|
||
new_data,
|
||
ip_address,
|
||
user_agent
|
||
) VALUES (
|
||
p_user_id,
|
||
p_action,
|
||
p_resource_type,
|
||
p_resource_id,
|
||
p_old_data,
|
||
p_new_data,
|
||
p_ip_address,
|
||
p_user_agent
|
||
) RETURNING id INTO v_audit_id;
|
||
|
||
RETURN v_audit_id;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 8. Create function to export user data (GDPR compliance)
|
||
CREATE OR REPLACE FUNCTION export_user_data(p_user_id UUID)
|
||
RETURNS JSONB AS $$
|
||
DECLARE
|
||
v_export_data JSONB;
|
||
v_user_email TEXT;
|
||
BEGIN
|
||
-- Get user email from auth.users
|
||
SELECT email INTO v_user_email
|
||
FROM auth.users
|
||
WHERE id = p_user_id;
|
||
|
||
SELECT jsonb_build_object(
|
||
'user_profile', (
|
||
SELECT jsonb_build_object(
|
||
'id', id,
|
||
'email', v_user_email,
|
||
'username', username,
|
||
'full_name', full_name,
|
||
'role', role,
|
||
'created_at', created_at
|
||
)
|
||
FROM profiles
|
||
WHERE id = p_user_id
|
||
),
|
||
'trips', (
|
||
SELECT COALESCE(jsonb_agg(
|
||
jsonb_build_object(
|
||
'id', id,
|
||
'title', title,
|
||
'destination', destination,
|
||
'start_date', start_date,
|
||
'end_date', end_date,
|
||
'created_at', created_at
|
||
)
|
||
), '[]'::jsonb)
|
||
FROM trips
|
||
WHERE user_id = p_user_id
|
||
),
|
||
'consents', (
|
||
SELECT COALESCE(jsonb_agg(
|
||
jsonb_build_object(
|
||
'consent_type', consent_type,
|
||
'consent_given', consent_given,
|
||
'consent_date', consent_date,
|
||
'consent_version', consent_version
|
||
)
|
||
), '[]'::jsonb)
|
||
FROM user_consents
|
||
WHERE user_id = p_user_id
|
||
),
|
||
'provider_profile', (
|
||
SELECT jsonb_build_object(
|
||
'id', id,
|
||
'company_name', company_name,
|
||
'contact_email', contact_email,
|
||
'phone', phone,
|
||
'created_at', created_at
|
||
)
|
||
FROM providers
|
||
WHERE user_id = p_user_id
|
||
)
|
||
) INTO v_export_data;
|
||
|
||
RETURN v_export_data;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 9. Create function to anonymize user data (soft delete)
|
||
CREATE OR REPLACE FUNCTION anonymize_user_data(p_user_id UUID)
|
||
RETURNS BOOLEAN AS $$
|
||
BEGIN
|
||
-- Anonymize user profile
|
||
UPDATE profiles
|
||
SET
|
||
username = 'deleted_' || substring(p_user_id::text, 1, 8),
|
||
full_name = 'Silinen Kullanıcı',
|
||
avatar_url = NULL,
|
||
bio = NULL,
|
||
updated_at = NOW()
|
||
WHERE id = p_user_id;
|
||
|
||
-- Anonymize provider profile if exists
|
||
UPDATE providers
|
||
SET
|
||
company_name = 'Silinen Sağlayıcı',
|
||
contact_email = 'deleted_' || substring(p_user_id::text, 1, 8) || '@anonymized.local',
|
||
phone = NULL,
|
||
website = NULL,
|
||
updated_at = NOW()
|
||
WHERE user_id = p_user_id;
|
||
|
||
-- Keep trips but mark as anonymized
|
||
UPDATE trips
|
||
SET
|
||
title = 'Silinen Seyahat',
|
||
updated_at = NOW()
|
||
WHERE user_id = p_user_id;
|
||
|
||
-- Log the anonymization
|
||
INSERT INTO audit_logs (user_id, action, resource_type, resource_id)
|
||
VALUES (p_user_id, 'anonymize', 'user', p_user_id);
|
||
|
||
RETURN TRUE;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER; |