SQL error when two rules refer to the :read action with hash parameters about cancancan HOT 16 CLOSED

@bryanrite: actually your suggestion from #74 does not work... apparently it's more subtle than just aliases. As this only happen with :read and when rendering :index I'm inclined to think it's related to the accessible_by magic.

from cancancan.

Okay I'm closing in on the bug, I don't even need a custom alias!

The following fails:

def initialize(user)
  can :read, Item, permissions: { user_id: user.id }
  can [:read, :update, :destroy], Item, permissions: { user_id: user.id }
end

[EDIT] the following fails too:

can :read, Item, permissions: { user_id: user.id }
can :read, Item, permissions: { user_id: user.id } # yes, the exact same rule

@bryanrite: so basically, simply having two rules related to the :read action with parameters. I guess it only happens in a polymorphic context.

from cancancan.

I'm trying to write a test for this and it always works. Maybe this is triggered by another gem or something, I'll try to figure it out.

from cancancan.

Ooooooooook. The problem seems to be within ActiveRecord:

If you have only one rule this is what happens in the end (which works):

Item.where(permissions: { user_id: 1 }).includes([:permissions])

If you have two rules about :read this is what happens in the end (which fails):

Item.where("(`permissions`.`user_id` = 1) OR (`permissions`.`user_id` = 1)").includes([:permissions])

ActiveRecord seems to ignore the includes call. If I use joins instead then it works.

from cancancan.

This is very weird, because I can't reproduce that outside of my project. Something seems to prevent includes from working. I'll need to investiguate ActiveRecord now I guess.

from cancancan.

@bryanrite: I think I found something... loot at http://apidock.com/rails/ActiveRecord/QueryMethods/includes and http://apidock.com/rails/ActiveRecord/QueryMethods/references

It basically says that calling includes is not enough, and that one should call references. If I add @items = @items.references(:permissions) then things work again.

Now, that doesn't explain why I'm unable to reproduce it... I thought it was related to sqlite or the in-memory storage, but apparently not. I think I'll first create a sample rails projects illustrating the problem, and then we'll try to figure how to make a test out of it, do you reckon it's a good idea?

from cancancan.

Yup, sounds great. I know there was some discussion about using references when we added Rails 4 support, but since its Rails 4 only, I wanted to pull it out into a separate adapter. See #40 and #72

from cancancan.

Ah, so theorically once 72 is merged my problem should solve itself. Good news :)

from cancancan.

I can confirm this problem. It happens when defining two read abilities on the same object, with different scopes. For example:
can :read, Thing, thingy: { roles: { user_id: user.id } }
can :read, Thing, other_thingy: { roles: { user_id: user.id } }

