wren
300 lines
15 KiB
PL/PgSQL
300 lines
15 KiB
PL/PgSQL
-- ==========================================================================
|
||
-- LeAudit Platform RBAC / User Schema Patch
|
||
-- 目标：在当前 leaudit_platform 新库中补齐用户、角色、权限、路由核心表
|
||
-- 设计原则：
|
||
-- 1. 兼容老系统 docauditai 的核心字段语义
|
||
-- 2. 新系统只做单地区隔离，地区字段统一使用 sso_users.area
|
||
-- 3. 采用 bigint 主键，兼容新库现有 leaudit_* bigint 引用字段
|
||
-- 4. 当前库里尚不存在 sso_users / roles / permissions / role_permissions / sys_routes / role_route / user_role
|
||
-- ============================================================================
|
||
|
||
BEGIN;
|
||
|
||
-- --------------------------------------------------------------------------
|
||
-- 1. 用户主表 sso_users
|
||
-- --------------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS sso_users (
|
||
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
||
sub VARCHAR(128) NOT NULL,
|
||
username VARCHAR(128) NOT NULL,
|
||
nick_name VARCHAR(128) NOT NULL,
|
||
phone_number VARCHAR(64),
|
||
email VARCHAR(256),
|
||
ou_id VARCHAR(128) NOT NULL,
|
||
ou_name VARCHAR(255) NOT NULL,
|
||
status SMALLINT NOT NULL DEFAULT 0,
|
||
is_leader BOOLEAN NOT NULL DEFAULT FALSE,
|
||
created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at TIMESTAMP WITHOUT TIME ZONE,
|
||
password VARCHAR(255),
|
||
try_count INTEGER,
|
||
try_login_time TIMESTAMP WITHOUT TIME ZONE,
|
||
area VARCHAR(64),
|
||
mq_person_uuid VARCHAR(64),
|
||
mq_account_uuid VARCHAR(64),
|
||
mq_synced_at TIMESTAMP WITHOUT TIME ZONE,
|
||
tenant_name VARCHAR(255),
|
||
dep_short_name VARCHAR(255),
|
||
dep_name VARCHAR(255),
|
||
CONSTRAINT sso_users_sub_key UNIQUE (sub)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_sso_users_sub ON sso_users(sub);
|
||
CREATE UNIQUE INDEX IF NOT EXISTS idx_sso_users_sub_unique ON sso_users(sub);
|
||
CREATE INDEX IF NOT EXISTS idx_sso_users_username ON sso_users(username);
|
||
CREATE INDEX IF NOT EXISTS idx_sso_users_area ON sso_users(area) WHERE area IS NOT NULL;
|
||
CREATE INDEX IF NOT EXISTS idx_sso_users_status ON sso_users(status);
|
||
CREATE INDEX IF NOT EXISTS idx_sso_users_deleted_at ON sso_users(deleted_at);
|
||
CREATE INDEX IF NOT EXISTS idx_sso_users_ou_id ON sso_users(ou_id);
|
||
CREATE INDEX IF NOT EXISTS idx_sso_users_is_leader ON sso_users(is_leader);
|
||
CREATE INDEX IF NOT EXISTS idx_sso_users_mq_person ON sso_users(mq_person_uuid);
|
||
CREATE INDEX IF NOT EXISTS idx_sso_users_mq_account ON sso_users(mq_account_uuid);
|
||
CREATE INDEX IF NOT EXISTS idx_sso_users_active_area_tenant
|
||
ON sso_users(status, deleted_at, area, tenant_name);
|
||
CREATE INDEX IF NOT EXISTS idx_sso_users_active_area_dep_expr
|
||
ON sso_users(status, deleted_at, area, (COALESCE(dep_name, dep_short_name, '')));
|
||
CREATE INDEX IF NOT EXISTS idx_sso_users_active_area_ou_name
|
||
ON sso_users(status, deleted_at, area, ou_name);
|
||
CREATE INDEX IF NOT EXISTS idx_sso_users_active_area_ou_id
|
||
ON sso_users(status, deleted_at, area, ou_id);
|
||
CREATE INDEX IF NOT EXISTS idx_sso_users_active_tenant_dep_expr
|
||
ON sso_users(status, deleted_at, tenant_name, (COALESCE(dep_name, dep_short_name, '')));
|
||
CREATE INDEX IF NOT EXISTS idx_sso_users_active_tenant_dep_ou_name
|
||
ON sso_users(status, deleted_at, tenant_name, (COALESCE(dep_name, dep_short_name, '')), ou_name);
|
||
|
||
COMMENT ON TABLE sso_users IS '用户主表：认证身份、组织信息、地区隔离基础字段统一沉淀在这里';
|
||
COMMENT ON COLUMN sso_users.sub IS '统一身份唯一标识，OAuth / SSO 主键';
|
||
COMMENT ON COLUMN sso_users.username IS '登录名/工号/展示账号';
|
||
COMMENT ON COLUMN sso_users.nick_name IS '用户真实姓名';
|
||
COMMENT ON COLUMN sso_users.area IS '用户主地区，当前系统唯一数据隔离字段';
|
||
COMMENT ON COLUMN sso_users.password IS '密码字段：当前阶段兼容旧值，后续应迁移为哈希';
|
||
COMMENT ON COLUMN sso_users.status IS '账户状态：0=正常，1=禁用';
|
||
COMMENT ON COLUMN sso_users.deleted_at IS '软删除时间';
|
||
|
||
-- --------------------------------------------------------------------------
|
||
-- 2. 角色表 roles
|
||
-- --------------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS roles (
|
||
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
||
role_key VARCHAR(64) NOT NULL,
|
||
role_name VARCHAR(128) NOT NULL,
|
||
data_scope VARCHAR(16) DEFAULT 'SELF',
|
||
description VARCHAR(255) DEFAULT '',
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
||
parent_role_id BIGINT,
|
||
priority INTEGER DEFAULT 0,
|
||
is_system_role BOOLEAN DEFAULT FALSE,
|
||
permissions_cache JSONB,
|
||
metadata JSONB,
|
||
CONSTRAINT roles_role_key_key UNIQUE (role_key),
|
||
CONSTRAINT fk_parent_role FOREIGN KEY (parent_role_id) REFERENCES roles(id) ON DELETE SET NULL,
|
||
CONSTRAINT chk_roles_data_scope CHECK (data_scope IN ('ALL', 'DEPT', 'SELF', 'GROUP'))
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_roles_parent_role_id ON roles(parent_role_id);
|
||
CREATE INDEX IF NOT EXISTS idx_roles_priority ON roles(priority DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_roles_permissions_cache ON roles USING GIN (permissions_cache);
|
||
|
||
COMMENT ON TABLE roles IS '角色表：当前主业务角色只使用 provincial_admin/admin/common，可选 super_admin';
|
||
COMMENT ON COLUMN roles.role_key IS '角色机器标识，例如 provincial_admin/admin/common';
|
||
COMMENT ON COLUMN roles.role_name IS '角色展示名称';
|
||
COMMENT ON COLUMN roles.data_scope IS '默认数据范围：ALL/DEPT/SELF；GROUP 仅保留兼容';
|
||
COMMENT ON COLUMN roles.priority IS '角色优先级，数值越大优先级越高';
|
||
|
||
-- --------------------------------------------------------------------------
|
||
-- 3. 菜单/路由表 sys_routes
|
||
-- --------------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS sys_routes (
|
||
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
||
route_path VARCHAR(255) NOT NULL,
|
||
route_name VARCHAR(128) NOT NULL,
|
||
component VARCHAR(255),
|
||
parent_id BIGINT,
|
||
route_title VARCHAR(255),
|
||
icon VARCHAR(128),
|
||
sort_order INTEGER DEFAULT 0,
|
||
is_hidden BOOLEAN DEFAULT FALSE,
|
||
is_cache BOOLEAN DEFAULT TRUE,
|
||
meta JSONB,
|
||
status INTEGER DEFAULT 0,
|
||
created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at TIMESTAMP WITHOUT TIME ZONE,
|
||
CONSTRAINT fk_parent_route FOREIGN KEY (parent_id) REFERENCES sys_routes(id) ON DELETE SET NULL
|
||
);
|
||
|
||
CREATE UNIQUE INDEX IF NOT EXISTS idx_routes_path ON sys_routes(route_path) WHERE deleted_at IS NULL;
|
||
CREATE INDEX IF NOT EXISTS idx_routes_parent_id ON sys_routes(parent_id);
|
||
CREATE INDEX IF NOT EXISTS idx_routes_status ON sys_routes(status);
|
||
|
||
COMMENT ON TABLE sys_routes IS '前端菜单/页面路由表：用于控制角色可见菜单，不替代 API 权限表';
|
||
COMMENT ON COLUMN sys_routes.route_path IS '前端路由路径';
|
||
COMMENT ON COLUMN sys_routes.status IS '状态：0=启用，1=禁用';
|
||
|
||
-- --------------------------------------------------------------------------
|
||
-- 4. 角色路由关系表 role_route
|
||
-- --------------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS role_route (
|
||
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
||
role_id BIGINT NOT NULL,
|
||
route_id BIGINT NOT NULL,
|
||
permission VARCHAR(8) DEFAULT 'RW',
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
||
status SMALLINT NOT NULL DEFAULT 1,
|
||
CONSTRAINT role_route_role_id_route_id_key UNIQUE (role_id, route_id),
|
||
CONSTRAINT fk_role_route_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
|
||
CONSTRAINT fk_role_route_route FOREIGN KEY (route_id) REFERENCES sys_routes(id) ON DELETE CASCADE,
|
||
CONSTRAINT chk_role_route_status CHECK (status IN (0, 1)),
|
||
CONSTRAINT chk_role_route_permission CHECK (permission IN ('R', 'W', 'RW'))
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_role_route_status ON role_route(status);
|
||
CREATE INDEX IF NOT EXISTS idx_role_route_role_status ON role_route(role_id, status);
|
||
|
||
COMMENT ON TABLE role_route IS '角色与菜单路由关联表';
|
||
COMMENT ON COLUMN role_route.permission IS '路由权限类型：R=读，W=写，RW=读写';
|
||
|
||
-- --------------------------------------------------------------------------
|
||
-- 5. 权限点表 permissions
|
||
-- --------------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS permissions (
|
||
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
||
permission_key VARCHAR(100) NOT NULL,
|
||
module VARCHAR(50) NOT NULL,
|
||
resource VARCHAR(50) NOT NULL,
|
||
action VARCHAR(50) NOT NULL,
|
||
description TEXT,
|
||
display_name VARCHAR(200),
|
||
permission_type VARCHAR(20) NOT NULL DEFAULT 'API',
|
||
is_system BOOLEAN NOT NULL DEFAULT FALSE,
|
||
metadata JSONB,
|
||
created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
created_by BIGINT,
|
||
updated_by BIGINT,
|
||
parent_id BIGINT,
|
||
sort_order INTEGER DEFAULT 0,
|
||
route_id BIGINT,
|
||
api_path VARCHAR(255),
|
||
api_method VARCHAR(16),
|
||
related_routes BIGINT[],
|
||
CONSTRAINT permissions_permission_key_key UNIQUE (permission_key),
|
||
CONSTRAINT permissions_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES permissions(id) ON DELETE SET NULL,
|
||
CONSTRAINT permissions_route_id_fkey FOREIGN KEY (route_id) REFERENCES sys_routes(id) ON DELETE SET NULL,
|
||
CONSTRAINT permissions_key_format_check CHECK (permission_key ~ '^[a-zA-Z0-9_*]+:[a-zA-Z0-9_*]+:[a-zA-Z0-9_*]+$')
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_permissions_module ON permissions(module);
|
||
CREATE INDEX IF NOT EXISTS idx_permissions_parent_id ON permissions(parent_id);
|
||
CREATE INDEX IF NOT EXISTS idx_permissions_route_id ON permissions(route_id);
|
||
CREATE INDEX IF NOT EXISTS idx_permissions_sort_order ON permissions(sort_order);
|
||
CREATE INDEX IF NOT EXISTS idx_permissions_system ON permissions(is_system);
|
||
CREATE INDEX IF NOT EXISTS idx_permissions_type ON permissions(permission_type);
|
||
CREATE INDEX IF NOT EXISTS idx_permissions_metadata ON permissions USING GIN (metadata);
|
||
|
||
COMMENT ON TABLE permissions IS '权限点定义表：统一使用 module:resource:action 风格 permission_key';
|
||
COMMENT ON COLUMN permissions.permission_key IS '权限键，例如 documents:list:read、audit:run:execute';
|
||
COMMENT ON COLUMN permissions.related_routes IS '共享权限可关联多个路由 ID';
|
||
|
||
-- --------------------------------------------------------------------------
|
||
-- 6. 角色权限关系表 role_permissions
|
||
-- --------------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS role_permissions (
|
||
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
||
role_id BIGINT NOT NULL,
|
||
permission_id BIGINT NOT NULL,
|
||
grant_type VARCHAR(10) NOT NULL DEFAULT 'GRANT',
|
||
data_scope VARCHAR(20),
|
||
condition_filter JSONB,
|
||
metadata JSONB,
|
||
created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
created_by BIGINT,
|
||
updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_by BIGINT,
|
||
CONSTRAINT unique_role_permission UNIQUE (role_id, permission_id),
|
||
CONSTRAINT role_permissions_role_id_fkey FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
|
||
CONSTRAINT role_permissions_permission_id_fkey FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE,
|
||
CONSTRAINT chk_role_permissions_grant_type CHECK (grant_type IN ('GRANT', 'DENY')),
|
||
CONSTRAINT chk_role_permissions_data_scope CHECK (data_scope IS NULL OR data_scope IN ('ALL', 'DEPT', 'SELF', 'GROUP'))
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_role_permissions_role_id ON role_permissions(role_id);
|
||
CREATE INDEX IF NOT EXISTS idx_role_permissions_permission_id ON role_permissions(permission_id);
|
||
CREATE INDEX IF NOT EXISTS idx_role_permissions_grant_type ON role_permissions(grant_type);
|
||
CREATE INDEX IF NOT EXISTS idx_role_permissions_data_scope ON role_permissions(data_scope);
|
||
CREATE INDEX IF NOT EXISTS idx_role_permissions_lookup ON role_permissions(role_id, grant_type);
|
||
CREATE INDEX IF NOT EXISTS idx_role_permissions_condition ON role_permissions USING GIN (condition_filter);
|
||
|
||
COMMENT ON TABLE role_permissions IS '角色权限关联表：grant_type 兼容旧系统 DENY 语义，当前新系统默认只配置 GRANT';
|
||
COMMENT ON COLUMN role_permissions.data_scope IS '数据范围：ALL/DEPT/SELF，GROUP 仅保留兼容';
|
||
|
||
-- --------------------------------------------------------------------------
|
||
-- 7. 用户角色关系表 user_role
|
||
-- --------------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS user_role (
|
||
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
||
user_id BIGINT NOT NULL,
|
||
role_id BIGINT NOT NULL,
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
||
CONSTRAINT user_role_user_id_role_id_key UNIQUE (user_id, role_id),
|
||
CONSTRAINT fk_user_role_user FOREIGN KEY (user_id) REFERENCES sso_users(id) ON DELETE CASCADE,
|
||
CONSTRAINT fk_user_role_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_user_role_user_role ON user_role(user_id, role_id);
|
||
|
||
COMMENT ON TABLE user_role IS '用户与角色关联表；一个用户允许挂多个角色';
|
||
|
||
-- --------------------------------------------------------------------------
|
||
-- 8. 给现有 leaudit_* 业务表补用户外键
|
||
-- 当前库中这些字段均为 bigint，且当前全为空，可安全补充外键。
|
||
-- --------------------------------------------------------------------------
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM pg_constraint WHERE conname = 'fk_leaudit_document_files_created_by'
|
||
) THEN
|
||
ALTER TABLE leaudit_document_files
|
||
ADD CONSTRAINT fk_leaudit_document_files_created_by
|
||
FOREIGN KEY (created_by) REFERENCES sso_users(id) ON DELETE SET NULL;
|
||
END IF;
|
||
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM pg_constraint WHERE conname = 'fk_leaudit_audit_runs_trigger_user_id'
|
||
) THEN
|
||
ALTER TABLE leaudit_audit_runs
|
||
ADD CONSTRAINT fk_leaudit_audit_runs_trigger_user_id
|
||
FOREIGN KEY (trigger_user_id) REFERENCES sso_users(id) ON DELETE SET NULL;
|
||
END IF;
|
||
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM pg_constraint WHERE conname = 'fk_leaudit_rule_sets_owner_user_id'
|
||
) THEN
|
||
ALTER TABLE leaudit_rule_sets
|
||
ADD CONSTRAINT fk_leaudit_rule_sets_owner_user_id
|
||
FOREIGN KEY (owner_user_id) REFERENCES sso_users(id) ON DELETE SET NULL;
|
||
END IF;
|
||
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM pg_constraint WHERE conname = 'fk_leaudit_rule_versions_editor_user_id'
|
||
) THEN
|
||
ALTER TABLE leaudit_rule_versions
|
||
ADD CONSTRAINT fk_leaudit_rule_versions_editor_user_id
|
||
FOREIGN KEY (editor_user_id) REFERENCES sso_users(id) ON DELETE SET NULL;
|
||
END IF;
|
||
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM pg_constraint WHERE conname = 'fk_leaudit_rule_versions_publisher_user_id'
|
||
) THEN
|
||
ALTER TABLE leaudit_rule_versions
|
||
ADD CONSTRAINT fk_leaudit_rule_versions_publisher_user_id
|
||
FOREIGN KEY (publisher_user_id) REFERENCES sso_users(id) ON DELETE SET NULL;
|
||
END IF;
|
||
END $$;
|
||
|
||
COMMIT;
|