Skip to content

Instantly share code, notes, and snippets.

@Musinux

Musinux/access-rights.definition.js

Last active March 25, 2020 10:12
Show Gist options
  • Save Musinux/09228307d34e3b63c0c32fa663a1eaba to your computer and use it in GitHub Desktop.
Save Musinux/09228307d34e3b63c0c32fa663a1eaba to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. Musinux revised this gist Mar 25, 2020. 1 changed file with 64 additions and 0 deletions.
    64 changes: 64 additions & 0 deletions module-user-role.model.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,64 @@
    /* 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
  2. Musinux revised this gist Mar 25, 2020. 1 changed file with 128 additions and 0 deletions.
    128 changes: 128 additions & 0 deletions module.model.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,128 @@
    /* 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
  3. Musinux created this gist Mar 19, 2020.
    33 changes: 33 additions & 0 deletions access-rights.definition.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,33 @@
    /** 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
    121 changes: 121 additions & 0 deletions platform-role.model.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,121 @@
    /* 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
    76 changes: 76 additions & 0 deletions role-access-right.model.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,76 @@
    /* 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
    165 changes: 165 additions & 0 deletions role.model.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,165 @@
    /* 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