Multi-tenancy : Fixed Roles and Permissions

[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

But the problem is the Roles foreign key, i have no idea how to separate these over two databases and letting them work together. Still kinda stuck in the concept thinking phase . And i'm fairly new to Laravel as well, coming from codeigniter 3 :)

Any advice, tips about approach and how to set it up? My preference would be option 3 but not sure if it's a valid approach.

Thanks in advance for your feedback and insights

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 :

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.

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 ?

Anyway thanks for your feedback

[masterix21]

If all tenants use the same roles and permissions, my suggestion is to change only the relations with the pivot table.

1. Create roles and permissions tables in the landlord database
2. Create model_has_roles and model_has_permissions pivot tables in each tenant database
3. Create the Pivot models ModelHasRole and ModelHasPermission that use the UsesTenantConnection trait.

In models that uses roles and permissions, override the roles() method like so (repeat for permissions():

public function roles()
{
    public function roles(): BelongsToMany
    {
        return $this->morphToMany(
            config('permission.models.role'),
            'model',
            config('permission.table_names.model_has_roles'),
            config('permission.column_names.model_morph_key'),
            'role_id'
        )
        ->using(ModelHasRole::class);
    }
}

I haven't tried the code, but it should work.

[BartMommens]

@masterix21 Thank you for your reply, i'm not getting any more errors but zero results.

In my Migration file to the tenants i've set the FK Database to the landlord database:

    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 DATABASE NAME OF LANDLORD
        $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 DATABASE NAME OF LANDLORD
                ->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 DATABASE NAME OF LANDLORD
                ->onDelete('cascade');

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

Without adding the db the migration fails.

Adjusted the config

config/permission.php:

  'models' => [
    'permission' => Spatie\Permission\Models\Permission::class,
    'role' => Spatie\Permission\Models\Role::class,
 ],
 
'table_names' => [
    'roles' => 'tenant_roles',  //-->Lives in Landlord DB
    'permissions' => 'tenant_permissions', //-->Lives in Landlord DB
    'model_has_permissions' => 'model_has_permissions', //-->Lives in Tenant DB
    'model_has_roles' => 'model_has_roles', //-->Lives in Tenant DB
    'role_has_permissions' => 'tenant_role_has_permissions',  //-->Lives in Landlord DB
],

The Pivot model ModelHasRole used here has the UsesTenantConnection Trait:

namespace App\Models\Pivot;

use Illuminate\Database\Eloquent\Relations\Pivot;
use Spatie\Multitenancy\Models\Concerns\UsesTenantConnection;

class ModelHasRole extends Pivot
{
    use UsesTenantConnection;
}

The Pivot model ModelHasPermission is Identical to the ModelHasRole.

And Added the pivots in my User model

App\Models\User.php:

namespace App\Models;

use App\Models\Pivot\ModelHasPermission;
use App\Models\Pivot\ModelHasRole;
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\Permission\Traits\HasRoles;

class User extends Authenticatable implements MustVerifyEmail
{
    use HasFactory, Notifiable, HasApiTokens, UsesTenantConnection, HasRoles;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'first_name',
        'last_name',
        'email',
        'password',
    ];

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
        'password',
        'remember_token',
    ];


    /**
     * A permission can be applied to roles.
     */

        public function roles(): BelongsToMany
                {
                  return $this->morphToMany(
                config('permission.models.role'),
                'model',
                config('permission.table_names.model_has_roles'),
                config('permission.column_names.model_morph_key'),
                'role_id'
            )->using(ModelHasRole::class);
        }
    
}

Calling function (in blade file):

{{ dd(\Illuminate\Support\Facades\Auth::user()->roles) }}

Result

[masterix21]

Do you have attached a role or permission to the user?

[BartMommens]

I think so HasRoles, HasPermissions

and in db it's linked :)

[BartMommens]

I'm losing my mind i guess...

i've tried to update the function roles

 public function roles(): BelongsToMany
            {
                return $this->belongsToMany(
                    config('permission.models.role'),
                    'model_has_roles',
                    'role_id',
                    config('permission.column_names.model_morph_key'),
                    'role_id'
                )->using(ModelHasRole::class);
            }

and i see the following output :

user seems to be related :

Roles is empty and in connection it says tenant, table null:

*brain meltdown imminent :)

[BartMommens]

Sorry for the trouble here , I'm new to Laravel started a few months ago, i'm used to writing core PHP it's all confusing at this point :)

[BartMommens]

In the beginning i expected it to be fairly easy to make it work by just changing the migration files and UsesLandlordConnection to custom models. This would make it a very powerful setup in combination with Spatie's multi tenancy. It would allow you to sync roles between all tenants in a few clicks or migrations 🙃

[BartMommens]

@masterix21 When i write my queries manually in Sequel PRO i get results:

I think it's somekind of Laravel issue on belongstomany:

What ever i throw at it it keeps on looking in the current connection:

fs_rp_mt_tenant_01.tenant_roles

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'fs_rp_mt_tenant_01.tenant_roles' doesn't exist (SQL: select tenant_roles.*, model_has_roles.role_id as pivot_role_id, model_has_roles.model_id as pivot_model_id from tenant_roles inner join model_has_roles on tenant_roles.id = model_has_roles.model_id where model_has_roles.role_id = 1

[BartMommens]

Update : I made some progress and now i'm able to get the roles related to the user by doing the following:

config\permission.php

'models' => [
    '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;

}

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'
        );
    }

}

Now i'm gonna check what i can get to work with this setup. I got a feeling that the Cache system is going to be an issue for the tenants ... not sure yet

[BartMommens]

@masterix21 Once again thank you for pointing me towards the correct path, it was a struggle but i learned alot about the inner workings of the Permission package.