如何设计一个可扩展的用户权限系统

-- 1. 用户（不变）
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  status VARCHAR(20) DEFAULT 'active' -- active/inactive/pending
);
-- 2. 权限（静态定义，未来新增直接INSERT）
CREATE TABLE permissions (
  id SERIAL PRIMARY KEY,
  code VARCHAR(100) UNIQUE NOT NULL,      -- 如 'document:sign', 'audit:log:view'
  description TEXT,
  category VARCHAR(50),                   -- 'content', 'finance', 'admin'
  created_at TIMESTAMPTZ DEFAULT NOW()
);
-- 3. 角色（纯名称容器，无逻辑）
CREATE TABLE roles (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL,       -- 'hr-manager', 'compliance-officer'
  description TEXT,
  is_system BOOLEAN DEFAULT FALSE         -- 是否系统内置（防误删）
);
-- 4. 角色-权限关联（多对多）
CREATE TABLE role_permissions (
  role_id INTEGER REFERENCES roles(id) ON DELETE CASCADE,
  permission_id INTEGER REFERENCES permissions(id) ON DELETE CASCADE,
  PRIMARY KEY (role_id, permission_id)
);
-- 5. 用户-角色关联（支持多角色）
CREATE TABLE user_roles (
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  role_id INTEGER REFERENCES roles(id) ON DELETE CASCADE,
  granted_at TIMESTAMPTZ DEFAULT NOW(),
  PRIMARY KEY (user_id, role_id)
);
-- 6. 策略表（ABAC 规则存储）
CREATE TABLE policies (
  id SERIAL PRIMARY KEY,
  code VARCHAR(100) UNIQUE NOT NULL,      -- 'policy:own-docs-only'
  expression TEXT NOT NULL,               -- JSON 或自定义 DSL，如 {"and": [{"eq": ["resource.owner_id", "subject.id"]}, ...]}
  effect VARCHAR(10) CHECK (effect IN ('allow','deny')) DEFAULT 'allow',
  enabled BOOLEAN DEFAULT TRUE,
  scope VARCHAR(20) DEFAULT 'global'      -- 'global' | 'tenant' | 'department'
);
-- 7. 策略绑定（哪些权限受此策略约束）
CREATE TABLE policy_bindings (
  policy_id INTEGER REFERENCES policies(id) ON DELETE CASCADE,
  permission_id INTEGER REFERENCES permissions(id) ON DELETE CASCADE,
  PRIMARY KEY (policy_id, permission_id)
);