165 lines
6.1 KiB
PL/PgSQL
165 lines
6.1 KiB
PL/PgSQL
-- =============================================================================
|
|
-- Migration 006: Superuser Hierarchy
|
|
-- =============================================================================
|
|
-- Implements:
|
|
-- 1. SUPERUSER role - god-like permissions
|
|
-- 2. is_initial_superuser flag - marks the founder (cannot be deleted)
|
|
-- 3. Initial superuser can only transfer their status (self-transfer only)
|
|
-- 4. Superusers can promote/demote other superusers (but not initial)
|
|
-- 5. Admins cannot touch superusers
|
|
-- =============================================================================
|
|
|
|
-- Add SUPERUSER to the user_role enum
|
|
ALTER TYPE user_role ADD VALUE IF NOT EXISTS 'SUPERUSER';
|
|
|
|
-- Add is_initial_superuser column to users table
|
|
-- This marks the very first user who gets god-like, non-removable status
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS is_initial_superuser BOOLEAN DEFAULT false;
|
|
|
|
-- Add is_protected column to prevent deletion of critical users
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS is_protected BOOLEAN DEFAULT false;
|
|
|
|
-- Create index for quick lookup of initial superuser
|
|
CREATE INDEX IF NOT EXISTS idx_users_initial_superuser ON users(is_initial_superuser) WHERE is_initial_superuser = true;
|
|
|
|
-- Create superuser_transfers table to track initial superuser transfers
|
|
-- This is an audit trail for when the initial superuser status is transferred
|
|
CREATE TABLE IF NOT EXISTS superuser_transfers (
|
|
id SERIAL PRIMARY KEY,
|
|
from_user_id INTEGER NOT NULL REFERENCES users(id),
|
|
to_user_id INTEGER NOT NULL REFERENCES users(id),
|
|
transferred_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
reason TEXT,
|
|
-- Constraint: from_user must have been initial superuser
|
|
CONSTRAINT valid_transfer CHECK (from_user_id != to_user_id)
|
|
);
|
|
|
|
-- Create index on superuser_transfers
|
|
CREATE INDEX IF NOT EXISTS idx_superuser_transfers_from ON superuser_transfers(from_user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_superuser_transfers_to ON superuser_transfers(to_user_id);
|
|
|
|
-- Create trigger to ensure only one initial superuser exists at a time
|
|
CREATE OR REPLACE FUNCTION ensure_single_initial_superuser()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
IF NEW.is_initial_superuser = true THEN
|
|
-- Check if there's already an initial superuser (other than this user)
|
|
IF EXISTS (
|
|
SELECT 1 FROM users
|
|
WHERE is_initial_superuser = true
|
|
AND id != NEW.id
|
|
) THEN
|
|
RAISE EXCEPTION 'Only one initial superuser can exist at a time. Transfer must be explicit.';
|
|
END IF;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER check_initial_superuser
|
|
BEFORE INSERT OR UPDATE ON users
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION ensure_single_initial_superuser();
|
|
|
|
-- Create trigger to prevent deletion of initial superuser
|
|
CREATE OR REPLACE FUNCTION prevent_initial_superuser_deletion()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
IF OLD.is_initial_superuser = true THEN
|
|
RAISE EXCEPTION 'Cannot delete the initial superuser. Transfer ownership first.';
|
|
END IF;
|
|
IF OLD.is_protected = true THEN
|
|
RAISE EXCEPTION 'Cannot delete a protected user.';
|
|
END IF;
|
|
RETURN OLD;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER prevent_delete_initial_superuser
|
|
BEFORE DELETE ON users
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION prevent_initial_superuser_deletion();
|
|
|
|
-- Create trigger to prevent removal of SUPERUSER role from initial superuser
|
|
CREATE OR REPLACE FUNCTION prevent_initial_superuser_role_removal()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
is_initial BOOLEAN;
|
|
BEGIN
|
|
-- Check if the user being demoted is the initial superuser
|
|
SELECT is_initial_superuser INTO is_initial FROM users WHERE id = OLD.user_id;
|
|
|
|
IF is_initial = true AND OLD.role = 'SUPERUSER' THEN
|
|
RAISE EXCEPTION 'Cannot remove SUPERUSER role from the initial superuser. Transfer ownership first.';
|
|
END IF;
|
|
|
|
RETURN OLD;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER prevent_initial_superuser_demotion
|
|
BEFORE DELETE ON user_roles
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION prevent_initial_superuser_role_removal();
|
|
|
|
-- Create function to transfer initial superuser status
|
|
-- This MUST be called by the current initial superuser
|
|
CREATE OR REPLACE FUNCTION transfer_initial_superuser(
|
|
current_su_id INTEGER,
|
|
new_su_id INTEGER,
|
|
transfer_reason TEXT DEFAULT NULL
|
|
)
|
|
RETURNS BOOLEAN AS $$
|
|
DECLARE
|
|
is_current_initial BOOLEAN;
|
|
new_user_exists BOOLEAN;
|
|
BEGIN
|
|
-- Verify current user is the initial superuser
|
|
SELECT is_initial_superuser INTO is_current_initial
|
|
FROM users WHERE id = current_su_id;
|
|
|
|
IF is_current_initial IS NULL OR is_current_initial = false THEN
|
|
RAISE EXCEPTION 'Only the current initial superuser can transfer this status';
|
|
END IF;
|
|
|
|
-- Verify new user exists
|
|
SELECT EXISTS(SELECT 1 FROM users WHERE id = new_su_id) INTO new_user_exists;
|
|
IF new_user_exists = false THEN
|
|
RAISE EXCEPTION 'Target user does not exist';
|
|
END IF;
|
|
|
|
-- Cannot transfer to self
|
|
IF current_su_id = new_su_id THEN
|
|
RAISE EXCEPTION 'Cannot transfer to yourself';
|
|
END IF;
|
|
|
|
-- Begin transfer
|
|
-- 1. Remove initial_superuser flag from current user
|
|
UPDATE users SET is_initial_superuser = false WHERE id = current_su_id;
|
|
|
|
-- 2. Set initial_superuser flag on new user
|
|
UPDATE users SET is_initial_superuser = true, is_protected = true WHERE id = new_su_id;
|
|
|
|
-- 3. Ensure new user has SUPERUSER role
|
|
INSERT INTO user_roles (user_id, role, created_at)
|
|
VALUES (new_su_id, 'SUPERUSER', NOW())
|
|
ON CONFLICT (user_id, role) DO NOTHING;
|
|
|
|
-- 4. Record the transfer
|
|
INSERT INTO superuser_transfers (from_user_id, to_user_id, reason)
|
|
VALUES (current_su_id, new_su_id, transfer_reason);
|
|
|
|
RETURN true;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Comment explaining the hierarchy
|
|
COMMENT ON COLUMN users.is_initial_superuser IS
|
|
'Marks the founding superuser. Cannot be deleted, can only transfer status to another user via self-initiated transfer.';
|
|
|
|
COMMENT ON COLUMN users.is_protected IS
|
|
'Protected users cannot be deleted. Initial superuser is always protected.';
|
|
|
|
COMMENT ON TABLE superuser_transfers IS
|
|
'Audit trail for initial superuser ownership transfers. Only the initial superuser can initiate a transfer.';
|