128 lines
5.7 KiB
SQL
128 lines
5.7 KiB
SQL
-- Casbin RBAC 权限管理表
|
||
-- 这些表用于存储 casbin 的 RBAC 模型和策略
|
||
|
||
-- 创建 casbin_rule 表，用于存储权限策略
|
||
CREATE TABLE IF NOT EXISTS casbin_rule (
|
||
id SERIAL PRIMARY KEY,
|
||
ptype VARCHAR(10) NOT NULL, -- 策略类型：p(策略), g(角色组), e(效果), m(模型)
|
||
v0 VARCHAR(256), -- 角色或用户
|
||
v1 VARCHAR(256), -- 资源
|
||
v2 VARCHAR(256), -- 操作
|
||
v3 VARCHAR(256), -- 额外参数
|
||
v4 VARCHAR(256), -- 额外参数
|
||
v5 VARCHAR(256) -- 额外参数
|
||
);
|
||
|
||
-- 创建索引以提高查询性能
|
||
CREATE INDEX IF NOT EXISTS idx_casbin_rule_ptype ON casbin_rule(ptype);
|
||
CREATE INDEX IF NOT EXISTS idx_casbin_rule_v0 ON casbin_rule(v0);
|
||
CREATE INDEX IF NOT EXISTS idx_casbin_rule_v1 ON casbin_rule(v1);
|
||
CREATE INDEX IF NOT EXISTS idx_casbin_rule_v2 ON casbin_rule(v2);
|
||
|
||
-- 插入默认的 RBAC 模型配置
|
||
-- 这里使用标准的 RBAC 模型：用户 -> 角色 -> 权限
|
||
INSERT INTO casbin_rule (ptype, v0, v1, v2, v3, v4, v5) VALUES
|
||
-- 策略规则：角色可以访问的资源
|
||
('p', 'admin', 'settings', 'read', '', '', ''),
|
||
('p', 'admin', 'settings', 'write', '', '', ''),
|
||
('p', 'admin', 'settings', 'delete', '', '', ''),
|
||
('p', 'admin', 'users', 'read', '', '', ''),
|
||
('p', 'admin', 'users', 'write', '', '', ''),
|
||
('p', 'admin', 'users', 'delete', '', '', ''),
|
||
('p', 'admin', 'invite_codes', 'read', '', '', ''),
|
||
('p', 'admin', 'invite_codes', 'write', '', '', ''),
|
||
('p', 'admin', 'invite_codes', 'delete', '', '', ''),
|
||
('p', 'admin', 'pages', 'read', '', '', ''),
|
||
('p', 'admin', 'pages', 'write', '', '', ''),
|
||
('p', 'admin', 'pages', 'delete', '', '', ''),
|
||
('p', 'admin', 'page_blocks', 'read', '', '', ''),
|
||
('p', 'admin', 'page_blocks', 'write', '', '', ''),
|
||
('p', 'admin', 'page_blocks', 'delete', '', '', ''),
|
||
|
||
('p', 'user', 'settings', 'read', '', '', ''),
|
||
('p', 'user', 'pages', 'read', '', '', ''),
|
||
('p', 'user', 'page_blocks', 'read', '', '', ''),
|
||
('p', 'user', 'invite_codes', 'read', '', '', ''),
|
||
('p', 'user', 'invite_codes', 'write', '', '', ''),
|
||
|
||
-- 角色组规则：用户属于哪个角色
|
||
('g', 'admin@example.com', 'admin', '', '', '', ''),
|
||
('g', 'user@example.com', 'user', '', '', '', '');
|
||
|
||
-- 创建权限资源表，用于管理可配置的权限
|
||
CREATE TABLE IF NOT EXISTS permissions (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
name VARCHAR(255) NOT NULL UNIQUE,
|
||
description TEXT,
|
||
resource VARCHAR(255) NOT NULL,
|
||
action VARCHAR(255) NOT NULL,
|
||
is_active BOOLEAN DEFAULT true,
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
-- 创建角色权限关联表
|
||
CREATE TABLE IF NOT EXISTS role_permissions (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
role_name VARCHAR(255) NOT NULL,
|
||
permission_id UUID NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
|
||
granted_by UUID REFERENCES users(id),
|
||
granted_at TIMESTAMPTZ DEFAULT NOW(),
|
||
UNIQUE(role_name, permission_id)
|
||
);
|
||
|
||
-- 创建用户角色关联表（支持多角色）
|
||
CREATE TABLE IF NOT EXISTS user_roles (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
role_name VARCHAR(255) NOT NULL,
|
||
granted_by UUID REFERENCES users(id),
|
||
granted_at TIMESTAMPTZ DEFAULT NOW(),
|
||
expires_at TIMESTAMPTZ,
|
||
UNIQUE(user_id, role_name)
|
||
);
|
||
|
||
-- 创建索引
|
||
CREATE INDEX IF NOT EXISTS idx_permissions_resource ON permissions(resource);
|
||
CREATE INDEX IF NOT EXISTS idx_permissions_action ON permissions(action);
|
||
CREATE INDEX IF NOT EXISTS idx_role_permissions_role ON role_permissions(role_name);
|
||
CREATE INDEX IF NOT EXISTS idx_user_roles_user_id ON user_roles(user_id);
|
||
CREATE INDEX IF NOT EXISTS idx_user_roles_role ON user_roles(role_name);
|
||
|
||
-- 插入默认权限
|
||
INSERT INTO permissions (name, description, resource, action, is_active, created_at, updated_at) VALUES
|
||
-- 设置管理权限
|
||
('settings_read', '读取系统设置', 'settings', 'read', true, NOW(), NOW()),
|
||
('settings_write', '修改系统设置', 'settings', 'write', true, NOW(), NOW()),
|
||
('settings_delete', '删除系统设置', 'settings', 'delete', true, NOW(), NOW()),
|
||
|
||
-- 用户管理权限
|
||
('users_read', '读取用户信息', 'users', 'read', true, NOW(), NOW()),
|
||
('users_write', '修改用户信息', 'users', 'write', true, NOW(), NOW()),
|
||
('users_delete', '删除用户', 'users', 'delete', true, NOW(), NOW()),
|
||
|
||
-- 邀请码管理权限
|
||
('invite_codes_read', '读取邀请码', 'invite_codes', 'read', true, NOW(), NOW()),
|
||
('invite_codes_write', '创建邀请码', 'invite_codes', 'write', true, NOW(), NOW()),
|
||
('invite_codes_delete', '删除邀请码', 'invite_codes', 'delete', true, NOW(), NOW()),
|
||
|
||
-- 页面管理权限
|
||
('pages_read', '读取页面', 'pages', 'read', true, NOW(), NOW()),
|
||
('pages_write', '创建/修改页面', 'pages', 'write', true, NOW(), NOW()),
|
||
('pages_delete', '删除页面', 'pages', 'delete', true, NOW(), NOW()),
|
||
|
||
-- 页面块管理权限
|
||
('page_blocks_read', '读取页面块', 'page_blocks', 'read', true, NOW(), NOW()),
|
||
('page_blocks_write', '创建/修改页面块', 'page_blocks', 'write', true, NOW(), NOW()),
|
||
('page_blocks_delete', '删除页面块', 'page_blocks', 'delete', true, NOW(), NOW());
|
||
|
||
-- 为角色分配权限
|
||
INSERT INTO role_permissions (role_name, permission_id, granted_by, granted_at)
|
||
SELECT 'admin', id, NULL, NOW() FROM permissions;
|
||
|
||
INSERT INTO role_permissions (role_name, permission_id, granted_by, granted_at)
|
||
SELECT 'user', id, NULL, NOW() FROM permissions WHERE action = 'read' OR (resource = 'invite_codes' AND action = 'write');
|
||
|
||
-- 创建触发器更新 updated_at
|
||
CREATE TRIGGER update_permissions_updated_at BEFORE UPDATE
|
||
ON permissions FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); |