sql & sequelize support

[musicformellons]

I am a bit puzzled by the following:

1. As my app has returning users with account etc I suppose I do need a database for authorization rules etc.
2. I really would prefer to use postgres (with sequelize) instead of mongodb. I saw some code examples in the docs for postgres & sequelize but it feels a bit like this is not really supported (yet) and I find it hard to judge whether I could get it up and running with these pointers. I guess my question boils down to: are these given examples enough to be up and running with postgres and sequelize, or are they just boilerplate examples on which more work is needed before you can use it?

Maybe you could also explain which features then would still be missing compared to the mongodb & mongoose solution.

[stalniy]

Integration with database should be quite easy because the only thing you need to do is to convert CASL rules into database query. sequelize uses Mongo-like syntax to construct SQL query. So, in terms of fetching all records according to ACL rules from the single table shouldn't be a big deal and examples from documentation should cover majority of such cases.

So, when you define permissions for an entity based on its own fields, all should be good. But CASL doesn't provide a solution if you need to define permissions for one entity based on fields of another entity and it doesn't matter which database you use. So, for such cases you will need to do some manual stuff

Lets consider an example: What if we want to allow all users who has admin word in email to update posts. Then I'd define rules as this:

can('update', 'Post', { 'author.email': { $regex: "admin" } })

In order to retrieve all posts which such user can update we need to create query like this:

SELECT *
FROM posts as p
  INNER JOIN users as u ON u.id = p.author_id
WHERE u.email LIKE "%admin%"

And now your rulesToQuery function should be able to detect that author.email is a field of another entity and you need to do inner join

[rconstantine]

I'm having difficulty understanding this. My scenario: VueJS app with Express backend. For now, I'm looking to use CASL on the frontend at least. I'm using sequelize to connect to MSSQL. I'd like to store rules to the database, and possibly build them in an admin UI.

I think that I should be able to manually build JSON for each rule somehow and store it, then retrieve it and shove it into abilities (multiple abilities can be one each for roles, right?). Is this assumption correct, to start with?

Looking at issue #44, can I just build rules like this:

[
  ["read", "Post"],
  ["read", "Book", { "id": 1 }],
  ...
]

and store them that way?

Or should I use the other way shown:

[
  { "actions": "read", "subject": "Post" },
  { "actions": "read", "subject": "Book", "conditions": { id: 1 } },
  ...
]

Or is this second way not supported anymore? I think it's more readable. What functions do I use to handle these? The packRules/unpackRules shown in issue #44? Or json.stringify/parse? Or toJSON()? Is there a counterpart for toJSON()? If I'm manually making the JSON, I guess I just need the opposite of toJSON?

Is this how I'd extend things?:

[
  ["read,edit", "Post"],
  ["read", "Book,Article", { "id": 1 }],
  ...
]

Or would it be better to extend in this way instead?

[
  ["read", "Post"],
  ["edit", "Post"],
  ["read", "Book", { "id": 1 }],
  ["read", "Article", { "id": 1 }],
  ...
]

