Multi tenant multi database support, tried all but not working.

[kingashok29]

I tried to extend the models, as well as updating permission config files while switching to tenant database but still when I try to update the role and permissions it does not work and always fetching roles and permissions using default database connection.

<?php

namespace App\Client;

use Spatie\Permission\Models\Role as ModelsRole;

class Role extends ModelsRole
{
    protected $connection = 'client';

    protected $table = 'roles';

    protected $fillable = ['name'];
    
    public function __construct()
    {
        parent::__construct();

        if ($connection = config('permission.db_connection')) {
            $this->setConnection($connection);
        }
    }
}

Permission model

<?php

namespace App\Client;

use Spatie\Permission\Models\Permission as ModelsPermission;

class Permission extends ModelsPermission
{
    protected $connection = 'client';

    protected $table = 'permissions';

    protected $fillable = ['name'];

    public function __construct()
    {
        parent::__construct();

        if ($connection = config('permission.db_connection')) {
            $this->setConnection($connection);
        }
    }
}

Configuration update while switching to tenant connection

'permission.db_connection' => 'client',
'permission.models.permission' => Permission::class,
 'permission.models.role' => Role::class,

Role and permission seeder

<?php

use App\Client\Permission;
use App\Client\Role;
use Illuminate\Database\Seeder;

class ClientRoleAndPermissionSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        //add client permissions.
        $client_permissions = [
            'manage properties',
            'manage agents',
        ];

        foreach($client_permissions as $p)
        {
            Permission::firstOrCreate([
                'name' => $p,
                'guard_name' => 'client'
            ]);
        }

        //add client roles and assign general permissions to get start.
        Role::firstOrCreate([
            'name' => 'account manager',
            'guard_name' => 'client'
        ])->givePermissionTo([ 'manage agents']);

    }
}

Now when I try to update permission, I receive an error that permission does not exist, It means permissions are still being fetched from the main database.

public function updatePermissions(Request $request)
    {
        $this->validate($request, [
            'role' => 'required|numeric',
        ]);

        $role = Role::findById($request->role);

        return $role->givePermissionTo('manage properties');

        flash()->success('Permissions updated for the <b> ' . $role->name . '</b>');
        return back();
    }

[rlvillacarlos]

Hi, you mentioned me from the issue I posted before. I would like to be honest with you that I am new to this package as well. I am not sure if the issue has something to do with this package or Eloquent.

Why do you need to set-up the connection in your model constructor? I see you have already set the $connection property of your model and I believe that is enough to change the connection being used by the model.

Have you defined this client connection in your db configuration? Did you create a connection configuration named client in config/database.php?

[kingashok29]

Hi, you mentioned me from the issue I posted before. I would like to be honest with you that I am new to this package as well. I am not sure if the issue has something to do with this package or Eloquent.

Why do you need to set-up the connection in your model constructor? I see you have already set the $connection property of your model and I believe that is enough to change the connection being used by the model.

Have you defined this client connection in your db configuration? Did you create a connection configuration named client in config/database.php?

Yes, I have a connection named 'client' in my DB configuration file. The reason of adding $connection in constructor was that even after adding connection property on Model, still the roles and properties were being fetched from the default MySQL connection.

[drbyte]

Sounds like your config/permission.php file is still pointing to the package models, not your App models.

[rlvillacarlos]

@drbyte , is it possible to have multiple settings for this package on the same laravel app?

Anyway, I think, he is not trying to change the tables used by Role and Permission. If I understand it correctly, he has the same tables in two different databases, that is why he is using different connections for admin and client. So in that case the setting in config/permission.php will stay the same for both connections.

Have you tried using the standard Model methods for assigning relations? Since the Role and Permission are just Eloquent Models, maybe you should try the Model methods first just to see if the client connection works, if it does (which I think should work) then possibly its the method in the package. I have not checked the codes for this package for I have no time. So maybe you can check it yourself once you confirmed that everything works as expected using the standard Model methods.

[drbyte]

is it possible to have multiple settings for this package on the same laravel app?

Yes.
Multi-tenancy introduces a number of complications. Spatie and others have written posts and released packages that help simplify the complexities of that. You should study those concepts if you intend to do multi-tenancy in your apps.

One of those complications is switching out the config to alternate configs when you switch tenants. This is most often best done by observing an event that you fire when switching to a specific tenant. That's the basic fundamental to accommodate; again, read up on the concepts before implementing them, and if using a package be sure to study how that package implements it before using it.

Anyway, I think, he is not trying to change the tables used by Role and Permission. If I understand it correctly, he has the same tables in two different databases, that is why he is using different connections for admin and client. So in that case the setting in config/permission.php will stay the same for both connections.

