Using database sessions not working due to the email being used as key

[philiplb]

Hi,

we are using the database for storing the sessions with the recommended schema:

Schema::create('sessions', function (Blueprint $table) {
    $table->string('id')->primary();
    $table->foreignId('user_id')->nullable()->index();
    $table->string('ip_address', 45)->nullable();
    $table->text('user_agent')->nullable();
    $table->text('payload');
    $table->integer('last_activity')->index();
});

But using the given model in the provider like this (auth.php):

'providers' => [
    'users' => [
        'driver' => 'keycloak-users',
        'model' => Vizir\KeycloakWebGuard\Models\KeycloakUser::class,
    ],
],

It results in an exception when it tries to store the session:

[previous exception] [object] (PDOException(code: 22P02): SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type bigint: \"foo@bar.com\" at /var/www/laravel/vendor/laravel/framework/src/Illuminate/Database/Connection.php:510)

What could I do here?

[mariovalney]

Hello! How are you?

The default model "KeycloakUser" uses the e-mail as ID (you can check here).

So you shouldn't use "foreignId" as column type to "user_id". Please, change it to string.

[philiplb]

Hi,

thank you for the very fast answer :) Yes, true. I am using this type currently due to this documentation: https://laravel.com/docs/8.x/session#database Do you know if it is save to just change that to a string type?

[mariovalney]

I'm not sure, but you can try.

Just make a rollback and change the column type. But... This user provider is not saving user to database. So a foreign key will always fail.

// With Foreign Key
$table->string('user_id')->references('id')->on('users');

// Without Foreign Key
$table->string('user_id');

Or... you can implement a Eloquent Model. Read more.

[philiplb]

Yeah, I'm a bit puzzled that it wants to touch the user_id at all. Or why this session schema from Laravels documentation actually has this field at all.

[philiplb]

It seems to work fine, thx.