RLS via Plugin?

[gdbjohnson]

I'm wanting to enforce row-level-security via Kysely by adding a where clause to all queries in the project. I'm playing with plugins to be able to do this, but I'm finding it to be a bit of a grind to figure out how to add the condition. I have taken a look at the kysely-access-control project which looks promising, but doesn't seem well adopted.

To start with a simple case, for all select queries, I want to add "orgCode = X" to all queries. I started with extending the OperationNodeTransformer for the bulk of the logic needed, with the below:

class RLSTransformer extends OperationNodeTransformer {

constructor(private orgCode: string, private userId: string) {
  super();
}

protected transformSelectQuery(node: SelectQueryNode): SelectQueryNode {      
  const orgCode = expressionBuilder<Database, "accounts">().eb("orgCode", "=", this.orgCode);
  const orgCodeNode = orgCode.toOperationNode();
  const newWhere = WhereNode.create(AndNode.create(orgCodeNode, node.where!.where))

  const newNode = {
    ...node,
    where: newWhere
  };
  return super.transformSelectQuery(newNode);
}
}

This works as expected... the compiled SQL looks good. I call it like so:

return db.selectFrom('accounts') .withPlugin(createRLSPlugin({ userId: 'userId', orgCode: 'orgCode' })) .where('id', '=', id) .selectAll() .executeTakeFirst()

However, the challenge with this approach, is that the querybuilder requires that I know the type when building the query. My meta data is guaranteed to be consistent for all tables, and I just want to append the sql snipped orgCode = XX to all queries in a more naive way.

Is this possible to do (easily)?

[koskimas]

However, the challenge with this approach, is that the querybuilder requires that I know the type when building the query.

I'm not following. What do you mean? What type of what query? The query in the plugin?

[gdbjohnson]

@koskimas : essentially, for every Select query that applies this plugin, I want to add "orgCode = X" in the where clause, for all queries. The idea is to abstract out the handling of meta data to a central place, so that devs working on business logic don't have to think about it. By centralizing it here, we can ensure better guarantees of consistent handling of this important piece of security as well.

[koskimas]

You didn't answer my question at all.

[gdbjohnson]

@koskimas perhaps I'm not understanding what your question is. I've reread my question and I don't understand how it is unclear as to what I am trying to do.

In my project, we are following a convention that all tables have a field in them, a tenant ID. I want to use kysely to transform all queries being built to add a where condition. The challenge I am facing is that using expressionBuilder requires that I know the type of the query being transformed.

At the end of the day, kysely generates a string, and I just want to add "orgCode = 'XYZ'" in a where clause to all the queries. I would think a plugin is the natural way to do this.

Hope that clarifies?

[gdbjohnson]

Inside the plugin, I have access to the node hierachy created by kysely as it's parsing the query. I want to manipulate that tree by adding this where clause. So, I guess it's the query in the plugin?