And I assume I can extend it for the rest of these: [ actions, // string separated by comma subjects, // string separated by comma, inverted, // true or undefined conditions, // object fields, // string separated by comma reason // string ]

Why is the 'inverted' item skipped in the above examples?

Lastly, I don't know that I would need ruleToQuery in this case, or would I?

[stalniy]

@rconstantine #44 was about creating a possibility to minify CASL rules for transporting them through JWT token. Currently there are 2 functions packRules/unpackRules which you can use to achieve that but they can't help you with sequelize integration.

You can read about suggested ways to store rules here.

There are several ways how to organize rules in database, so you need to decide this by yourself. The simplest usecase is:

1 Role has multiple abilities (one to many relationship). So, you have 2 tables: roles and abilities. abilities table should have foreign key to roles table. Then user can be associated with a role.

Then having a role you can retrieve its rules.

Alternatively you can use JSON data type for your column in roles table and put inside the whole JSON. It should have format like this:

[
  { "actions": "read", "subject": "Post" },
  { "actions": "read", "subject": "Book", "conditions": { id: 1 } },
  ...
]

[rconstantine]

@stalniy Thanks for your last answer. That helped me create the database tables I needed and the administrative screens for those tables. I'm now finally circling back and trying to finish this off. I had to go and do more visible items in my application so management could 'see' progress. Working on hidden stuff like user authorization makes them think you haven't done any work!

In Vue, when I call this.$ability.update(myStuff) where myStuff looks something like:

[
  { "actions": "read", "subject": "Post" },
  { "actions": "read", "subject": "Book", "conditions": { id: 1 } },
  ...
]

I get an error stating update is not a function. I'm running "@casl/ability": "^2.5.0", "@casl/vue": "^0.4.3".

I want the above JSON to replace any current rules. Indeed, I also want to be able to empty the rules out using this.$ability.update([]), but that gives the same error. What could I be doing wrong?

[rconstantine]

@stalniy, never mind. I figured it out. I had added a hard-coded definition of rules in a js file and initialized the ability object with it. It was those rules I was trying to override. Instead, I just removed them so that the $ability object would begin empty and now the update() function is working without error.

To be clear, I had this in one file:

import { abilitiesPlugin } from '@casl/vue'
import { RoleAbilities } from './casl/roleAbilities'

export default ({ Vue }) => {
  Vue.use(abilitiesPlugin, RoleAbilities)
}

And this in another:

import { AbilityBuilder } from '@casl/ability'

const Administrator = AbilityBuilder.define(can => {
  can('manage', 'all')
})

const Executive = AbilityBuilder.define(can => {
  can('read', 'all')
  can('update', 'Post', { state: 'waitForModeration' })
})

const defaults = AbilityBuilder.define(can => {
  can('read', 'all')
})

export const RoleAbilities = { Administrator, Executive, defaults }

I don't know what happened behind the scenes to prevent the update function from working, but changing the first file to no longer use the second seems to have worked. Now I just have to employ the rules.

Thanks again for this module and for the Vue integration.

[stalniy]

I don’t plan to create a core package for sequelize as it looks like that project is dying slowly. Instead I plan to add support for objection.js or/and mikroORM

[musicformellons]

Mmmh, what makes you think sequelize is dying?

[stalniy]

Probably I used to loud word “dying”). This is just my opinion after working with sequelize for half a year

this is the list of what I don’t like in sequelize:

• no query builder
• raw option just passes generated query to driver, and returns result without proper grouping
• I had a lot of discussion in my team about using classes for models or not (because documentation didn’t have info about classes but now it has)
• sequelize try to query database with inefficient generated query (e.g., when you have join, limit and offset, it produces query which doesn’t use indexes without any warning or using a better strategy)
• it’s hard to extend sequelize. There is no something like plugins in mongoose, only hooks.
• in order to insert a graph of objects into db, you need to pass a graph of includes

So, for standard use cases it works good, as well as mentioned libraries. But on the other hand objection and mikro-orm allows to work with non standard use cases easier (because both have query builder and more solid codebase)

[stalniy]

Some time ago I commented in sequelize repo on replacement of string based operators with symbol based

It’s ridiculous, I said that Symbols don't fix the problem, they agreed and closed the issue.

[ujwal-setlur]

@stalniy any status on mikroOrm support? I am using mikro-orm and would love this support. Happy to help or test!

[stalniy]

No progress for now. But have some thoughts:

1. The best way to start is to integrate with knex
2. MikroORM supports knex, so it should work with knex integration out of the box (or with minor configuration)
3. In knex, we can’t check whether a field is a json field or a foreign key ref (usually we can do this only on ORM level), that’s why we need to introduce extra syntax in conditions that emphasize field as a foreign field ref (to support joins).
4. To fulfill 3, I’ll need to send a PR in sift. So, this extra syntax in 3 can be processed on casl side (by passing getter option, for example)

[stalniy]

So, to start we need a function rulesToKnexQuery(ability, action, subject, options). options object should contain a property isRelation: (prop, subjectType, subject) => boolean. This function
returns true or false if prop is a relation to another entity. prop can be a string (e.g., name) or string with dot notation (e.g., author.email). If function return true we need to innerJoin relation and add conditions to where clause, if false then this is a local field and just add condition to where clause.

