'column "id" does not exist' error while trying to associate a Role with a User using rails-authorization

Question

I'm exploring Rails for the first time and trying to add some fairly straightforward role-based security to my test app. Some Googling seemed to indicate rails-authorization is the way to go for this. I followed the README and everything seemed to be going well, but now I'm trying to associate a User with a Role and it fails. Here's the snippet from my script/console session:

>> u = User.find(:first)
=> #<User id: 1, login: "cwhit", name: "", email: "[email protected]", crypted_password: "7ac064547fb8992e8e53e936df31657a40f9c5af", salt: "56671492059f8e40eb3d509940944aaba31ebc72", created_at: "2009-03-26 18:06:04", updated_at: "2009-03-26 18:06:04", remember_token: nil, remember_token_expires_at: nil>
>> r = Role.find(:first)
=> #<Role id: 1, name: "ProjectManager", authorizable_type: nil, authorizable_id: nil, created_at: "2009-03-27 11:02:35", updated_at: "2009-03-27 11:02:35">
>> u.has_role r
ActiveRecord::StatementInvalid: PGError: ERROR:  column "id" does not exist
LINE 1: ....546623', '2009-03-27 11:42:16.546623', 5, 1) RETURNING "id"
                                                                   ^
: INSERT INTO "roles_users" ("created_at", "updated_at", "role_id", "user_id") VALUES('2009-03-27 11:42:16.546623', '2009-03-27 11:42:16.546623', 5, 1) RETURNING "id"

Am I just doing something silly, or is this a known issue? I found essentially the same error in a question in the Google Group for the rails-authorization plugin, but there was no solution provided.

Here's my basic config:

• OS X
• Rails 2.3.2
• PostgresQL
• Plugins:
  • restful-authentication
  • rails-authorization

Answer 1

Ran into the same problem with models where I was using a primary key other than 'id'. Turns out I had forgotten to declare it in the model class definition using "set_primary_key". Perhaps you need to do something like this:

class CulpritClass < ActiveRecord::Base

  set_primary_key :key_field_you_want

end

Answer 2

Try installing the composite_primary_keys gem (link) and add the following to the RolesUser controller:

set_primary_keys :user_id, :role_id

Since there is no id field for that table (and there doesn't need to be), the primary key becomes (user_id, role_id) which you can add to the database with

 ALTER TABLE roles_users ADD PRIMARY KEY (user_id, role_id);

This solved the problem in my case, which seems to be Postgres-specific.

Answer 3

I simply used two primary key declarations

set_primary_key :user_id

set_primary_key :role_id

This worked for my purposes but keep in mind that it returns (in the sql at least) the second primary key that you define. In this case it would be the role_id of the object.

Answer 4

I had the same problem but on destroy (of roles). I tried the composite_primary_keys gem but found that it broke other things in Rails 2.3.4. Specifically, it caused Rails :belongs_to association to generate the wrong attributed id in this situation:

   belongs_to :inviter, :class_name => 'User'

It was generating user_id as the attribute name instead of inviter_id.

The ultimate solution was to add an id column to roles_users. Since I already had a roles_users table with no surrogate key I had to do this lil migration:

  class AddIdToRolesUsers < ActiveRecord::Migration
    def self.up

      # create new table the way it should be (_with_ a traditional Rails id column)
      create_table :x_roles_users do |t|
        t.belongs_to :role
        t.belongs_to :user
        t.timestamps
      end

      execute 'insert into x_roles_users (user_id, role_id, created_at, updated_at) select * from roles_users'

      drop_table :roles_users

      rename_table :x_roles_users, :roles_users
    end

    def self.down
      remove_column :roles_user, :id
    end
  end