No, you must switch the config during runtime. This package (like most) only knows ONE configuration at any time. But when you decide that you're in a specific tenant that's when you must switch the config to use the config that suits the tenant.
In this case he's not switching table names, but rather model names.

[rlvillacarlos]

@drbyte I got your point more clearly now. The config/permission.php contains the configuration for setting the Permission class and Role class to be used by the app. As I am new to this package, I have not checked this and did not changed any of the configurations and I simply used whatever is default.

I believe the getRoleClass() of the HasRoles trait has been the reason why, even if you use your own Role class, the package still uses the default Role class. I did not fully traced everything but the package uses a PermissionRegistrar class for loading the role class to use based on whatever is specified in the package configuration.

So as expected, @drbyte's solution of loading the tenant configuration will solve your problem. I think, although not 100% sure, you just need to create a config for the client where you define Role and Permission class to use. Although, I do not know how to load the tenant specific config during runtime .

[kingashok29]

@rlvillacarlos I used tinker to debug the issue mentioned by you. When I start tinker and after switching to any customer database and then if I try to access the value of Role and Permission model from permission configuration file, I see that the role and permission model are switched as well.

[kingashok29]

@drbyte I have one unique column company id in customers table on main database. Customer need to enter the company ID as first step to login, if provided company ID exist in the main database, application will connect to the customer database and then after filling the email and password customer session will start, right after login I switch the Role and Permission models in permission configuration file and other things like configuring requests and queues etc.

[drbyte]

If you're not also switching the caching system (or at least the prefix) used by the tenant when switching tenants, you may need to also call forgetCachedPermissions() on the user or the PermissionsRegistrar when you switch, especially if you've previously checked any permissions before making the switch.

[kingashok29]

@drbyte Yes, right after new tenant login, in authenticated method, I am doing that as well.

    /**
     * The user has been authenticated.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  mixed  $user
     * @return mixed
     */
    protected function authenticated(Request $request, $user)
    {
        $company = Company::with('plan')->first();

        if($user->blocked)
        {
            Auth::guard('client')->logout();
            flash()->error('You are blocked to access account. Reason - <b> ' .$user->block_reason);
            return back();
        }

        // Reset cached roles and permissions
        app()[\Spatie\Permission\PermissionRegistrar::class]->forgetCachedPermissions();

        flash()->success('Welcome back.');
    }

I used Debugbar to look for the database queries, and as you can see permissions are still being fetched from the main database and that's why when I try to assign permission, I receive error that permission does not exist. Which of course

does not exist on main database instead exist in client database.

[drbyte]

Let's build a new app to recreate the problem. Starting from a fresh Laravel install.
What are all the steps required to duplicate just this situation? Nothing more than what's needed to demonstrate this problem.

[kingashok29]

@drbyte Okay, these are the steps to recreate this problem only.

• Fresh install of Laravel.
• Update the user's migration table, add a column to store the client_id, on new client registration, fill this field with a random numeric value.
• Create admins migration table, seed some admins, migrate permissions table, and seed some role and permissions as well. (admin guard)
• After client registration create new database for the client, like client_{client_id}, migrate the role and permissions table in the client database, seed some role, and permissions. (I am using the custom command to do all these tasks in a queued job right after new account registration)
• Create new users table in the client database with fields like name, email, password. Seed some entries in this table with random roles. (client guard)
• Login to the client account, you can use client_id in route and then connect to the client database, and switching of Role and Permission model in permission configuration.
• After login try to assign permissions to the client (Here you will see an error that permission with guard 'client' does not exist because permissions are still being fetched from the main database.)

[drbyte]

Thanks for the list.
I don't have time today to go through and build that app myself. Perhaps you can build it (separating each step with separate atomic commits that can be understood individually) and push it to a github repo so we can inspect it together?

Also, I think there are some things missing in your list, such as all the exact steps you're using to switch connections and configurations.

In short: the problem is in the code. But to identify where, we need to have the code to look at.

[kingashok29]

@drbyte I have solved this by updating the permission config file. I have removed default modals and added new Role and Permission modal with connection property set to the client means these modal will fetch permissions and roles from the client database.

But again if I go back to the main admin dashboard which is using default database connection, in this case roles and permissions are not being updating and showing error that "permission not found". I have added EnforceMainDatabaseOnPermissions middleware, which will update the config file and swap the default modals back and set db_connection property to 'default'.

So basically if the main database permissions are working then client permissions will not work and vice versa. I have also tried to dd config values and they are as expected.

I don't know why after switching modals and db_connection property the permissions are being fetched from wrong database.

[kingashok29]

My solution.

Using Bouncer package to manage roles and abilities on the main connection for the Admin side.
Using this package to manage roles and permissions on the tenant side, by switching the tenant connections dynamically.

Now everything is fine.