What is the best way to do row level security in Node.JS + SQL DBMS?

Question

MySQL

Can you please advise what is the best way to implement row-level security in Node.JS + SQL RDBMS?

For example, a small ERP system with different branches. Each branch sees invoices and customers only for itself.
The manager sees only his clients, and the head of all. Accordingly, all reports should also be limited to roles.

-------------------------------------

So far I have found, such options:
(if something is wrong, do not judge strictly)

At the application level:
1. Add WHERE predicates to each query.
Pros: everything works quickly and easily. You can do complex SELECTs for reports
Cons:complexity in support, if you need to add a role or change, you need to shovel all the code.

2. ORM Sequilize with scopes in models.
Pros: ease of support, added a role condition to the scope and that's it. You can switch to another DBMS at no extra cost.
Cons: It is problematic to make a complex query for a report with a join of a dozen tables and aggregated groupings.
High entry threshold for new programmers who have not worked with him before. There may be performance issues.

3. Filtering the received data on js
Pros: everything is in JS, without complex queries to the database.
Minuses:not just support. Problems with performance and traffic. It's not easy to do pagination, count total, ...

At the DBMS level:
4. at the View level .
Pros: simple implementation. High performance
Cons: Each role has its own view. In the application, you need to determine which view the request is for. During migrations, views need to be changed. In all SELECTs, you need to put a variable instead of the table name.

5. Pull stored procedures
Pros: everything is fast
Cons: the approach is considered outdated, so it seems to be unfashionable. New programmers should learn the DBMS language.

6. Use a DBMS with built-in row level security for examplePostgres (RLS)
Pros: It 's simple. Enabled database-level policies and that's it.
Cons: Sequilize can have problems because, for Postgres, you can't pass a session variable via SET= . This is needed to define RLS roles. Since the connection of the node is permanent or in the pool, after each request, it must then be reset to zero so that other sessions do not receive the rights of the previous one. (the Knex query builder can with SET=) . Or how in php every time to create a new connection and then delete it.

Answer 1

I think Sequilize is heavy for such things and complex queries.
You can try knex .
In one place, you create a subquery with dynamic where conditions depending on the user's roles. Then, in reports and other selects, you do not refer to the table, but to a limited subquery. According to this principle:

const ClientsSubQuery = knex.select(1).as('clients');
const query = knex.select('*').from(ClientsSubQuery);
console.log(query.toSQL());

It looks like views, but without the drawbacks, everything is on the application side and more functional. For example, if a user has several roles and you need data on all of their where-conditions for a specific table.
You can decide whether you need ORM by watching this video:
https://www.youtube.com/watch?v=bkDUIIho70o
You can make a good application without ORM.
If you really want to, you can use Objection.js or Bookshelf.js. These are more understandable ORMs (unlike Sequilize), based on knex and most likely compatible with the approach shown above.
PS: knex can migrate and much more. If desired, as with ORM, you can then switch your application, for example, from MySQL to Postgres or vice versa