The result of this rulesToKnexQuery is a knex QueryBuilder that fetch records in a single request. If we do function like rulesToKnexQuery than it will be very easy to add integration for objection.js and mikroORM.js and any knex based ORM.

Afterwards we can even implement something similar for sequelize but in this case the result of the function will be an object, which we can pass to findAll because sequelize doesn't have query builder

@ujwal-setlur if you want to try to do this, your help is very welcome!

[ujwal-setlur]

@stalniy I will try to get to this in the next week or two!

[otaviosoares]

I want to help too. I intend to use it with objection.js. @stalniy do you plan to have rulesToKnexQuery at @casl/ability/extra as well?

[stalniy]

As a separate package. @casl/knex

[stalniy]

I’ll create a folder for it and prepare the infrastructure code

update: it would be awesome if We could create this together :)

[otaviosoares]

Sure, let me know when you want to chat.

[stalniy]

Sure, let me know when you want to chat.

@otaviosoares sent you an email with instructions and created infra code for casl-knex in feat/knex branch

[stalniy]

Waiting for the feedback from @crcn in https://github.com/crcn/sift.js/issues/202

[stalniy]

Guys, is here anybody want to help to integrate casl with sequelize?

P.S.:

I created a new library called ucast which can help to do this without efforts.

This library will eventually replace sift.js and will allow to integrate casl with any database system, including ElasticSearch, Casandra and other NoSQL dbs.

[stalniy]

Hi @ujwal-setlur ,

You said that can help with mikro-orm integration. Do you still have desire to help? If so, please check the issue - https://github.com/stalniy/ucast/issues/5

Update: for those who are afraid but want to help, I created a small gist with basic implementation for sequelize. As you can see it's really not hard :) the only thing which is left is to finish implementation for other operators, add check for foreign properties and write tests

[ujwal-setlur]

@stalniy, yes, I can take a stab at it. Thank you for the sequelize gist. I am sure it will be useful. I will take a stab at it this weekend

[stalniy]

Hi everybody,

Just some awesome updates regarding work towards SQL support in CASL:

Thanks to outstanding contribution of @otaviosoares, I've published the first version of @ucast/objection library that helps to translate mongo query into SQL! So, as soon as I replace siftjs with ucast, all you will be able to use objection.js ORM and CASL to query accessible records.

It supports auto-joins by the way, so the case described by me in the comment above is now possible!

Thank you very much @otaviosoares, I really appreciate your help!

[ShallmentMo]

@stalniy as soon as I replace siftjs with ucast any progress on this?

[stalniy]

I'm working on v5 in next branch, and plan to publish prerelease version today or tomorrow. So, all feedback is very welcome

[ShallmentMo]

looking forward for it 👍

[stalniy]

@ShallmentMo release 5.0.0 in next channel. It uses @ucast/mongo2js library instead of siftjs.

The gist how to use objection with CASL right now can be found here. It's raw however. Now, I need to change rulesToQuery (or create rulesToAST) function that will produce a final AST which then can be interpreted by @ucast/objection.

But fill free to play with what we have right now. Looking forward to the feedback

[ShallmentMo]

@stalniy thanks for your work. But now we are using Sequelize. The gist you wrote might not fit. So I just write something like this:

function abilityQuery(action, object) {
  const { Op } = Sequelize
  const { $and = [], $or = [] } = rulesToQuery(ability, action, object, (rule) => {
    return rule.conditions || {}
  })
  return {
    [Op.and]: $and,
    [Op.or]: $or
  }
}

[stalniy]

Guys, we are now very close to have support for SQL! I've just published alpha version of @ucast/sql that unifies integration of ucast with 4 major JavaScript ORMS (sequelize, objection, typeorm, mikro-orm).

Now, ucast conditions can be easily converted to query builder of any of the listed ORMs. That means that casl v5 will support SQL databases!

update: with auto-join option

[GuskiS]

Great news. But what about knex?

[stalniy]

