access-rights.definition.js

/** eslint camelcase: 0 */
const accessRightsDefinition = {
  exercise: {
    view: 'exercise.view',
    do: 'exercise.do'
  },
  session: {
    view: 'session.view',
    do: 'session.do'
  },
  module: {
    create: 'module.create',
    delete: 'module.delete',
    edit_admin: 'module.edit_admin',
    edit: 'module.edit',
    participate: 'module.participate',
    view: 'module.view'
  },
  exercise_attempt: {
    delete: 'exercise_attempt.delete'
  },
  user: {
    view: 'user.view',
    manage: 'user.manage',
    see_dashboard: 'user.see_dashboard'
  },
  role: {
    manage: 'role.manage',
    add_to_user: 'role.add_to_user'
  }
}

module.exports = accessRightsDefinition

module-user-role.model.js

/* eslint camelcase: 0 */
const PostgresStore = require('../utils/PostgresStore.js')
const debug = require('debug')('hephaistos:module-user-role.model.js')
const Role = require('./role.model.js')

class ModuleUserRole {
  /** @type {Number} */
  module_id
  /** @type {Number} */
  user_id
  /** @type {Number} */
  role_id

  /**
   * @param {Number} moduleId
   * @param {Number} userId
   */
  static async remove (moduleId, userId) {
    const result = await PostgresStore.client.query({
      text: `DELETE FROM ${ModuleUserRole.tableName} 
        WHERE module_id=$1 AND user_id=$2`,
      values: [moduleId, userId]
    })
    debug('result', result.rows[0])
    return result.rows[0]
  }

  /**
   * @param {Number} moduleId
   * @param {Number} userId
   * @param {Number} roleId
   */
  static async add (moduleId, userId, roleId) {
    const result = await PostgresStore.client.query({
      text: `INSERT INTO ${ModuleUserRole.tableName} 
        (module_id, user_id, role_id) VALUES ($1, $2, $3)
        ON CONFLICT (module_id, user_id)
        DO UPDATE
         SET role_id = $3
      RETURNING *`,
      values: [moduleId, userId, roleId]
    })
    debug('result', result.rows[0])
    return result.rows[0]
  }

  static toSqlTable () {
    const User = require('./user.model.js')
    const Module = require('./module.model.js')
    return [`
      CREATE TABLE ${ModuleUserRole.tableName} (
        module_id INTEGER REFERENCES ${Module.tableName}(id),
        user_id INTEGER REFERENCES ${User.tableName}(id),
        role_id INTEGER REFERENCES ${Role.tableName}(id)
      )`,
      `ALTER TABLE ${ModuleUserRole.tableName} ADD UNIQUE(module_id, user_id)`
    ]
  }
}

/** @type {String} */
ModuleUserRole.tableName = 'module_user_role'

module.exports = ModuleUserRole

module.model.js

/* eslint camelcase: 0 */
const PostgresStore = require('../utils/PostgresStore.js')
const debug = require('debug')('hephaistos:module.model.js')
const ModuleUserRole = require('./module-user-role.model.js')
const RoleAccessRight = require('./role-access-right.model.js')
const accessRights = require('./access-rights.definition.js')

class Module {
  /** @type {Number} */
  id
  /** @type {String} */
  name
  /** @type {Date} */
  creation_date

  /**
   * @param {Number} id
   * @returns {Promise<Module>}
   */
  static async getById (id) {
    const result = await PostgresStore.client.query({
      text: `SELECT * FROM ${Module.tableName} WHERE id=$1`,
      values: [id]
    })
    return result.rows[0]
  }

  /**
   * @param {import('./user.model')} user
   * @param {Number} moduleId
   * @param {String} right
   * @returns {Promise<Boolean>}
   */
  static async hasAccessRight (user, moduleId, right) {
    if (user.hasGlobalAccessRight(right)) return true
    const result = await PostgresStore.client.query({
      text: `
      SELECT 1 FROM ${Module.tableName} AS m
        LEFT JOIN ${ModuleUserRole.tableName} AS r ON m.id = r.module_id
        LEFT JOIN ${RoleAccessRight.tableName} AS ar ON r.role_id = ar.role_id
      WHERE m.id = $1
        AND r.user_id = $2
        AND ar.access_right = $3
      LIMIT 1`,
      values: [moduleId, user.id, right]
    })
    return !!result.rows.length
  }

