Help with complex query

[nimble anvil]

I'm trying to query some models but it has to filter out based on some permissions

The permission table is as follows:

id, user_id, role_id, action, resource_type, resource_id, allow

• Either user_id or role_id exists

For example

user_id, role_id, action,         resource_type,       resource_id, allow
1        null   , project.view,   App\Models\Project   1            true

This indicates that the user "1" can view the project (therefore should be included in the query).

The system makes this exception : If the user has allow set to true nomatter what the role says, it should be granted this permission. For example:

user_id, role_id, action,         resource_type,       resource_id, allow
1        null   , project.view,   App\Models\Project   1            true
null     1      , project.view,   App\Models\Project   1            false

Assuming user 1 has role 1, he would still be granted access since it is being overridden on the user.

However:

user_id, role_id, action,         resource_type,       resource_id, allow
1        null   , project.view,   App\Models\Project   1            false
null     1      , project.view,   App\Models\Project   1            true

he would not be granted access since it is being overridden on the user.

Whenever a user permission is missing, it is assumed to grab it from the role. So in the following case:

user_id, role_id, action,         resource_type,       resource_id, allow
null     1      , project.view,   App\Models\Project   1            true

It is allowed for any user in the role 1 to see the project 1.

(continued in comment)

The code is as follows:

    public function scopeWithPermission(Builder $query, string $action): Builder
    {

        $query->where(function ($query) use ($action) {
            $query->whereHas("permissions", function ($builder) use ($action) {
                // scenario 1:
                // no role specified and user has permission -> Should allow
                $builder->where('user_id', auth()->id())
                    ->where('action', $action)
                    ->where('allow', true);
            })->orWhereHas("permissions", function ($builder) use ($action) {
                // scenario 2:
                // user not specified but role has permission -> Should allow
                $builder->whereNotExists(function ($q) use ($action) {
                    $q->where('user_id', auth()->id())->where('action', $action);
                })->where(function ($builder) use ($action) {
                    $builder->whereIn('role_id', auth()->user()->roles->pluck('id'))
                        ->where('action', $action)
                        ->where('allow', true);
                });
            })->orWhereHas("permissions", function ($builder) use ($action) {
                // scenario 3:
                // user is allowed and role is also alloswed
                $builder
                    ->where('user_id', auth()->id())
                    ->where('action', $action)
                    ->where('allow', true);
                $builder
                    ->whereIn('role_id', auth()->user()->roles->pluck('id'))
                    ->where('action', $action)
                    ->where('allow', true);
            });
        });
        return $query;
    }

It seems to work (at least in my head) but it fails in this scenario:

user_id, role_id, action,         resource_type,       resource_id, allow
1        null   , project.view,   App\Models\Project   1            false
null     1      , project.view,   App\Models\Project   1            true

Scenario 1 shouldn't be fulfilled (no direct permission set to true)
Scenario 2 should also not be fulfilled since there exists one row with user_id
Scenario 3 should also not be fulfilled since they're not both true.

I don't understand where the logic issue arises :/

[turbid lagoon]

Are you using strings in the "allow" column?

Have you tried calling ->toRawSql() on the query to check if it looks like you'd expect?

[nimble anvil]

no it's either 0 - 1 (it's a $table->boolean column).

there was something wrong with the $builder->whereNotExists

It wasn't searching the correct table

I had to change it to:

 $builder->whereNotExists(function ($q) use ($action) {
                    $q->from("permissions")->where('user_id', auth()->id())->where('action', $action)->where('resource_type', self::class)->whereRaw('permissions.resource_id = ' . $this->getTable() . '.id');
                });

basically include a "from" and a whereRaw, to get the correct conditionals + table alias

[turbid lagoon]

Yeah that makes sense. You might want to consider using whereHas() / whereDoesntHave() when it's an Eloquent query. Then you don't have to specify and link the table manually, plus you can use the related model's scopes in the subquery. And if the related model has global scopes like SoftDeletes, they are applied automatically.