@GuskiS, in knex we can’t do auto join. So, you can just use @ucast/sql +casl + knex.raw (or knex().whereRaw

[vemundeldegard]

@stalniy Can't wait to use this. You said TypeORM is supported, but I can't see it in docs?

[stalniy]

@stalniy Can't wait to use this. You said TypeORM is supported, but I can't see it in docs?

Supported. No worries :) I’ll update readme later. I’m finishing other stuff for v5, you can check the milestone https://github.com/stalniy/casl/milestone/5

casl/sql will be a small wrapper around casl/ability/extra and ucast/sql

So, I think it will take up to month to finish everything and test. I’ll publish prereleases earlier so you will be able to test and provide feedback

[stalniy]

Any suggestions regarding API for integration with sequelize and typeorm is very welcome. What I have so far is:

What I plan to implement

• @casl/sql/objection exports:

  • AccessibleRecordsQueryBuilder that has accessibleBy(ability, action) method.
    Integration of QueryBuilder can be done by specifying static QueryBuilder prop on Model (as described here).

    // BaseModel.js
    const { AccessibleRecordsQueryBuilder } = require('@casl/sql/objection');
    
    class BaseModel extends Model {
      static QueryBuilder = AccessibleRecordsQueryBuilder;
    }
    
    // Person.js
    const { BaseModel } = require('./BaseModel');
    
    // Person now uses AccessibleRecordsQueryBuilder
    class Person extends BaseModel {}
    
    Person.query().accessibleBy(ability).where(...)
    

  • toObjectionQuery(ability, action, objectionQuery) which adds conditions to specified objectionQuery (this function is called by accessibleBy internally). This is in case you don't want/can't use custom AccessibleRecordsQueryBuilder:

    const { toObjectionQuery } = require('@casl/sql/objection');
    
    class Person extends Model {}
    
    const query = toObjectionQuery(abilitly, 'read', Person.query());
    

• @casl/sql/mikro-orm exports:

  • AccessibleEntityRepository which can be set as default entity repository for all entities through MikroORM.init

    const { MikroORM } = require('mikro-orm')
    const { AccessibleEntityRepository } = require('@casl/sql/mikro-orm')
    
    const orm = await MikroORM.init({
      entities: [UserSchema, ProjectSchema],
      dbName: ':memory:',
      type: 'sqlite',
      entityRepository: AccessibleEntityRepository,
    });
    
    const query = orm.em.getRepository(User)
      .accessibleBy(ability, action)
      .where(...);
    

  • toMikroORMQuery(ability, action, query), similar to objection.js, this function can be used in case you can't/don't want use custom EntityRepository

sequelize

sequelize has scopes but they look really ugly when you need to pass extra parameters in a scope (Project.scope('random', { method: ['accessibleBy', ability] })). So, probably here the best way is just to export toSequelizeQuery function and use in Model's static methods when needed:

const { Model } = require('sequelize')
const { toSequelizeQuery } = require('@casl/sql/sequelize')

class Project extends Model {
  static accessibleBy(ability) {
    return this.findAll(toSequelizeQuery(ability, 'read'))
  }
}

Any thoughts or feedback is very welcome

TypeORM

Don't have much knowledge about TypeORM. So, the best I can do is to provide a function toTypeORMQuery(ability, action, query).

const query = toTypeORMQuery(ability, 'read', conn.createQueryBuilder(Person, 'p'))

Any thoughts or feedback is very welcome

[burgalon]

Are there any commits/starting PR re Sequelize or Sequelize with scopes from your last comment?

[stalniy]

not yet. There are several pre-releases for casl v5. It's finished but I plan to create @casl/sql based on @casl/ability v5 prerelease and @ucast/sql prerelease to ensure that they work nice together.

Also it'd be good to get a feedback about about this comment, just to reduce likelihood of introducing breaking changes on the early stages. I don't use all that ORMs in production, so you guys the best advisors I have currently :)

P.S.: the work is also not moving forward because I'm currently looking for a new job and doing test tasks ;) But I think that @casl/sql will be available in the beginning of October or earlier.

[veryeasily]

I have a few questions regarding the objection.js plugin. The first may be a little bike-shed-y, but would it be possible to provide a class mixin like:

// BaseModel.js
const { Accessible } = require('@casl/sql/objection')
const { Model } = require("objection")

class BaseModel extends Accessible(Model) {}

// Person.js
const { BaseModel } = require('./BaseModel')