  /**
   * @param {import('./user.model')} user
   * @returns {Promise<Module[]>}
   */
  static async getMyModules (user) {
    if (user.hasGlobalAccessRight(accessRights.module.view)) {
      const result = await PostgresStore.client.query(
        `SELECT id, name FROM ${Module.tableName}`
      )
      return result.rows
    }
    const result = await PostgresStore.client.query({
      text: `
      SELECT * FROM ${Module.tableName} AS m
        LEFT JOIN ${ModuleUserRole.tableName} AS r ON m.id=r.module_id
      WHERE r.user_id=$1`,
      values: [user.id]
    })
    return result.rows
  }

  /**
    * @param {Number} id
    * @param {String} name
    * @returns {Promise<Module>}
    */
  static async update (id, name) {
    const result = await PostgresStore.client.query({
      text: `UPDATE ${Module.tableName} SET name = $1
        WHERE id=$2 RETURNING *`,
      values: [name, id]
    })
    debug('result', result.rows[0])
    return result.rows[0]
  }

  /**
   * @param {Object.<('name'|'creation_date'), any>} params
   * @returns {Promise<Module>}
   */
  static async create (params) {
    if (Object.keys(params).length === 0) return null

    // filter out any non-alphanumeric parameter
    const fields = Object.keys(params)
      .filter(_ => _ !== 'id' && !_.match(/[^a-z_]/))

    const variables = fields.map((_, i) => `$${i + 1}`).join(', ')
    const values = fields.map(_ => params[_])
    const fieldNames = fields.join(',')

    const q = {
      text: `INSERT INTO ${Module.tableName} (${fieldNames}) VALUES (${variables})
        RETURNING *`,
      values
    }

    debug('q', q)

    const result = await PostgresStore.client.query(q)
    debug('result', result.rows[0])
    return result.rows[0]
  }

  static toSqlTable () {
    return `
    CREATE TABLE ${Module.tableName} (
      id SERIAL PRIMARY KEY,
      name TEXT,
      creation_date TIMESTAMPTZ NOT NULL
    )
    `
  }
}

/** @type {String} */
Module.tableName = 'module'

module.exports = Module

platform-role.model.js

/* eslint camelcase: 0 */
const PostgresStore = require('../utils/PostgresStore.js')
// const debug = require('debug')('hephaistos:platform-role.model.js')
const RoleAccessRight = require('./role-access-right.model.js')
const Role = require('./role.model.js')
const config = require('../server.config.js')

/**
 * Roles that apply to the whole platform
 * Mainly necessary to be able to manage modules and users
 */
class PlatformRole {
  /** @type {Number} */
  user_id
  /** @type {Number} */
  role_id

  /**
   * @param {Number} roleId
   */
  static async deleteAllForRole (roleId) {
    await PostgresStore.client.query({
      text: `DELETE FROM ${PlatformRole.tableName} WHERE role_id=$1`,
      values: [roleId]
    })
  }

  /**
   * @param {Number} userId
   */
  static async deleteAllForUser (userId) {
    await PostgresStore.client.query({
      text: `DELETE FROM ${PlatformRole.tableName} WHERE user_id=$1`,
      values: [userId]
    })
  }

  /**
   * @param {import('./user.model.js')} user
   * @return {Promise<{ id: Number, name: String }>}
   */
  static async getUserRole (user) {
    const result = await PostgresStore.client.query({
      text: `SELECT role.id as id, role.name as name FROM ${Role.tableName} as role
        LEFT JOIN ${PlatformRole.tableName} AS pr
          ON pr.role_id = role.id
        WHERE pr.user_id = $1
        LIMIT 1`,
      values: [user.id]
    })
    return result.rows[0]
  }

  /**
   * @param {import('./user.model')} user
   * @param {String} right
   * @returns {Promise<Boolean>}
   */
  static async hasAccessRight (user, right) {
    const result = await PostgresStore.client.query({
      text: `SELECT 1 FROM ${PlatformRole.tableName} AS r
        LEFT JOIN ${RoleAccessRight.tableName} AS ar ON r.role_id=ar.role_id
      WHERE r.user_id=$1
        AND ar.access_right=$2
      LIMIT 1`,
      values: [user.id, right]
    })
    return !!result.rows.length
  }