[nimble anvil]

I assumed since I'm already in a whereHas it will automatically do it

It still hasn't clicked 100% in my head, if you could give me an example on how to do it it would be amazing

[little panther]

I have to ask - why are you re-inventing the wheel? Spatie Permissions or Bouncer are both well developed packages for managing roles/permissions

[turbid lagoon]

I'm not OP but I've been in situations where the access control requirements weren't supported by any 3rd party package I could find.

[little panther]

Interesting, I have fairly odd control requirements and managed with Bouncer quite well

[turbid lagoon]

You can use whereHas() inside whereHas() subqueries. It also supports dot notation, so you can reach deep into the relations, like whereHas("user.posts.comments", ...)

[nimble anvil]

Re-inventing the wheel is a good way to understand how things work.

apart from that it's what @turbid lagoon said. Sometimes you have constraints that you have to do a lot of parkour within the library configuration in order to make it work.. But then you are susceptible to api changes from the library

and lastly, you don't have any fear of upgrades, since you are the author of your own code

[little panther]

Yep, there are definitely use cases for going it alone, but when it comes to something like permissions etc, it's typically best to use something tried, tested, and validated by a wider audience.

No reasonable corporation will be able to readily gain assurance on something thrown together solo, and they'll always have concerns that "Only X knows how to work with it", and they'll swiftly move on to something that has wider support and assurance.

Going it alone, yes, you guarantee your codebase, but a proper test suite mitigates any risk of upgrading 3rd party packages

If you fear upgrading, then don't use a framework full stop, as Laravel by itself has dozens of dependencies that can update on a moment's notice.
Avoiding upgrades "because it might break something", is a hacker's paradise, and no customer worth their salt will accept stagnant approaches. It may be that you target and work with organisations that don't care about that, but after the first major breach, they'll very much care about what's gone on behind the scenes, and you find yourself in a world of hurt.

There's always a use case for self-building, but when it comes to something as readily available as permissions etc, this is when you lean on the open source community. The fact that Spatie Laravel-Permission has 40m installs, any vulnerability is going to be very swiftly picked up, reported and rectified.
A "I'll do it myself" approach, will be picked up by precisely nobody but yourself (which if you have a secops/cybersec team dedicated to code review - probably less of an issue)

[nimble anvil]

I agree with you, however as I remember there's no way on spatie to query from the database only models that the user has a view permission on...

[little panther]

Pretty sure Spatie provides access to a user's permissions which are cached (so doesn't hit your db), then you'd just need to filter the collection appropriately

[nimble anvil]

I can't load 5000 entities into memory then filter them...

It has to happen during pagination

In my solution there's a table for the user and role permissions which you can join to only bring the rows that should be viewed

[little panther]

Not sure why you'd need to load in that many entities into memory when you can constrain and scope the relation.

Note that spatie still uses databases behind the scene, so you can constrain the relationship at any point

[nimble anvil]

" then you'd just need to filter the collection appropriately" I thought this is what you meant

[little panther]

I believe they moved to some segregated caching as well, so depending on your cache provider, that can filter as well

[nimble anvil]

Wait, I just opened the documentation... How would I load all the books "Books" that the current user has access to?!

[little panther]

I use Bouncer over Spatie (personal preference, it has better scopes for my use case)

It's dependent on if they're all directly assigned, or if you have some role/ownership rules, which will complicate it somewhat.
If all directly assigned then you can use the permission() scope on the user model I believe, it's been a long time since I used it!
For Bouncer I have weird use cases based on per-tenant, per-team, plus direct permissions, so it ends up being a merge of a couple of collections to get everything a user can see based on tenant, team ownership, user ownership, and directly assigned.
That's my own fault for making permissions assignment too granular tho

[nimble anvil]

If all directly assigned then you can use the permission() scope on the user model I believe, it's been a long time since I used it!

My constraints follow the discord ones.
A role permission followed by a user permission which overrides the role permission