SQL error when visiting the Users admin page (PostgreSQL)

[battika]

Hello,

I'm getting the following error when visiting the Users page (using PostgreSQL):

SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: integer = character varying
LINE 1: ..."user" LEFT JOIN "auth_assignment" ON "user"."id" = "auth_as...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
The SQL being executed was: SELECT "id", "username", "email", "status" FROM "user" LEFT JOIN "auth_assignment" ON "user"."id" = "auth_assignment"."user_id" LIMIT 12

It is caused by the following line in UserController.php file:

        $users = $model->select('id, username, email, status')
                       ->joinWith('role')
                       ->orderBy($sort->orders)
                       ->limit($pagination->limit)
                       ->offset($pagination->offset)
                       ->all();

It's because user.id is a serial while auth_assignment.user_id is a varchar by definition. PostgreSQL will not allow to use the "=" operator with arguments of different types.

[nenad-zivkovic]

In your database in auth_assignment table you can change user_id to be integer, or how you call it serial. This has nothing to do with template, if you were using rbac migration provided by yii2 developers (like I did) sooner or later you would get the same error. I can change rbac migration in my templates and update auth_assignment user_id to be integer so you will not have problems on PostgreSQL. hope it will not cause some other problems, at least it shouldn't... Thanks for reporting and please tell me does this fix helps.

[battika]

Hello @nenad-zivkovic , you are right. I go ahead and report this on the yii2 repository and see why they chose varchar and not integer. Thank you.

[nenad-zivkovic]

I have released version 1.2.1 that is fixing this issue by changing auth_assignment user_id field to integer type. I think that there is no real reason for it to be varchar. Maybe the creator of original migration was thining that someone may use some other data as user_id like username or something like that, but I think that integer is better option anyway.

[battika]

Thank you