61 lines
3.1 KiB
SQL
61 lines
3.1 KiB
SQL
-- Role-based access for the admin panel.
|
|
--
|
|
-- Until now admin access was one flag: users.role = 'admin'. This replaces it
|
|
-- with named roles carrying explicit permissions, so a moderator can resolve
|
|
-- reports without also being able to ban users or gift stickers to everyone.
|
|
--
|
|
-- Three system roles are seeded and cannot be edited or deleted; superadmins
|
|
-- can define further custom roles from any subset of the permission catalog
|
|
-- (which lives in code — see internal/rbac). The one thing a custom role can
|
|
-- never hold is admins:manage: granting, changing, revoking and blocking staff
|
|
-- stays with superadmins.
|
|
CREATE TABLE IF NOT EXISTS admin_roles (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
key VARCHAR(40) NOT NULL UNIQUE,
|
|
name VARCHAR(80) NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
-- System roles are seeded below and are read-only.
|
|
is_system BOOLEAN NOT NULL DEFAULT FALSE,
|
|
-- "resource:action" strings, or '*' for everything (superadmin only).
|
|
permissions TEXT[] NOT NULL DEFAULT '{}',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
INSERT INTO admin_roles (key, name, description, is_system, permissions) VALUES
|
|
('superadmin', 'Superadmin',
|
|
'Everything, including managing other staff.', TRUE, ARRAY['*']),
|
|
('admin', 'Admin',
|
|
'Runs the platform day to day. Cannot manage staff.', TRUE, ARRAY[
|
|
'stats:read','users:read','users:notify','users:ban','users:limits',
|
|
'disciples:manage','posts:moderate','comments:moderate','collections:read',
|
|
'feedback:read','feedback:reply','feedback:resolve',
|
|
'reports:read','reports:resolve','vani:read','vani:write',
|
|
'rewards:read','rewards:write','rewards:send','rewards:delete',
|
|
'broadcasts:read','broadcasts:write','broadcasts:publish',
|
|
'short_urls:read','quizzes:read','quizzes:write','quizzes:delete','quizzes:results']),
|
|
('moderator', 'Moderator',
|
|
'Keeps the community healthy: reports, moderation and feedback.', TRUE, ARRAY[
|
|
'stats:read','users:read','posts:moderate','comments:moderate',
|
|
'collections:read','feedback:read','feedback:reply','feedback:resolve',
|
|
'reports:read','reports:resolve'])
|
|
ON CONFLICT (key) DO NOTHING;
|
|
|
|
-- Staff membership. NULL = not staff.
|
|
ALTER TABLE users
|
|
ADD COLUMN IF NOT EXISTS admin_role_id UUID REFERENCES admin_roles(id) ON DELETE RESTRICT;
|
|
|
|
-- A superadmin can suspend another staff member's panel access without taking
|
|
-- their role away, so restoring them is one click rather than a re-grant.
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS admin_blocked_at TIMESTAMPTZ;
|
|
|
|
CREATE INDEX IF NOT EXISTS users_admin_role_idx
|
|
ON users (admin_role_id) WHERE admin_role_id IS NOT NULL;
|
|
|
|
-- Everyone who was an admin under the old single flag becomes a superadmin:
|
|
-- they had unrestricted access before, so anything less would lock existing
|
|
-- staff out of the panel the moment this deploys.
|
|
UPDATE users
|
|
SET admin_role_id = (SELECT id FROM admin_roles WHERE key = 'superadmin')
|
|
WHERE role = 'admin' AND admin_role_id IS NULL;
|