  /**
   * @param {Number} userId
   */
  static async remove (userId) {
    await PostgresStore.client.query({
      text: `DELETE FROM ${PlatformRole.tableName} 
        WHERE user_id=$1`,
      values: [userId]
    })
  }

  /**
   * @param {Number} userId
   * @param {Number} roleId
   */
  static async add (userId, roleId) {
    await PostgresStore.client.query({
      text: `INSERT INTO ${PlatformRole.tableName} 
        (user_id, role_id) VALUES ($1, $2)
        ON CONFLICT (user_id, role_id)
        DO UPDATE
         SET role_id = $2
      `,
      values: [userId, roleId]
    })
  }

  static toSqlTable () {
    const User = require('./user.model.js')
    return [`
      CREATE TABLE ${PlatformRole.tableName} (
        user_id INTEGER REFERENCES ${User.tableName}(id),
        role_id INTEGER REFERENCES ${Role.tableName}(id)
      )`,
      `ALTER TABLE ${PlatformRole.tableName} ADD UNIQUE(user_id, role_id)`
    ]
  }

  static async initScript () {
    const User = require('./user.model.js')
    const roles = await Role.getAll()
    const adminRole = roles.find(r => r.name === 'ADMIN')
    const user = await User.getByEmail(config.ADMIN_EMAIL, ['id'])
    await PlatformRole.add(user.id, adminRole.id)
  }
}

/** @type {String} */
PlatformRole.tableName = 'platform_role'

module.exports = PlatformRole

role-access-right.model.js

/* eslint camelcase: 0 */
const PostgresStore = require('../utils/PostgresStore.js')
// const debug = require('debug')('hephaistos:role-access-right.model.js')
const Role = require('./role.model.js')

class RoleAccessRight {
  /** @type {Number} */
  role_id
  /** @type {String} */
  access_right

  /**
   * @param {Number} roleId
   * @returns {Promise<RoleAccessRight[]>}
   */
  static async getByRoleId (roleId) {
    const result = await PostgresStore.client.query({
      text: `SELECT access_right FROM ${RoleAccessRight.tableName}
      WHERE role_id = $1`,
      values: [roleId]
    })
    return result.rows
  }

  /**
   * @param {Number} roleId
   */
  static async removeAllForRole (roleId) {
    return PostgresStore.client.query({
      text: `DELETE FROM ${RoleAccessRight.tableName} 
        WHERE role_id=$1`,
      values: [roleId]
    })
  }

  /**
   * @param {Number} roleId
   * @param {String} accessRight
   */
  static async remove (roleId, accessRight) {
    return PostgresStore.client.query({
      text: `DELETE FROM ${RoleAccessRight.tableName} 
        WHERE role_id=$1 AND access_right=$2`,
      values: [roleId, accessRight]
    })
  }

  /**
   * @param {Number} roleId
   * @param {String} accessRight
   */
  static async add (roleId, accessRight) {
    return PostgresStore.client.query({
      text: `INSERT INTO ${RoleAccessRight.tableName} 
        (role_id, access_right) VALUES ($1, $2)
        ON CONFLICT (role_id, access_right)
        DO NOTHING`,
      values: [roleId, accessRight]
    })
  }

  static toSqlTable () {
    return [`
      CREATE TABLE ${RoleAccessRight.tableName} (
        role_id INTEGER REFERENCES ${Role.tableName}(id),
        access_right VARCHAR(50)
      )`,
      `ALTER TABLE ${RoleAccessRight.tableName} ADD UNIQUE(role_id, access_right)`
    ]
  }
}

/** @type {String} */
RoleAccessRight.tableName = 'role_access_right'

module.exports = RoleAccessRight

role.model.js

/* eslint camelcase: 0 */
const PostgresStore = require('../utils/PostgresStore.js')
const debug = require('debug')('hephaistos:role.model.js')

class Role {
  /** @type {Number} */
  id
  /** @type {String} */
  name
  /** @type {Date} */
  creation_date
  /** @type {String[]} */
  access_rights

  /**
   * @returns {Promise<Role[]>}
   */
  static async getAllWithAccessRights () {
    const RoleAccessRight = require('./role-access-right.model.js')
    const { rows: roles } = await PostgresStore.client.query({
      text: `SELECT * FROM ${Role.tableName}`
    })
    const { rows: accessRights } = await PostgresStore.client.query({
      text: `SELECT * FROM ${RoleAccessRight.tableName}`
    })
    roles.forEach(r => {
      r.access_rights = accessRights.filter(ar => ar.role_id === r.id)
    })
    return roles
  }

