cvmcosta / ltijs-sequelize Goto Github PK

In a production env, as per sequlize standards and other ORMS, we should not use .sync() as it can be unpredictable and destructive. We should use migrations instead.

Is there a way to start the serverless mode without triggering the db setup (.sync() + migrations) as well as the _databaseCleanup cronjobs? While this function is perfectly fine in a dedicated server, due to the nature of AWS lambdas the setup and the cleanup will be fired constantly everytime a lambda starts up bringing extra unnecessary load to the database (especially if multiple concurent lambdas fire at the same time)
My idea is to have a dedicated server that will handle some parts of the LTI protocol (including the setup and cleanup) and lambdas handling another.
Maybe a flag (or two) in the Database options that block the

   await sequelize.sync(); // Run migrations
    provDatabaseDebug('Performing migrations');
    const umzug = new Umzug({
      migrations: {
        glob: path.join(__dirname, 'migrations') + '/*.js'
      },
      context: sequelize.getQueryInterface(),
      storage: new SequelizeStorage({
        sequelize
      }),
      logger: console
    });
    await umzug.up();

await (0, _classPrivateFieldGet2.default)(this, _databaseCleanup).call(this);
    (0, _classPrivateFieldSet2.default)(this, _cronJob, cron.schedule('0 */1 * * *', async () => {
      await (0, _classPrivateFieldGet2.default)(this, _databaseCleanup).call(this);
    }));
    (0, _classPrivateFieldGet2.default)(this, _cronJob).start();

?

Hi @Cvmcosta, I was trying to update to the latest versions of ltijs (~5.7.3) and ltijs-sequelize (~2.4.1) and I ran into an issue. I am currently using postgres. Things seem to work fine if I have a database schema set up from running the previous version of these libraries:

2021-04-16T20:12:21.871Z provider:main Attempting to connect to database
2021-04-16T20:12:21.872Z provider:database Using Sequelize Database Plugin - Cvmcosta
2021-04-16T20:12:21.872Z provider:database Dialect: postgres
2021-04-16T20:12:22.194Z provider:database Performing migrations
{ event: 'migrating', name: '00_addAuthorizationServer.js' }
{
   event: 'migrated',
   name: '00_addAuthorizationServer.js',
   durationSeconds: 0.022
}

However, if I run the application on an empty postgres database, the migration errors out thusly:

2021-04-16T20:14:37.681Z provider:main Attempting to connect to database
2021-04-16T20:14:37.682Z provider:database Using Sequelize Database Plugin - Cvmcosta
2021-04-16T20:14:37.682Z provider:database Dialect: postgres
2021-04-16T20:14:38.316Z provider:database Performing migrations
{ event: 'migrating', name: '00_addAuthorizationServer.js' }
Error during deployment:  DatabaseError [SequelizeDatabaseError]: column "authorizationServer" of relation "platforms" already exists

(followed by more tracebacks, and the server and database connection shutting down)

Could you try to reproduce this error please? I could easily be missing some important step. If this is a real issue, this seems like it could be a significant blocker for new adoption and development work (where you may want to wipe out databases, or start from scratch often).

Thanks for the assistance!

In the architecture I'm working on, I'm segregating the LTIjs database into a separate Postgres Schema by providing options to the LTIjs-sequelize constructor:

const scopedLTIDB = new LTIDatabase(name, user, password, {
    host: host,
    dialect: 'postgresql',
    schema: configurations.lti.schema,
    logging: logging,
});

lti.setup(configurations.lti.secret,
    {
        plugin: scopedLTIDB,
    }, {...etc})

This causes the following error on a migration from v2.3.3 to ^2.4.0.

// Correct Schema prefix.
Executing (default): SELECT "name" FROM "LTI"."SequelizeMeta" AS "SequelizeMeta" ORDER BY "SequelizeMeta"."name" ASC;

{ event: 'migrating', name: '00_addAuthorizationServer.js' }

