Как в БД указать несколько ролей пользователя?

Как в БД указать несколько ролей пользователя?

Это важный вопрос о дизайне схемы БД. Есть несколько подходов, у каждого свои плюсы и минусы.

Подход 1: Отдельная таблица связей (РЕКОМЕНДУЕТСЯ)

Это нормализованное решение, которое я использую в production.

-- Таблица пользователей
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Таблица ролей (справочник)
CREATE TABLE roles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(50) UNIQUE NOT NULL
);

INSERT INTO roles (name) VALUES ('admin'), ('moderator'), ('user'), ('viewer');

-- Таблица связей (many-to-many)
CREATE TABLE user_roles (
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role_id UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  assigned_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  PRIMARY KEY (user_id, role_id)
);

-- Индекс для быстрого поиска по пользователю
CREATE INDEX idx_user_roles_user_id ON user_roles(user_id);
CREATE INDEX idx_user_roles_role_id ON user_roles(role_id);

Преимущества:

• Полная нормализация
• Легко добавлять/удалять роли
• Хорошо масштабируется
• Можно хранить доп. данные (assigned_at, assigned_by)

Недостатки:

• Нужна JOIN операция для получения ролей
• Чуть медленнее на больших объёмах

Получение ролей пользователя:

SELECT r.name, r.id
FROM user_roles ur
JOIN roles r ON ur.role_id = r.id
WHERE ur.user_id = @user_id;

В коде (Node.js/TypeScript):

async function getUserRoles(userId: string): Promise<string[]> {
  const result = await db.query(
    `SELECT r.name FROM user_roles ur
     JOIN roles r ON ur.role_id = r.id
     WHERE ur.user_id = $1`,
    [userId]
  );
  return result.rows.map(row => row.name);
}

// Проверка роли
async function hasRole(userId: string, roleName: string): Promise<boolean> {
  const result = await db.query(
    `SELECT 1 FROM user_roles ur
     JOIN roles r ON ur.role_id = r.id
     WHERE ur.user_id = $1 AND r.name = $2`,
    [userId, roleName]
  );
  return result.rows.length > 0;
}

Подход 2: JSON массив (денормализация)

Для простых случаев и отсутствия частых изменений ролей.

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  roles TEXT[] DEFAULT ARRAY[]::TEXT[], -- массив строк
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Или JSON
CREATE TABLE users_json (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  roles JSONB DEFAULT '[]'::JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Работа с массивом:

-- Добавить роль
UPDATE users
SET roles = array_append(roles, 'admin')
WHERE id = @user_id AND NOT roles @> ARRAY['admin'];

-- Удалить роль
UPDATE users
SET roles = array_remove(roles, 'admin')
WHERE id = @user_id;

-- Проверить наличие роли
SELECT * FROM users WHERE roles @> ARRAY['admin'];

Работа с JSONB:

-- Добавить роль
UPDATE users_json
SET roles = jsonb_set(roles, '{0}', '"admin"')
WHERE id = @user_id;

-- Проверить
SELECT * FROM users_json WHERE roles @> '"admin"';

В коде:

async function getUserRoles(userId: string): Promise<string[]> {
  const result = await db.query(
    'SELECT roles FROM users WHERE id = $1',
    [userId]
  );
  return result.rows[0]?.roles || [];
}

async function addRole(userId: string, roleName: string): Promise<void> {
  await db.query(
    `UPDATE users SET roles = array_append(roles, $2)
     WHERE id = $1 AND NOT roles @> ARRAY[$2]`,
    [userId, roleName]
  );
}

Преимущества:

• Простота — нет JOINов
• Быстро для чтения
• Мало данных в памяти

Недостатки:

• Денормализация
• Сложнее добавлять/удалять
• Сложнее делать запросы (например, "найти всех админов")
• Нельзя хранить доп. данные

Подход 3: Битовые флаги (для небольшого кол-во ролей)

Для очень частых проверок ролей с минимальными затратами.

CREATE TABLE users (
  id UUID PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  role_flags SMALLINT DEFAULT 0, -- 2 байта = 16 ролей
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Битовые позиции
-- bit 0 = admin (1)
-- bit 1 = moderator (2)
-- bit 2 = viewer (4)
-- bit 3 = user (8)

SQL операции:

-- Добавить роль (администратор = 1)
UPDATE users SET role_flags = role_flags | 1 WHERE id = @user_id;

-- Удалить роль
UPDATE users SET role_flags = role_flags & ~1 WHERE id = @user_id;

-- Проверить роль
SELECT * FROM users WHERE (role_flags & 1) > 0; -- админы

В коде:

const ROLES = {
  ADMIN: 1 << 0,      // 1
  MODERATOR: 1 << 1,  // 2
  VIEWER: 1 << 2,     // 4
  USER: 1 << 3,       // 8
};

function hasRole(userFlags: number, role: number): boolean {
  return (userFlags & role) > 0;
}

function addRole(userFlags: number, role: number): number {
  return userFlags | role;
}

function removeRole(userFlags: number, role: number): number {
  return userFlags & ~role;
}

Преимущества:

• Супер быстро
• Минимум памяти

Недостатки:

• Максимум ~16 ролей
• Неинтуитивно
• Сложно масштабировать

Мое рекомендуемое решение

Для большинства projects я использую Подход 1 (отдельная таблица):

// User entity в ORM (например, TypeORM, Prisma)
class User {
  id: string;
  email: string;
  roles: Role[]; // relation
}

class Role {
  id: string;
  name: string;
  users: User[]; // relation
}

// Middleware для проверки ролей
function requireRole(...requiredRoles: string[]) {
  return async (req: Request, res: Response, next: NextFunction) => {
    const userRoles = await getUserRoles(req.user.id);
    const hasPermission = requiredRoles.some(r => userRoles.includes(r));
    if (!hasPermission) {
      return res.status(403).json({ error: 'Forbidden' });
    }
    next();
  };
}

// Использование
router.post('/admin/users', requireRole('admin'), createUser);

Вывод: Выбирай таблицу связей для гибкости и масштабируемости. Денормализация (JSON) годится только для простых случаев без частых изменений.