  /**
   * @returns {Promise<Role[]>}
   */
  static async getAll () {
    const result = await PostgresStore.client.query({
      text: `SELECT * FROM ${Role.tableName} ORDER BY id`
    })
    return result.rows
  }

  /**
    * @param {Number} id
    * @param {String} name */
  static async update (id, name) {
    await PostgresStore.client.query({
      text: `UPDATE ${Role.tableName} SET name = $2 WHERE id=$1`,
      values: [id, name]
    })
  }

  /** @param {Object.<('name'|'creation_date'), any>} params */
  static async create (params) {
    if (Object.keys(params).length === 0) return null

    // filter out any non-alphanumeric parameter
    const fields = Object.keys(params)
      .filter(_ => _ !== 'id' && !_.match(/[^a-z_]/))

    const variables = fields.map((_, i) => `$${i + 1}`).join(', ')
    const values = fields.map(_ => params[_])
    const fieldNames = fields.join(',')

    const result = await PostgresStore.client.query({
      text: `INSERT INTO ${Role.tableName} (${fieldNames}) VALUES (${variables})
        RETURNING *`,
      values
    })
    debug('result', result.rows[0])
    return result.rows[0]
  }

  /**
   * @param {Number} id
   */
  static async delete (id) {
    const PlatformRole = require('./platform-role.model.js')
    const ModuleUserRole = require('./module-user-role.model.js')
    const RoleAccessRight = require('./role-access-right.model.js')

    await PlatformRole.deleteAllForRole(id)
    await ModuleUserRole.deleteAllForRole(id)
    await RoleAccessRight.removeAllForRole(id)

    await PostgresStore.client.query({
      text: `DELETE FROM ${Role.tableName} WHERE id=$1`,
      values: [id]
    })
  }

  static toSqlTable () {
    return `
    CREATE TABLE ${Role.tableName} (
      id SERIAL PRIMARY KEY,
      name TEXT,
      creation_date TIMESTAMP NOT NULL
    )`
  }

  static async initScript () {
    const RoleAccessRight = require('./role-access-right.model.js')
    const accessRight = require('./access-rights.definition.js')
    const {
      rows: [adminRole, teacherRole, studentRole]
    } = await PostgresStore.client.query({
      text: `INSERT INTO ${Role.tableName} (name, creation_date)
        VALUES ($2, $1), ($3, $1), ($4, $1)
      RETURNING *`,
      values: [new Date(), 'ADMIN', 'TEACHER', 'STUDENT']
    })

    const studentRights = [
      accessRight.exercise.do,
      accessRight.exercise.view,
      accessRight.session.view,
      accessRight.session.do,
      accessRight.module.view,
      accessRight.module.participate
    ]

    const teacherRights = [
      ...studentRights,
      accessRight.exercise_attempt.delete,
      accessRight.module.edit,
      accessRight.user.see_dashboard
    ]

    const adminRights = [
      ...teacherRights,
      accessRight.module.delete,
      accessRight.module.create,
      accessRight.module.edit_admin,
      accessRight.user.view,
      accessRight.user.manage,
      accessRight.role.manage,
      accessRight.role.add_to_user
    ]

    await PostgresStore.client.query({
      text: `INSERT INTO ${RoleAccessRight.tableName} (role_id, access_right)
        VALUES ${adminRights.map((_, i) => `($1, $${i + 2})`).join(', ')}
      RETURNING *`,
      values: [adminRole.id, ...adminRights]
    })

    await PostgresStore.client.query({
      text: `INSERT INTO ${RoleAccessRight.tableName} (role_id, access_right)
        VALUES ${teacherRights.map((_, i) => `($1, $${i + 2})`).join(', ')}
      RETURNING *`,
      values: [teacherRole.id, ...teacherRights]
    })

    await PostgresStore.client.query({
      text: `INSERT INTO ${RoleAccessRight.tableName} (role_id, access_right)
        VALUES ${studentRights.map((_, i) => `($1, $${i + 2})`).join(', ')}
      RETURNING *`,
      values: [studentRole.id, ...studentRights]
    })
  }
}

/** @type {String} */
Role.tableName = 'role'

module.exports = Role