The error I got is:
ActiveRecord::StatementInvalid:
PG::UndefinedTable: ERROR: missing FROM-clause entry for table "roles"
LINE 1: SELECT "categories".* FROM "categories" WHERE (("roles"."ac...
^
: SELECT "categories".* FROM "categories" WHERE (("roles"."user_id" = 3 AND "roles"."role_type" = 'admin') OR ("roles"."user_id" = 3 AND "roles"."role_type" = 'admin'))

Lets hope #72 will fix it. I will try as soon as its merged and released.

from cancancan.

I just tried the references fix. It indeed fixes the SQL error, but still doesn't lead to the desirable result. The accessible_by scope seems to work correctly only for the last ability defined. The other objects that should be accessible through the first ability are not returned.

For this reason I doubt that #72 alone will fix the problem.

from cancancan.

Interesting.

What I don't understand is why cancan is not able to generate

Item.where(permissions: { user_id: [1, 2] })

instead of

Item.where("(`permissions`.`user_id` = 1) OR (`permissions`.`user_id` = 2)")

I think this would solve the problem and be cleaner. Probably a technical reason behind it.

from cancancan.

This is not the problem, since there is a single user in the ability. It does something like

Item.where("(`project_permissions`.`user_id = 1) OR (`account_permissions`.`user_id` = 1)")

I believe this cannot be solved with an IN array query.

The exact situation in my case is as follows. I have the following permissions defined:

can :read, Category, account: nil, library: { provider: { roles: { actor_id: actor.id, role_type: ['admin', 'viewer'] } } }
can :read, Category, account: { roles: { actor_id: actor.id, role_type: ['admin', 'viewer'] } }

I have global library categories that may only be accessed by actors that have a role on the provider of that library. Furthermore, I have account specific categories, that may be accessed by any actor that has an role on that account.

Running Category.accessible_by(some_actor, :read) results in:

PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "roles"
LINE 1: SELECT "categories".* FROM "categories"  WHERE (("roles"."ac...
                                                         ^
: SELECT "categories".* FROM "categories"  WHERE (("roles"."actor_id" = 1 AND "roles"."role_type" IN ('admin', 'viewer')) OR ("categories"."account_id" IS NULL AND "roles"."actor_id" = 1 AND "roles"."role_type" IN ('admin', 'viewer')))

Applying the references fix, solves the problem partially. Running Category.accessible_by(some_actor, :read).references(:role) results in correct results for the last case (account specific categories), but not for the first (global library categories). The resulting query is fairly complexed due to many joins:

 SELECT ... FROM "categories"
LEFT OUTER JOIN "accounts" ON "accounts"."id" = "categories"."account_id"
LEFT OUTER JOIN "roles" ON "roles"."rolable_id" = "accounts"."id" AND "roles"."rolable_type" = 'Account'
LEFT OUTER JOIN "libraries" ON "libraries"."id" = "categories"."library_id"
LEFT OUTER JOIN "providers" ON "providers"."id" = "libraries"."provider_id"
LEFT OUTER JOIN "roles" "roles_providers" ON "roles_providers"."rolable_id" = "providers"."id" AND "roles_providers"."rolable_type" = 'Provider'
WHERE (("roles"."actor_id" = 1 AND "roles"."role_type" IN ('admin', 'viewer'))
OR ("categories"."account_id" IS NULL AND "roles"."actor_id" = 1 AND "roles"."role_type" IN ('admin', 'viewer')))

I believe the problem occurs because we are using the roles table twice. Rails correctly distinguishes them by calling the second reference "roles_providers". However, in the WHERE clause, CanCanCan only uses the reference "roles", where in fact the "roles_provider" reference should be used the second time (the right hand operand of the OR clause).

Btw the problem also occurs when swapping the ability definitions. In that case the global library categories succeed, but the account specific categories however do not.

from cancancan.

I am having a similar problem, with two relatively simple permissions. What I'm trying to do is allow users to view other users who are either members of approved teams or members of their own (potentially unapproved) team. I'm using Rails 4.1.2.

Here is my ability.rb:

if user.role == 'participant'
  can [:index, :show], User, :team => { :status => 'approved' }
  can [:index, :show], User, :team => { :id => user.team_id }
end

When I try to access the index method, the following SQL and error are generated:

SELECT  "users".* FROM "users"  WHERE "users"."id" = 5  ORDER BY last_name LIMIT 1
SELECT  "teams".* FROM "teams"  WHERE "teams"."id" = $1  ORDER BY name LIMIT 1  [["id", 1]]
SELECT  "users".* FROM "users"  WHERE "users"."id" = $1  ORDER BY last_name LIMIT 1  [["id", 7]]
SELECT "users".* FROM "users"  WHERE (("teams"."id" = 1) OR ("teams"."status" = 'approved'))  ORDER BY last_name

PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "teams"

When only the first ability definition is used, the last line of the generated SQL looks like

SELECT "users"."id" AS t0_r0, "users"."email" AS t0_r1, [...lots of parameters...] "users"."last_name" AS t0_r15, "users"."team_id" AS t0_r16, "teams"."id" AS t1_r0, "teams"."name" AS t1_r1 FROM "users" LEFT OUTER JOIN "teams" ON "teams"."id" = "users"."team_id" WHERE "teams"."status" = 'approved'  ORDER BY last_name

And everything works as expected (given the single ability definition). When only the second ability definition is used, everything works too. It seems that the problem has to do with accessing nested attributes twice and combining the results with a logical OR.

Interestingly, using the show method (when both abilities are defined) raises no errors and behaves as expected, generating the following SQL:

SELECT  "users".* FROM "users"  WHERE "users"."id" = $1  ORDER BY last_name LIMIT 1  [["id", 8]]
SELECT  "users".* FROM "users"  WHERE "users"."id" = 5  ORDER BY last_name LIMIT 1
SELECT  "teams".* FROM "teams"  WHERE "teams"."id" = $1  ORDER BY name LIMIT 1  [["id", 1]]
SELECT  "users".* FROM "users"  WHERE "users"."id" = $1  ORDER BY last_name LIMIT 1  [["id", 7]]
SELECT  "teams".* FROM "teams"  WHERE "teams"."id" = $1  ORDER BY name LIMIT 1  [["id", 1]]

Do you suppose that #72 will fix this? If so, I hope it's merged soon.

from cancancan.

@jamesmaniscalco you can test this yourself by trying the references fix. I suppose somewhere in your code (probably your UsersController#index action) you perform
@users = User.accessible_by(current_ability, :read).
Change this to @users = User.accessible_by(current_ability, :read).references(:team).

For me it did indeed fixed the error, but it did not lead to the results I wanted/expected. The query returns the correct results for the later case (the last ability defined), but not for the first. Could you try this and post your findings?

from cancancan.

@kreintjes that fix seems to work for my purposes. I wasn't making that call explicitly, but CanCan was doing it implicitly as described in the wiki with load_and_authorize_resource. I changed the controller to the following:

load_and_authorize_resource :except => :index

def index
  authorize! :index, User
  @users = User.accessible_by(current_ability, :index).references(:team)
end

Now the SQL that gets generated looks like this:

SELECT "users"."id" AS t0_r0, ....... "teams"."name" AS t1_r1 FROM "users" LEFT OUTER JOIN "teams" ON "teams"."id" = "users"."team_id" WHERE ("teams"."id" = 9) OR ("teams"."status" = 'approved'))  ORDER BY last_name

Which has both the LEFT OUTER JOIN and the logical OR statement in the WHERE clause.

Thanks for your help!

from cancancan.

Seems to me this issue can be closed. Feel free to reopen in case.

from cancancan.