Multi-tenancy : Split up Roles and Permissions over Landlord / Tenant database.

[BartMommens]

Hey all,

This question dangles somewhere between Multi-tenancy and Spatie's roles and permissions.

So the idea is to create a multi-tenant application, this application has FIXED roles and permissions for users. And i want them to be "synced" over all tenants. At first i wast thinking about seeding force seeding the new roles permissions on production but that doesn't seem like a smart approach at all.

My second approach would be to create migrations that manually insert roles and permissions, or revoke delete in the up and down of the migration files. This looks like a better approach then seeding, yet the problem might arise for new tenant apps since all the migrations are done in batch 1 so migrate:rollback is a bump on the road.

Third approach would be to place all roles and permissions in the Landlord Database, and keeping those up to date with migrations. But i have no clue how to split up everything and dealing with the foreign keys.

Plan would be in the following

landlord db tables: permissions, roles_has_permissions

tenant db tables: roles

...

I got a little further in Third approach but kinda hit a brick wall here...

So what i did was:

Split up migration files -> one for landlord :

<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreatePermissionTables extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        $tableNames = config('permission.table_names');

        if (empty($tableNames)) {
            throw new \Exception('Error: config/permission.php not loaded. Run [php artisan config:clear] and try again.');
        }

        Schema::create($tableNames['permissions'], function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name',125);       // For MySQL 8.0 use string('name', 125);
            $table->string('guard_name', 125); // For MySQL 8.0 use string('guard_name', 125);
            $table->timestamps();

            $table->unique(['name', 'guard_name']);
        });

        Schema::create($tableNames['roles'], function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name', 125);       // For MySQL 8.0 use string('name', 125);
            $table->string('guard_name', 125); // For MySQL 8.0 use string('guard_name', 125);
            $table->timestamps();

            $table->unique(['name', 'guard_name']);
        });

        Schema::create($tableNames['role_has_permissions'], function (Blueprint $table) use ($tableNames) {
            $table->unsignedBigInteger('permission_id');
            $table->unsignedBigInteger('role_id');

            $table->foreign('permission_id')
                ->references('id')
                ->on($tableNames['permissions'])
                ->onDelete('cascade');

            $table->foreign('role_id')
                ->references('id')
                ->on($tableNames['roles'])
                ->onDelete('cascade');

            $table->primary(['permission_id', 'role_id'], 'role_has_permissions_permission_id_role_id_primary');
        });

        app('cache')
            ->store(config('permission.cache.store') != 'default' ? config('permission.cache.store') : null)
            ->forget(config('permission.cache.key'));
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        $tableNames = config('permission.table_names');

        if (empty($tableNames)) {
            throw new \Exception('Error: config/permission.php not found and defaults could not be merged. Please publish the package configuration before proceeding, or drop the tables manually.');
        }

        Schema::drop($tableNames['role_has_permissions']);
        Schema::drop($tableNames['roles']);
        Schema::drop($tableNames['permissions']);
    }
}

