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.