Single db with separate schema per tenant possible?

[adampotocki]

I'm exploring a use case for path based tenancy on same domain/subdomain with a single postgres db with a landlord schema for users/roles/permissions and separate db schema per tenant for model tables.

How would this be done using spatie\laravel-multitenancy? Any issues with SSO and session management? Your experience positive/negatives would be greatly appreciated.

[stefano-vergani]

Hi @adampotocki i'm tryin' to do something like that now, and i'm struggling around the switching schema function instead switching database.

My database config for tenant has search path set to null

        'tenant' => [
            'driver'         => 'pgsql',
            'url'            => env('DB_URL'),
            'host'           => env('DB_HOST', '127.0.0.1'),
            'port'           => env('DB_PORT', '5432'),
            'database'       => env('DB_DATABASE', 'laravel'),
            'username'       => env('DB_USERNAME', 'root'),
            'password'       => env('DB_PASSWORD', ''),
            'charset'        => env('DB_CHARSET', 'utf8'),
            'prefix'         => '',
            'prefix_indexes' => true,
            'search_path'    => null,
            'sslmode'        => 'prefer',
        ],

My database config for landlord has search path set to public

        'landlord' => [
            'driver'         => 'pgsql',
            'url'            => env('DB_URL'),
            'host'           => env('DB_HOST', '127.0.0.1'),
            'port'           => env('DB_PORT', '5432'),
            'database'       => env('DB_DATABASE', 'laravel'),
            'username'       => env('DB_USERNAME', 'root'),
            'password'       => env('DB_PASSWORD', ''),
            'charset'        => env('DB_CHARSET', 'utf8'),
            'prefix'         => '',
            'prefix_indexes' => true,
            'search_path'    => 'public',
            'sslmode'        => 'prefer',
        ],

my default config is set to landlord in .env file and in multitenancy config i set:

    [....]
    'tenant_finder'                      => KindeTenantFinder::class,

    'switch_tenant_tasks'                => [
        \App\Actions\Tenants\SwitchTenantPgSchemaTask::class,
        // \Spatie\Multitenancy\Tasks\PrefixCacheTask::class,
        // \Spatie\Multitenancy\Tasks\SwitchTenantDatabaseTask::class,
        // \Spatie\Multitenancy\Tasks\SwitchRouteCacheTask::class,
    ],

    'tenant_model'                       => App\Models\Tenant::class,
    [....]

i'm using Kinde for spa authentication so I don't need session nor domain to find the current tenant, i switch it by validate and parse the jwt token sent me from the front end application.

For now, i have some issues with SwitchTenantPgSchemaTask::class

        $tenantConnectionName = $this->tenantDatabaseConnectionName();

        if (is_null(config("database.connections.{$tenantConnectionName}"))) {
            throw InvalidConfiguration::tenantConnectionDoesNotExist($tenantConnectionName);
        }

        $schema = $schema ?: 'public';

        if ($schema === '') {
            throw new RuntimeException("Empty schema for $tenantConnectionName");
        }

        config(["database.connections.{$tenantConnectionName}.search_path" => $schema]);

        DB::purge($tenantConnectionName);
        DB::reconnect($tenantConnectionName);

It seems that changing schema is not working somehow. Keep investigating...
Have you resolved?