And one for the tenants:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class CreateRolesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {

        $tableNames = config('permission.table_names');
        $columnNames = config('permission.column_names');

        if (empty($tableNames)) {
            throw new \Exception('Error: config/permission.php not loaded. Run [php artisan config:clear] and try again.');
        }
        //GET THE LANDLORD DATABASE NAME HERE...
        $landlord_db_name = DB::connection('landlord')->getDatabaseName();

        Schema::create($tableNames['model_has_permissions'], function (Blueprint $table) use ($tableNames, $columnNames, $landlord_db_name) {
            $table->unsignedBigInteger('permission_id');

            $table->string('model_type');
            $table->unsignedBigInteger($columnNames['model_morph_key']);
            $table->index([$columnNames['model_morph_key'], 'model_type'], 'model_has_permissions_model_id_model_type_index');

            $table->foreign('permission_id')
                ->references('id')
                ->on($landlord_db_name.'.'.$tableNames['permissions']) //SET FK TO LANDLORD DB
                ->onDelete('cascade');

            $table->primary(['permission_id', $columnNames['model_morph_key'], 'model_type'],
                'model_has_permissions_permission_model_type_primary');
        });

        Schema::create($tableNames['model_has_roles'], function (Blueprint $table) use ($tableNames, $columnNames, $landlord_db_name) {
            $table->unsignedBigInteger('role_id');

            $table->string('model_type');
            $table->unsignedBigInteger($columnNames['model_morph_key']);
            $table->index([$columnNames['model_morph_key'], 'model_type'], 'model_has_roles_model_id_model_type_index');

            $table->foreign('role_id')
                ->references('id')
                ->on($landlord_db_name.'.'.$tableNames['roles']) //SET FK TO LANDLORD DB
                ->onDelete('cascade');

            $table->primary(['role_id', $columnNames['model_morph_key'], 'model_type'],
                'model_has_roles_role_model_type_primary');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        $tableNames = config('permission.table_names');

        if (empty($tableNames)) {
            throw new \Exception('Error: config/permission.php not found and defaults could not be merged. Please publish the package configuration before proceeding, or drop the tables manually.');
        }

        Schema::drop($tableNames['model_has_roles']);
        Schema::drop($tableNames['model_has_permissions']);
    }
}

Migration was a great success, added some roles and permissions in landlord database, and added role to a user in the tenant. The relationship is a success and works.

Landlord tables:

Tenant Tables:

Relations:

Now i've extended the Spatie's Role and Permission model in order to use landlord connection:

TenantRole :

namespace App\Models;

use Spatie\Multitenancy\Models\Concerns\UsesLandlordConnection;
use Spatie\Permission\Models\Role;

class TenantRole extends Role
{
    use UsesLandlordConnection;

}

TenantPermission :

namespace App\Models;

use Illuminate\Database\Eloquent\Relations\BelongsToMany;
use Spatie\Multitenancy\Models\Concerns\UsesLandlordConnection;
use Spatie\Multitenancy\Models\Tenant;
use Spatie\Permission\Models\Permission;

class TenantPermission extends Permission
{
   use UsesLandlordConnection;
}

Oke everything booted, but i had an error on the permissions guard, DAMN!

I disabled cache for testing purpose ... nothing... alright then moving on...

In my view file i tried to check permissions for the logged in user:

 {{Auth::User()->getPermissionNames()}}

And here is where it all broke down :(

This is the error i got:

It looks like he's looking for the model_has_permissions table in the landlord database instead of the tenant database, if i remove the use UsesLandlordConnection; from the models it's even deader than dead and he's now looking in the Tenant DB.

So anyone know how i can separate those two things ? since i only have 2 models to work with form spatie package : Role & Permission.

It works like 80% but that extra 20% is gonna whomp me into oblivion isn't it ?

Or should i find a different approach and abandon this road?

Anyway thanks for your feedback

Originally posted by @BartMommens in https://github.com/spatie/laravel-multitenancy/discussions/244

[BartMommens]

I've found a solution to make this work with a special thanks to @masterix21, who pointed me in the correct direction. And has been a great help on the Spatie Multi-tenancy package.

config\permission.php

'models' => [
    'permission' => App\Models\TenantPermission::class,
    'role' => App\Models\TenantRole::class,
],

New Role model,

App\Models\TenantRole.php:

use Spatie\Multitenancy\Models\Concerns\UsesLandlordConnection;
use Spatie\Permission\Models\Role as BaseRole;

class TenantRole extends BaseRole
{
    use UsesLandlordConnection;

}

New Permission model

App\Models\TenantPermission.php:

namespace App\Models;

use Spatie\Multitenancy\Models\Concerns\UsesLandlordConnection;
use Spatie\Permission\Models\Permission as BasePermission;

class TenantPermission extends BasePermission
{
    use UsesLandlordConnection;
}

App\Models\User.php :

use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;
use Laravel\Sanctum\HasApiTokens;
use Spatie\Multitenancy\Models\Concerns\UsesTenantConnection;
use Spatie\Multitenancy\Models\Tenant;
use Spatie\Permission\Traits\HasPermissions;
use Spatie\Permission\Traits\HasRoles;

class User extends Authenticatable implements MustVerifyEmail
{
    use UsesTenantConnection, HasRoles;
    /**
     * A model may have multiple roles.
     */
    public function roles (): BelongsToMany {
        return $this->belongsToMany(
            config('permission.models.role'), //This model uses the LandlordConnection
            Tenant::current()->getDatabaseName().'.'.config('permission.table_names.model_has_roles'),  //Just inserted the tenant DB name here
            'role_id',
            'model_id'
        );
    }

    /**
     * A model may have multiple direct permissions.
     */
    public function permissions(): BelongsToMany
    {
        return $this->morphToMany(
            config('permission.models.permission'),
            'model',
            Tenant::current()->getDatabaseName().'.'.config('permission.table_names.model_has_permissions'),
            config('permission.column_names.model_morph_key'),
            'permission_id'
        );
    }

}

I hope other people can use this method and implement it

Originally posted by @BartMommens in https://github.com/spatie/laravel-multitenancy/discussions/244#discussioncomment-767957