Hi,
I run into a problem while trying to handle the following situation:

I want that all users can read their things:

can('read', 'Thing', { "owners.user_id": user.id })

But i want also to add a specific rule to permit to alice to read a Thing that she doesn't own (the thing with id 3), so I have an additional rule for alice that says:

{
  action: 'read',
  subject: 'Thing',
  conditions: { id: 3 }
}

This situation results in a SQL statement similar to:

SELECT ... FROM Thing INNER JOIN ThingsOwners ON Thing.id = ThingsOwners.thing_id WHERE ( Thing.id = 3 or ThingsOwners.user_id = ALICE_ID)

That works as expected until the thing 3 has at least an owner, otherwise the thing 3 is filtered out by the INNER JOIN.

Using LEFT JOIN should fix this issue and afaik it wouldn't introduce any other problem.

What do you think about it? If you are ok with it I can work on a PR to replace INNER JOIN with LEFT JOIN. Thanks!

I'm using you gist code to check casl rights directly at the database. But when i do a eager or withGraphJoined the interpret function will add a second join for the relation, leading to the error table name "xxx" specified more than once - objection. My ability checks if the user id is either TechnicalContact or AccountOwner.

I see there is a sample which seems to prevent adding a new relation join, but this is not available with objection.

My code:

import {interpret} from '@ucast/sql/objection';

toObjectionQuery<M extends Model>(
  ability: AnyAbility,
  action: string,
  query: QueryBuilder<M, M[]>
): QueryBuilder<M, M[]> {
  const q =
    rulesToQuery(ability, action, query.modelClass(), rule => {
      if (!rule.ast) {
        throw new Error('Unable to create Objection.Query without AST');
      }
      return rule.ast;
    }) || {};

  if (q) {
    const {$and = [], $or = []} = q;
    const condition = new CompoundCondition('and', [
      ...$and,
      new CompoundCondition('or', [...$or] as CompoundCondition[]),
    ] as CompoundCondition[]);

    return interpret(
      condition,
      query as QueryBuilder<Model, Model[]>
    ) as QueryBuilder<M, M[]>;
  }
  return query;
}

const ability = fastify.userAbilities;

// Create query that takes abilities into account
const accountQuery = this.toObjectionQuery<AwsAccountModel>(
  ability,
  'read',
  AwsAccountModel.query().withGraphJoined(
    '[technicalContact,accountOwner,costcenterData]'
  )
);

Error:
Results in an sql query like this: table name "technical_contact" specified more than once

and the sql looks somewhat like this:

...
INNER JOIN "users" AS "technical_contact"
ON "technical_contact"."id" = CAST("acc"."data"#>>'{TechnicalContact}' AS text)
LEFT JOIN "users" AS "technical_contact"
ON "technical_contact"."id" = CAST("acc"."data"#>>'{TechnicalContact}' AS text)
...
WHERE (("technical_contact"."id" = ? or "account_owner"."id" = ?))

The latest Node allows to specify dual loading via package.json exports field, need to utilize it in order to close stalniy/casl#331

• this interpreter should be part of a new package @ucast/sequelize (put it in packages/sequelize)
• it should support the same operators as js interpreter from @ucast/js (the most complex is $elemMatch)
• it should allow to pass custom operators
• it should use createInterpreter function from @ucast/core to create interpret function
• the return value of interpret function is an object, sequelize scope ({ include: [], where: {} })
• when it finds condition.field with dots inside it's a hint that this property may be a relation to another table. Using sequelize.js API we should be able to understand and if a field construct query for JSON field and if it's a relation add INNER JOIN + where conditions

I think I found another bug with relations in 'ucast'.

const query = AwsAccountModel.query();

const { can, build } = new AbilityBuilder(Ability);
can('read', 'AwsAccountModel', { 'id': { $in: ['0001', '0002'] } });
const ability = build();

const ast = rulesToAST(ability, 'read', 'AwsAccountModel');
const newQuery = interpret(
    ast,
    query
);
newQuery.debug().then()

returns a working sql query: select "aws_accounts".* from "aws_accounts" where "id" in($1, $2)

If i add a new rule to check for a related property, the sql query is not working anymore. It does not matter if the query for 'id' is 'in' or 'eq'.

const query = AwsAccountModel.query();

const { can, build } = new AbilityBuilder(Ability);
can('read', 'AwsAccountModel', { 'technicalContact.id': 'xxx' });
can('read', 'AwsAccountModel', { 'id': { $in: ['0001', '0002'] } });
const ability = build();

const ast = rulesToAST(ability, 'read', 'AwsAccountModel');
const newQuery = interpret(
    ast,
    query
);
newQuery.debug().then()

This will return the sql query:

select
   "aws_accounts".* 
from
   "aws_accounts" 
   inner join
      "users" as "technicalContact" 
      on "technicalContact"."id" = CAST("aws_accounts"."data" #>> '{TechnicalContact}' AS text) 
where
   (
      "id" in
      (
         $1,
         $2
      )
      or "technicalContact"."id" = $3
   )

Which generates the error column reference "id" is ambiguous.

The field "id" in the where clause should be "aws_accounts"."id".

I can't set the rule to be something like can('read', 'AwsAccountModel', { 'aws_accounts.id': { $in: ['0001', '0002'] } });, because the dot notation is always interpreted as a relation. Relation fields are always added in the correct notation "relationName"."field".

I think the root table in the where clause should always be prefixed with the table name.

Interpreter can translate a condition in a SQL Query with ambigous column names. This can happen when the condition is based on both a related table fields and the main table fields.

A solution could be to prefix all the fields in the where condition with the main table alias. What do you think about it?

Hi again, I'm using rulesToAST to convert an Ability to an AST condition and then I translate the condition in a typeorm query with ucast/sql.

I found that the Interpreter builds a wrong query when the ability has a rule with no conditions (e.g. can("read", "User")), indeed the ability is translated into r { operator: 'and', value: [] } AST condition that the interpreter translates into .where('()', []).

async function main() {
  const conn = await createConnection();

  const ability = defineAbility((can) => {
    can('read', 'User');
  });

  const astCondition = rulesToAST(ability, 'read', 'User');
  console.log(astCondition)
  // r { operator: 'and', value: [] }

  let query = interpret(astCondition, User.createQueryBuilder());
  await query.getMany()
  // SELECT "User"."id" AS "User_id", "User"."firstName" AS "User_firstName", "User"."lastName" AS "User_lastName", "User"."age" AS "User_age" FROM "users" "User" WHERE ()

  conn.close()
}

Is this the correct usage of rulesToAST and ucast/sql?

Hello,

I am testing the casl / objection integration for a project based on this gist. Since @ucast/objection is deprecated, i converted the code to use interpret from @ucast/sql/objection. But the code is creating a different sql command now - i do use knexSnakeCaseMappers for objection.

abilities

    allow(
      ['read', 'update', 'replace', 'delete'],
      [AccountModel],
      {
        'accountOwner.id': user.id,
      }
    );

    allow(
      ['read', 'update', 'replace', 'delete'],
      [AwsAccountModel],
      {
        'technicalContact.id': user.id,
      }
    );

sql command before (technical_contact):

select "aws_accounts".* from "aws_accounts"
inner join "users" as "technical_contact" on "technical_contact"."id" = CAST("aws_accounts"."data"#>>'{TechnicalContact}' AS text) 
inner join "users" as "account_owner" on "account_owner"."id" = CAST("aws_accounts"."data"#>>'{AccountOwner}' AS text)
where (("technical_contact"."id" = $1 or "account_owner"."id" = $2))

sql command now (technicalContact):

select "aws_accounts".* from "aws_accounts" 
inner join "users" as "technical_contact" on "technical_contact"."id" = CAST("aws_accounts"."data"#>>'{TechnicalContact}' AS text)
inner join "users" as "account_owner" on "account_owner"."id" = CAST("aws_accounts"."data"#>>'{AccountOwner}' AS text) 
where ("technicalContact"."id" = $1 or "accountOwner"."id" = $2)

Please delete this issue, if it is in the wrong place or irrelevant.

Best Christian

I'm currently testing mongo2js and noticed that gt and gt doesn't works on nested arrays.
I'm using sift to compare results.

const sift = require("sift");
const { filter } = require("@ucast/mongo2js");

const query = { "foo.bar.baz": { $lt: 0 } };

const uQuery = filter(query);
const siftQuery = sift(query);

const data = { foo: [{ bar: [{ baz: 1 }] }] };

const uResult = uQuery(data);
// true
// value returned by "getField" : [ 1 ] and comapred against query value "1"

const siftResult = siftQuery(data);
// false

This is because in @ucast/js, in this case, the comparison interpreters like lt compare array of values against a single value.
This is due to the getField function that returns an array of value.

I thought about a solution and because the actual value could be an array, we would need to know if values have been aggregated into an array or if the value is an array.
But there might be a simpler solution.

• this interpreter should be part of a new package @ucast/sql (put it in packages/sql)
• it should support the same operators as js interpreter from @ucast/js
• it should allow to pass custom operators
• it should use createInterpreter function from @ucast/core to create interpret function
• the return value of interpret function is an array of 3 elements: [sql, params, joins]
• when it finds condition.field with dots inside it's a hint that this property may be a relation to another table. Using custom option joinRelation we can allow users to specify logic which determines whether it's a relation.
• submodule for objection
• submodule for sequelize
• submodule for mikro-orm
• submodule for TypeORM

• this interpreter should be part of @ucast/mongo
• it should support the same operators as js interpreter from @ucast/js
• it should allow to pass custom operators
• it should use createInterpreter function from @ucast/core to create interpret function
• the return value of interpret function is an object, mongo query

This may be useful if we want to convert custom mongo query operators into regular mongo query or if we need to convert custom parser ast to mongo query

It seems that sql interpreter doesn't support conditions with nested relations.
Indeed the condition o { operator: 'eq', value: 2, field: 'x.y.z' } is translated into WHERE 'x'.'y.z' = :1.

Are you planning to support this type of conditions?

If you want check that array is not empty this combination does not work like at mongo.

{ arrayProp: { $not: { $size: 0 } } }

Can I use this library to transform an API request body validated by AJV schema into the update field in a mongo operation?

// Example Request Body
const body = {
  name: "test",
}

// --- validate body with AJV here, hereafter "body" is type MyModel ---

// Example Mongo Update
const update: UpdateFilter<MyModel> = {
  $set: {
    ...body,
    lastUpdatedBy: userId,
    lastUpdatedOn: new Date()
  }
}

// Usage Question
const updateViaUcast = todoLearnUcastHere(body, userId, new Date())
expect(updateViaUcast.$set.name).toBeTruthy()

• this interpreter should be part of a new package @ucast/objection (put it in packages/objection)
• it should support the same operators as js interpreter from @ucast/js (except $all, $where, $size. $elemMatch operates only on related table and always perform a join)
• it should allow to pass custom operators
• it should use createInterpreter function from @ucast/core to create interpret function
• the return value of interpret function is a Objection.js query builder
• it should take initial query builder as an argument
• auto join support on related table

prisma.io is a next-gen ORM which talks to various SQL DBs. I am currently using CASL with Prisma and would love to replace my current manual code with ucast.

Hi, I understand that there is a @ucast/sql package, but mikro-orm also supports MongoDB queries. And that made me think, could we possibly use const { rulesToQuery } = require('@casl/ability/extra') to construct accessibleBy?

If that's the case, would you recommend that people who want to use casl and mikro-orm together go down the rulesToQuery -> mongo operator -> mikro-orm route, or the @ucast/sql -> raw sql -> mikro-orm route?

Thanks

I found a few other projects that allow for creating an interpreter from JSON. Is it possible?

I have following condition

const condition = new CompoundCondition('and', [
  new FieldCondition('eq', 'status', 'active'),
  new CompoundCondition('or', [
    new CompoundCondition('and', [
      new FieldCondition('eq', 'createdAt', 'v'),
      new FieldCondition('eq', 'name', 'v'),
    ]),
    new FieldCondition('eq', 'updatedAt', 'v'),
  ]),
]);

This gets interpreted as

("status" = $1 and ("createdAt" = $2 and "name" = $3) or "updatedAt" = $4)

My expectation would have been for this to interpreted as

("status" = $1 and (("createdAt" = $2 and "name" = $3) or "updatedAt" = $4))

Is this expected or did I miswrote the condition? Could you please check?

Here is how to reproduce this

/* eslint-disable max-classes-per-file */
import { CompoundCondition, FieldCondition } from '@ucast/core';
import {
  createSqlInterpreter,
  pg,
  gt,
  eq,
  gte,
  lt,
  lte,
  or,
  and,
  within,
  nin,
} from '@ucast/sql';

const interpreter = createSqlInterpreter({
  eq,
  lte,
  lt,
  gt,
  gte,
  in: within,
  nin,
  and,
  or,
});

const condition = new CompoundCondition('and', [
  new FieldCondition('eq', 'status', 'active'),
  new CompoundCondition('or', [
    new CompoundCondition('and', [
      new FieldCondition('eq', 'createdAt', 'v'),
      new FieldCondition('eq', 'name', 'v'),
    ]),
    new FieldCondition('eq', 'updatedAt', 'v'),
  ]),
]);

const [where] = interpreter(condition, {
  ...pg,
  joinRelation: () => {
    return false;
  },
});

console.log(where);

hello, I'm using @casl/ability to define my abilities. I find the idea of the @casl/prisma package really good, but I'm using typeorm. I found that @ucast/sql is able to translate the ability into a where clause which will be applied to a query builder. So I tried to install: @ucast/core and @ucast/sql and got the following error:

➜  ~/Development git:(main) ✗ npm i @ucast/core @ucast/sql
npm ERR! code ERESOLVE
npm ERR! ERESOLVE could not resolve
npm ERR! 
npm ERR! While resolving: @ucast/[email protected]
npm ERR! Found: [email protected]
npm ERR! node_modules/typeorm
npm ERR!   peer typeorm@"^0.3.0" from @nestjs/[email protected]
npm ERR!   node_modules/@nestjs/typeorm
npm ERR!     @nestjs/typeorm@"^9.0.0" from the root project
npm ERR!   typeorm@"0.3.7" from the root project
npm ERR! 
npm ERR! Could not resolve dependency:
npm ERR! peerOptional typeorm@"^0.2.0" from @ucast/[email protected]
npm ERR! node_modules/@ucast/sql
npm ERR!   @ucast/sql@"*" from the root project
npm ERR! 
npm ERR! Conflicting peer dependency: [email protected]
npm ERR! node_modules/typeorm
npm ERR!   peerOptional typeorm@"^0.2.0" from @ucast/[email protected]
npm ERR!   node_modules/@ucast/sql
npm ERR!     @ucast/sql@"*" from the root project
npm ERR! 
npm ERR! Fix the upstream dependency conflict, or retry
npm ERR! this command with --force, or --legacy-peer-deps
npm ERR! to accept an incorrect (and potentially broken) dependency resolution.
npm ERR! 
npm ERR! See /Users/wolflu/.npm/eresolve-report.txt for a full report.

npm ERR! A complete log of this run can be found in:
npm ERR!     /Users/wolflu/.npm/_logs/2022-09-11T16_53_14_528Z-debug-0.log

So now my question is, is @ucast/sql still the way to go to translate into an sql query, or do I have to implement something my own via the rulesToQuery function from @casl/ability/extra?

Hope this project is not dead. Really cool project.

wolflu05

MongoDB $and, $nor and $or operators expect array of expressions (i.e. objects):

{ $and: [ { <expression1> }, { <expression2> } , ... , { <expressionN> } ] }
{ $nor: [ { <expression1> }, { <expression2> }, ...  { <expressionN> } ] }
{ $or: [ { <expression1> }, { <expression2> }, ... , { <expressionN> } ] }

However, it seems that the ucast/mongo parser doesn't validate these entries, thus it's possible to call:

parse({ $and: ["foo", 42] })  // e.g. from received HTTP query

No exception thrown but the following condition returned:

{
  "operator": "and",
  "value": [
    {
      "operator": "eq",
      "value": "f",
      "field": "0"
    },
    {
      "operator": "eq",
      "value": "o",
      "field": "1"
    },
    {
      "operator": "eq",
      "value": "o",
      "field": "2"
    }
  ]
}

42 seems to be ignored in this case, probably because Object.keys(42) returns [].

Instead, we should expect an error like Error('"$and" expects value to be an array of objects');

Same applies to calling parse("foo") directly with a non object value, which should be prevented by type checking (i.e. parse expects a record) but can still happen if the value is any. Though I'm not sure if it should be handled by the lib or explicitly by the user, which should make sure to pass an object to parse.

Hi,

In our app we use webpack 5 and ucast throws some error while building the app. On investigating i found that the error happens because of the type field defined here
https://github.com/stalniy/ucast/blob/master/packages/core/package.json#L5

Here the type is defined as commonjs so webpack looks at this field and decide that this is a commonjs package.
Here is a similar issue
webpack/webpack#11597
projectfluent/fluent.js#517

It would be better if ucast remove the type field from all the packages' package.json. Then webpack can look at other field like module,main and decide which bundle to use.

I got arr in (?, ?) instead of ('one', 'two')

• this interpreter should be part of a new package @ucast/mikro-orm (put it in packages/mikro-orm)
• it should support the same operators as js interpreter from @ucast/js (the most complex is $elemMatch)
• it should allow to pass custom operators
• it should use createInterpreter function from @ucast/core to create interpret function
• the return value of interpret function is a query builder
• it should take initial query builder as an argument
• when it finds condition.field with dots inside it's a hint that this property may be a relation to another table. Using mikro-orm API we should be able to understand and if a field construct query for JSON field and if it's a relation add INNER JOIN + where conditions

Basic example:

const query = filter({ $lt: 3,  $gt: 0  });

const data = 2

const result = query(data)
// true
// now return  Unknown top level operator "$lt"

I've noticed that this top level "or" is parsed as "and" incorrectly. Is there something wrong with my condition syntax? Thanks!

// Example 1:
import { Condition } from '@ucast/core';
import { MongoQueryParser, allParsingInstructions } from '@ucast/mongo';

const parser = new MongoQueryParser(allParsingInstructions);
const condition = { $or: [{ id: { $eq: 1 }, name: { $eq: 'abc' } }] };
const ast = parser.parse(condition);
console.log(ast);

// ast shows operator "and". Expecting operator "or".
/*
{
  operator: "and",
  value: [
    {
      operator: "eq",
      value: 1,
      field: "id",
    },
    {
      operator: "eq",
      value: "abc",
      field: "name",
    },
  ],
}
*/

// Example 2:
import { Condition, FieldCondition, CompoundCondition } from '@ucast/core';
import { MongoQueryParser, allParsingInstructions } from '@ucast/mongo';

const parser = new MongoQueryParser(allParsingInstructions);
const condition = new CompoundCondition('or', [
    new FieldCondition('gt', 'x', 5),
    new FieldCondition('lt', 'y', 10),
]);
const ast = parser.parse(condition);
console.log(ast);

// again, ast shows operator "and". Expecting operator "or".
{
  operator: "and",
  value: [
    {
      operator: "eq",
      value: 1,
      field: "id",
    },
    {
      operator: "eq",
      value: "abc",
      field: "name",
    },
  ],
}

Hello,
Thank you for this very useful package.

I have a problem with a query supported within mongodb but not within the package.

const store = [{ foo: { bar: 'value' }}];
const predicate = guard({ foo: { bar: 'value' }} as any); // any to avoid typing error
const result = store.filter(predicate);

but it works with:

const store = [{ foo: { bar: 'value' }}];
const predicate = guard({ 'foo.bar': 'value' } );
const result = store.filter(predicate);

Result:

Is it easy to fix ?

• configure lint-staged
• fix eslint errors
• configure codecov
• integrate semantic-release
• create a chat room (use gitter or alternative). Add badge to chat
• publish

Hi, I'm testing ucast with typeorm query builder because we're planning to use casl in one our projects.

I've noticed that when I try to evaluate the query, postgres driver fails with a syntaxerror on the query:

QueryFailedError: syntax error at end of input
    at new QueryFailedError (/Users/claudio/dev/tests/casl/src/error/QueryFailedError.ts:11:9)
    at PostgresQueryRunner.<anonymous> (/Users/claudio/dev/tests/casl/src/driver/postgres/PostgresQueryRunner.ts:228:19)
    at step (/Users/claudio/dev/tests/casl/node_modules/tslib/tslib.js:143:27)
    at Object.throw (/Users/claudio/dev/tests/casl/node_modules/tslib/tslib.js:124:57)
    at rejected (/Users/claudio/dev/tests/casl/node_modules/tslib/tslib.js:115:69)
    at processTicksAndRejections (internal/process/task_queues.js:93:5) {
  length: 92,
  severity: 'ERROR',
  code: '42601',
  detail: undefined,
  hint: undefined,
  position: '147',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'scan.l',
  line: '1172',
  routine: 'scanner_yyerror',
  query: 'SELECT "u"."id" AS "u_id", "u"."firstName" AS "u_firstName", "u"."lastName" AS "u_lastName", "u"."age" AS "u_age" FROM "users" "u" WHERE "age" > ?',
  parameters: []
}

As you can see ? symbol is not interpreted by typeorm. Instead, as documented here, it expects that the parameter placeholders are like :parameter_name and parameters values are passed in an object { parameter_name: value }.

I created a PR, let me know your thoughts. Thanks :)