[typeorm] Add support for CTEs in authorizers

[dberardo-com]

great library. however i have some complex authorization logic that require deep tables lookups to figure out what to authorize and what not.

specifically the ID of the object to be autorized should fall in a list of IDs which contains potentially thousands of records, which is not possible to return as a Filter in a custom autorize method.

Currently i am using CTEs in typeorm to create a sort of subregion from which the actual query is executed and this works fine.

To implement something like this with this library i can either:

• add a virtual attribute to all my tables ("authorized") and then add a filter like { authroized: { eq: true} }
• OR use CTEs based on the userContext attributes

i believe the second is the most elegant solution but how to deal with this ?

[dberardo-com]

i believe adding CTEs would be possible by ovveriding the query service and overriding the original query function which returns a querybuilder:

so basically follow this example: https://tripss.github.io/nestjs-query/docs/persistence/typeorm/custom-service/

but adding a new query method: https://github.com/TriPSs/nestjs-query/blob/master/packages/query-typeorm/src/services/typeorm-query.service.ts#L86

and once there i could either add a virtual "authorized" boolean column to be then filtered upon in the authroized, or add a "where" clause to filter down the scope of available resources.

the question is ... would either of those 2 be overwritten in further steps down the line by the library ? which one would be more robust ?

[TriPSs]

I'm not understanding the problem fully here if but if you need a flag you could also make a non existing field and check for that?

[dberardo-com]

thanks for your quick reply,

essentially my problem is that i have a lot of rows in the DB and so it is not feasible IMO to pass the whole arraay of permitted IDs for a given object to the authorizationfilter. does it make sense ?

in fact i dont want to materialize the query at all, but i want to add a CTE or a WHERE filter directly to the queryBuilder in order to avoid materialization and perform authorization

[TriPSs]

Question: why would the auth filter not contain the relation filter? Could you maybe give some examples with the DTO or your current implementation?

I think, if the auth filter is not suitable creating your own service and overwriting the query is indeed the way to go.