// Invalid Schema prefix.
Executing (default): ALTER TABLE "public"."platforms" ADD COLUMN "authorizationServer" VARCHAR(255) DEFAULT NULL;
Error during deployment:  DatabaseError [SequelizeDatabaseError]: relation "public.platforms" does not exist

This seems to be an open bug with Sequelize (11522, 11742).

I was able to find a workaround by altering the definition of the migration for my use case:

{
    tableName: 'platforms',
    schema: 'LTI',
  }

But this obviously isn't great, and I'm not sure if there's a way to pass the schema down from the constructor. I tried a more generic approach by adding a schema option to Umzug's SequelizeStorage object, but that didn't work, (it probably only applies to the SequelizeMeta table, and uses the getQueryInterface for the migration, which would have the same problem as a raw migration).

    const umzug = new Umzug({
      migrations: {
        glob: path.join(__dirname, 'migrations') + '/*.js'
      },
      context: sequelize.getQueryInterface(),
      storage: new SequelizeStorage({
        sequelize,
        schema: sequelize.options.schema
      }),
      logger: console
    });

Ultimately, not sure if anything else can be done since this is a Postgres-specific feature, but I'm documenting it to save anyone else a trip down the rabbit hole.

Unrelated:

Also, this probably isn't an issue since this migration only has one modification, but we may want to wrap it for consistency with future migrations in a Transaction so that failures can be rolled back together, ie:

await queryInterface.sequelize.transaction(async () => {
  await queryInterface.addColumn(

ltijs-sequelize does not take into account authorizationServer property on create/update operations due to missing definition in https://github.com/Cvmcosta/ltijs-sequelize/blob/master/src/DB.js#L171

Hi There,

I am trying to use Ltijs sample server app using Ltijs-sequelize plugin with MySql Database.

But when I start yarn start I get below error:
DatabaseError [SequelizeDatabaseError]: BLOB/TEXT column 'iss' used in key specification without a key length

I think it caused by trying to index iss which is a TEXT field.
iss: {
type: Sequelize.TEXT
},
......
indexes: [{
fields: ['iss', 'clientId', 'deploymentId', 'user']
},

Have any one come across this issue and have any fix available?

Detail logs are below.

yarn start

yarn run v1.22.10
$ env DEBUG=provider:* node index.js
provider:main Attempting to connect to database +0ms
provider:database Using Sequelize Database Plugin - Cvmcosta +0ms
provider:database Dialect: mysql +0ms
(node:11251) Warning: Accessing non-existent property 'MongoError' of module exports inside circular dependency
(Use node --trace-warnings ... to show where the warning was created)
Error during deployment: DatabaseError [SequelizeDatabaseError]: BLOB/TEXT column 'iss' used in key specification without a key length
at Query.formatError (/Users/jadavbheda/CD/Projects/NewCanney/ncisd-lti/server/node_modules/sequelize/lib/dialects/mysql/query.js:265:16)
at Query.run (/Users/jadavbheda/CD/Projects/NewCanney/ncisd-lti/server/node_modules/sequelize/lib/dialects/mysql/query.js:77:18)
at processTicksAndRejections (internal/process/task_queues.js:93:5)
at async /Users/jadavbheda/CD/Projects/NewCanney/ncisd-lti/server/node_modules/sequelize/lib/sequelize.js:619:16
at async MySQLQueryInterface.addIndex (/Users/jadavbheda/CD/Projects/NewCanney/ncisd-lti/server/node_modules/sequelize/lib/dialects/abstract/query-interface.js:541:12)
at async Function.sync (/Users/jadavbheda/CD/Projects/NewCanney/ncisd-lti/server/node_modules/sequelize/lib/model.js:1367:7)
at async Sequelize.sync (/Users/jadavbheda/CD/Projects/NewCanney/ncisd-lti/server/node_modules/sequelize/lib/sequelize.js:793:35)
at async Database.setup (/Users/jadavbheda/CD/Projects/NewCanney/ncisd-lti/server/node_modules/ltijs-sequelize/dist/DB.js:394:5)
at async Provider.deploy (/Users/jadavbheda/CD/Projects/NewCanney/ncisd-lti/server/node_modules/ltijs/dist/Provider/Provider.js:769:7)
at async setup (/Users/jadavbheda/CD/Projects/NewCanney/ncisd-lti/server/index.js:67:3) {
parent: Error: BLOB/TEXT column 'iss' used in key specification without a key length
at Packet.asError (/Users/jadavbheda/CD/Projects/NewCanney/ncisd-lti/server/node_modules/mysql2/lib/packets/packet.js:712:17)
at Query.execute (/Users/jadavbheda/CD/Projects/NewCanney/ncisd-lti/server/node_modules/mysql2/lib/commands/command.js:28:26)
at Connection.handlePacket (/Users/jadavbheda/CD/Projects/NewCanney/ncisd-lti/server/node_modules/mysql2/lib/connection.js:425:32)
at PacketParser.onPacket (/Users/jadavbheda/CD/Projects/NewCanney/ncisd-lti/server/node_modules/mysql2/lib/connection.js:75:12)
at PacketParser.executeStart (/Users/jadavbheda/CD/Projects/NewCanney/ncisd-lti/server/node_modules/mysql2/lib/packet_parser.js:75:16)
at Socket. (/Users/jadavbheda/CD/Projects/NewCanney/ncisd-lti/server/node_modules/mysql2/lib/connection.js:82:25)
at Socket.emit (events.js:315:20)
at addChunk (internal/streams/readable.js:309:12)
at readableAddChunk (internal/streams/readable.js:284:9)
at Socket.Readable.push (internal/streams/readable.js:223:10)
at TCP.onStreamRead (internal/stream_base_commons.js:188:23) {
code: 'ER_BLOB_KEY_WITHOUT_LENGTH',
errno: 1170,
sqlState: '42000',
sqlMessage: "BLOB/TEXT column 'iss' used in key specification without a key length",
sql: 'ALTER TABLE idtokens ADD INDEX idtokens_iss_client_id_deployment_id_user (iss, clientId, deploymentId, user)',
parameters: undefined
},
original: Error: BLOB/TEXT column 'iss' used in key specification without a key length
at Packet.asError (/Users/jadavbheda/CD/Projects/NewCanney/ncisd-lti/server/node_modules/mysql2/lib/packets/packet.js:712:17)
at Query.execute (/Users/jadavbheda/CD/Projects/NewCanney/ncisd-lti/server/node_modules/mysql2/lib/commands/command.js:28:26)
at Connection.handlePacket (/Users/jadavbheda/CD/Projects/NewCanney/ncisd-lti/server/node_modules/mysql2/lib/connection.js:425:32)
at PacketParser.onPacket (/Users/jadavbheda/CD/Projects/NewCanney/ncisd-lti/server/node_modules/mysql2/lib/connection.js:75:12)
at PacketParser.executeStart (/Users/jadavbheda/CD/Projects/NewCanney/ncisd-lti/server/node_modules/mysql2/lib/packet_parser.js:75:16)
at Socket. (/Users/jadavbheda/CD/Projects/NewCanney/ncisd-lti/server/node_modules/mysql2/lib/connection.js:82:25)
at Socket.emit (events.js:315:20)
at addChunk (internal/streams/readable.js:309:12)
at readableAddChunk (internal/streams/readable.js:284:9)
at Socket.Readable.push (internal/streams/readable.js:223:10)
at TCP.onStreamRead (internal/stream_base_commons.js:188:23) {
code: 'ER_BLOB_KEY_WITHOUT_LENGTH',
errno: 1170,
sqlState: '42000',
sqlMessage: "BLOB/TEXT column 'iss' used in key specification without a key length",
sql: 'ALTER TABLE idtokens ADD INDEX idtokens_iss_client_id_deployment_id_user (iss, clientId, deploymentId, user)',
parameters: undefined
},
sql: 'ALTER TABLE idtokens ADD INDEX idtokens_iss_client_id_deployment_id_user (iss, clientId, deploymentId, user)',
parameters: undefined
}

Closing server...
Closing connection to the database...
provider:database Closing connection to database +107ms
provider:database Closed database connection and removed cronjob +1ms
Shutdown complete.
✨ Done in 0.81s.

Due to this code snippet:

https://github.com/Cvmcosta/ltijs-sequelize/blob/master/src/DB.js#L298-L313

We are getting "Duplicate Entry" errors.
When a call to getLineitems is called, it gets an access token with the scope https://purl.imsglobal.org/spec/lti-ags/scope/lineitem.raedyOnly . Trucating this at 50 chars is ``.
Next, if you try and send a score, the following logic/issue happens:

1. It needs a scope of https://purl.imsglobal.org/spec/lti-ags/scope/lineitem https://purl.imsglobal.org/spec/lti-ags/scope/score. It so it fetches a new access token.
2. This token has a scope field of https://purl.imsglobal.org/spec/lti-ags/scope/lineitem https://purl.imsglobal.org/spec/lti-ags/scope/score.
3. It tries to insert this into the DB
4. The DB has a UNIQUE on platformUrl, clientId, scopes. But because the index truncates the cope to 50, these 2 different scopes are seen as the same:

Scope 1:
https://purl.imsglobal.org/spec/lti-ags/scope/lineitem https://purl.imsglobal.org/spec/lti-ags/scope/score

Scope 2:
https://purl.imsglobal.org/spec/lti-ags/scope/lineitem.readyOnly 

## ^^ Both truncate to:

https://purl.imsglobal.org/spec/lti-ags/scope/line

Suggested Fix

A new column named token_hash or access_token_hash that is an md5 of the 3 fields (platformUrl, clientId, scopes) and that is used for the unique check.

Steps to reproduce

1. Setup LTIJS
2. Create a route like /send-grade
3. Use the following snippet. This basically gets line items and then pushes a score.

router.post('/send-grade', async(req, res) => {
    try {
        const idtoken = res.locals.token // IdToken
        const score = req.body.grade // User numeric score sent in the body
        // Creating Grade object
        const gradeObj = {
          userId: idtoken.user,
          scoreGiven: score,
          scoreMaximum: 100,
          activityProgress: 'Completed',
          gradingProgress: 'FullyGraded'
        }
    
        // Selecting linetItem ID
        let lineItemId = idtoken.platformContext.endpoint.lineitem // Attempting to retrieve it from idtoken
        console.log('Line Item ID: ', lineItemId);
        // This call makes an access token with a scope: https://purl.imsglobal.org/spec/lti-ags/scope/lineitem.readOnly
        let response = await lti.Grade.getLineItems(idtoken, { resourceLinkId: true });
        const lineItems = response.lineItems;
        console.log('Line items:', response.lineItems);
        // Get the SAMPLE lineitem.
        let lineItemInfo = lineItems.find(item => item.resourceid === 'SAMPLE');
        if (!lineItemInfo) {
            res.send('Unable to find the SAMPLE lineItem resourceid');
        }
        console.log('Submitting a score:');
        // Demo lineItemID url. You will need to locate yours.
        const lineItemID = lineItemInfo.id;
        // This wants an access token with the scope https://purl.imsglobal.org/spec/lti-ags/scope/lineitem https://purl.imsglobal.org/spec/lti-ags/scope/score 
        // But when this is inserted, the index truncates at 50. This is the error line.
        const responseGrade = await lti.Grade.submitScore(idtoken, lineItemID, gradeObj);
        return res.send(responseGrade);
    } catch(e) {
        console.log(e);
        return res.send('Error');
    }
});

Any way you could patch this critical vulnerability?
No fix available
node_modules/mysql2
ltijs-sequelize *
Depends on vulnerable versions of mysql2
node_modules/ltijs-sequelize

Hi,
I have been using ltijs-sequelize with my postgres database on the blackboard platform. When I try to launch, I get PROVIDER_NOT_DEPLOYED.

Can you let me know if I am missing out on anything?