class Person extends BaseModel {}

Person.query().accessibleBy(ability).where(...)

That seems more like it would match up with their plugin development best-practices.

The second question has to do with related queries. My apologies if this one is obvious, but here goes. If I have something like

// base_model.js
const { Model } = require("objection");
const { AccessibleRecordsQueryBuilder } = require("@casl/sql/objection");

class BaseModel extends Model {
  static get QueryBuilder() {
    return AccessibleRecordsQueryBuilder;
  }
}

// comment.js
class Comment extends BaseModel {}

// post.js
class Post extends BaseModel {
  static get relationMappings() {
    return {
      comments: {
        relation: Model.HasManyRelation,
        modelClass: Comment,
        from: "Posts.id",
        to: "Comments.postId",
      },
    };
  }
}

// ability.js
const { Ability, AbilityBuilder } = require("@casl/ability");

function defineAbilitiesFor(user) {
  const { can, rules } = new AbilityBuilder();

  can("read", "Post");
  can("read", "Comment", { published: true });

  return new Ability(rules);
}

What is the correct way for me to execute something like Post.query().withGraphFetched("comments") in such a way where the comments are required to have a true published field? Is the idea that Post.query().accessibleBy(ability).withGraphFetched("comments") will work out of the box there?

Thanks again for building this! I am super super looking forward to it

[stalniy]

Thank you @veryeasily for the thoughtful comment!

1. Mixin - ok
2. withGraphFetched won’t work out of the box. Because the root entity is Post and accessibleBy gets rules for the post entity. To support withGraphFetched, I see 2 options:
   • using separate method withAccessibleGraphFetched(ability, 'comments')
   • using where conditions (not sure about query builder methods): qb.where(() => Comment.accessibleBy(ability))

[veryeasily]

Thanks for the reply -- FWIW, off the cuff, that 2nd approach with the where conditions looks good to me. It looks hopefully easier to maintain from casl's perspective and also easier for me to reason about from a library user perspective.

[stalniy]

casl@5 has been just released. All packages and docs are updated. SQL support is coming...

[lambrohan]

@stalniy Can I use v5 with postgres and typeorm?

[stalniy]

Yes, it’s possible. Also you can manually plug @ucast/sql to get some semi-basic functionality to transform casl rules to sql

[lambrohan]

@stalniy Do you have any examples on how can it be done properly?

[stalniy]

It’s not battle tested yet. Don’t have enough time to do this. That’s why there is no docs

[JaneJeon]

For those of us using Objection.js, please see https://github.com/janeJeon/objection-authorize, I’ve been using and building it for the past few years, and it’s battle tested, and it supports full isomorphism!

[stalniy]

Guys, I proud to say CASL is now officially supports Prisma. So, anyone who uses prisma as ORM can try to use @casl/prisma and provide feedback

[natepelzel]

Hey, thanks for all the hard work on this project. I am very interested in the MicroORM integration, and think your idea to handle it via a custom repository makes a lot of sense.

Have you began development on it yet? Is there anything I can do to assist?

[stalniy]

Hi @natepelzel

Use @ucast/sql@alpha version to test integration with mikro-orm. there is a subpackage @ucast/sql/mikro-orm which exposes this functionality.

There are some edge cases in objection.js integration which might be the same for mikro-orm, check this issues:

• https://github.com/stalniy/ucast/issues/17
• https://github.com/stalniy/ucast/issues/18

[jassdv]

is there any update regarding Objection support? @casl/sql/objection ? would that be available any time soon? or something I can start with ?

[JaneJeon]

@jassdv unfortunately, the author doesn't seem to see Objection as the right platform to target. In the meantime, however, there is https://github.com/JaneJeon/objection-authorize which has been used for years and provides CASL integration

[stalniy]

@JaneJeon this is not true. I’ve never said that. Despite the fact there is no update on the work it doesn’t mean that you can use this thread to promote your library.

Please, let’s keep this issue focused on the actual title.

update: one more comment like that and you will be blocked

[raymondsze]

Its good to see if there is official library support typeorm as typeorm itself and nestjs are getting quite popular.

[kasvith]

Is there any docs about objection integration?