How to make database policies so to respect N-N relationships

[karim-jouini42]

Hello,

Need help dear comunity

• Backend: Azure SQL (so mssql)
• Schema: I have a Projects table, a Users table, and a linking table UserProjects (with user_id, project_id fields), that tells what projects can each user see
• Token: the authentication token contains a series of claims, including @claims.userId of the authenticated user

I protect the tables with Policy/Database in dab-config, like this, on both UserProjects table and Users table

"permissions": [
{
"role": "Admin",
"actions": [
{
"action": "read",
"policy": {
"database": "@item.user_id eq @claims.userId"
}
}
]
}
]

I can't find a way to configure dab-config.json so that the following query, only returns projects my user can see. It keeps on behaving as if it is a LEFT JOIN not an INNER JOIN

query
{
projects
{
items
{
id,
name,
Users
{
items
{
id,
name
}
}
}
}
}

Any documentation I can read?

PS: I don't want to use RLS in SQL (it has many drawbacks, including perf)