PostgreSQL RLS

// todo installation section

RLS (row-level security) is a feature of PostgreSQL that lets you enable global “filters” on specified tables.

For instance, it can automatically scope tables with a tenant_id column to the current tenant (specified e.g. using a session variable).

In the context of multi-tenancy, it essentially lets you have all data in a single database, but use your app as if it were a multi-database application. In other words, you don’t have to think about the scoping on the application level because it is enforced on the database level.

How it works

1. Tenancy creates a new user, using the credentials provided in the rls.user config. This user will have the RLS policy enabled, meaning scoping will take place while you’re connected to the database as this user. The central user will still be able to query all data,
2. The RLS bootstrapper will switch to this database user when tenancy is initialized, and revert to the central user when tenancy is ended¹,
3. This way, when you’re in the tenant context, scoping will take automatically, at the database level, and when you’re in the central context, you can query your entire database. Just like single-database tenancy, but without having to add traits to your models to do the scoping manually.

The tenant user uses a dedicated tenant connection (that is set as the default connection in Laravel), so you can still access all data, if needed, using your central connection. Tenancy makes this easy using the CentralConnection trait that you can apply on individual models.

There are currently two ways to use RLS — two RLS Managers:

config/tenancy.php

'rls' => [
    /*
    * The RLS manager responsible for creating policies.
    *
    * @see Stancl\Tenancy\RLS\PolicyManagers\TraitRLSManager
    * @see Stancl\Tenancy\RLS\PolicyManagers\TableRLSManager
    */
    'manager' => Stancl\Tenancy\RLS\PolicyManagers\TableRLSManager::class,
],

TraitRLSManager depends on single-database tenancy traits: BelongsToTenant and BelongsToPrimaryModel. It essentially just disables the scopes used in the traits and handles scoping using RLS. It’s essentially identical to just using those traits with single-database tenancy. We’ll cover why it exists in a section below.

TableRLSManager scans your entire database, looks for tables that are related to the tenant (with any level of indirection) and builds a RLS policy scoping the model to the tenant. This is the manager that makes your app feel like multi-db while using a single-db setup.

It does this by traversing every single foreign key, seeing which lead to the tenant, and then picking the shortest path to build the policy with. For instance:

Reaction belongsTo Comment belongsTo Post belongsTo Tenant

becomes:

CREATE POLICY reactions_rls_policy ON reactions USING (
    comment_id IN (
        SELECT id
        FROM comments
        WHERE post_id IN (
            SELECT id
            FROM posts
            WHERE tenant_id = current_setting('my.tenant')
        )
    )
);

That way, you will only be able to access Reactions that belong to the current tenant (via the comment-post-tenant relations) while you’re in the tenant context.

Comparison of RLS managers

TableRLSManager

• Pros:
  • No code changes needed
  • Supports unlimited indirection (i.e. tables that belong to tenants indirectly via an unlimited amount of intermediate tables)
• Cons:
  • Lower-level — you might need to configure some things in migrations, instead of models
• Summary:
  • Makes single-database tenancy work similar to multi-database tenancy
  • Works great when you have “deep” database relationships or when you don’t have control over all models in your application
  • Works with tables, not models

This is the main RLS manager that is essentially the point of this feature existing — it’s very much in line with the spirit of our package of “no code changes needed”, with everything handled in the background for you and your application logic not becoming polluted with manual scoping that you have to constantly think about.

TraitRLSManager

• Pros:
  • Much simpler
  • You can easily choose which models to scope using RLS
• Cons:
  • Requires same code changes as single-database tenancy
  • Supports at most 2 levels of indirection (Bar belongsTo Foo belongsTo Tenant, children of Bar will not be scoped)
• Summary:
  • Works with models, not tables
  • Essentially just an alternative scoping strategy for single-database tenancy

This manager requires that you use single-database tenancy traits. When you use RLS, the global scopes in those traits become inactive so the scoping is done via RLS, not Eloquent global scopes.

The reason why we included this manager is to show an alterantive implementation to the table manager. The class is much simpler so it can be a helpful reference for writing custom RLS managers if that’s something your application would need (if the table manager weren’t a good fit for any reason).

The trait manager is also helpful for migrating to RLS. If you have an application that uses our single-database tenancy traits (BelongsToTenant / BelongsToPrimaryModel), you can switch to this manager and have all your scoping done via RLS. All database scoping should work identically to the single-database tenancy traits. This lets you get used to the setup where you have a tenant connection in your app and a separate database user for connecting to the database from the tenant context.

If everything works fine with this approach and you feel confident using it in production, you should have a much easier time migrating from single-database tenancy to the table manager approach.

Trait manager usage

The usage for the trait manager is the same as for single-database tenancy: use the BelongsToTenant or BelongsToPrimaryModel traits on your Eloquent models.

The TraitRLSManager will scan your app/Models directory and look for models that use these traits.

Implicit RLS

By default, any model that uses the traits mentioned above will be scoped using RLS.

That said, if you’d like to scope only some models using RLS, you can disable implicit scoping:

TenancyServiceProvider.php

TraitRLSManager::$implicitRLS = false;

And use the RLSModel interface to flag models that you do want to scope.

You can also customize where this manager looks for models. By default this is only app/Models.

TenancyServiceProvider.php

TraitRLSManager::$modelDirectories = ['app/Models'];

Or exclude specific models directly:

TenancyServiceProvider.php

TraitRLSManager::$excludedModels = [Post::class];

Table manager usage

The table manager approach is more automatic than the trait manager approach so there’s fewer code changes you need to make. However, being more complex, it’s important have a good understanding of how it works.

Foreign keys, trees, paths

The table manager works like this:

1. It lists all tables in your database
2. For each table, it lists all foreign keys
3. It follows each foreign key to see if it leads to the tenants table (no matter how indirectly related those two tables may be).
4. It builds a tree of all of these paths that lead to the tenants table
5. It then reduces this tree to a list of shortest paths — one path for each table related (again, with any degree of indirection) to the tenant.
6. For these shortest paths, it builds RLS policies.

To give an example:

reactions
- type        string
- comment_id  foreign key referencing `comments.id`
- author_id   foreign key referencing `authors.id`

comments
- text        string
- post_id     foreign key referencing `posts.id`
- author_id   foreign key referencing `authors.id`

posts
- text        string
- author_id   foreign key referencing `authors.id`
- tenant_id   foreign key referencing `tenants.id`

authors
- name        string
- tenant_id   foreign key referencing `tenants.id`

This would be a fairly common setup where you’d have something like a forum or blog platform where each tenant has its own content (posts, comments) and users (authors).

In this example, the following paths are available (this would be the full tree):

• reactions
  • reactions -> comments -> posts -> authors -> tenants
  • reactions -> comments -> posts -> tenants
  • reactions -> comments -> authors -> tenants
  • reactions -> authors -> tenants (shortest path)
• comments — two paths of equal distance here, either one can be used
  • comments -> posts -> authors -> tenants
  • comments -> posts -> tenants (shortest path)
  • comments -> authors -> tenants (shortest path)
• posts
  • posts -> authors -> tenants
  • posts -> tenants (shortest path)
• authors
  • authors -> tenants (shortest — only — path)

After generating this full tree and reducing it to the shortest paths, the TableRLSManager would then generate RLS policies for these paths, like the one you saw in the How it works section.

Opt-in / opt-out

By default, the TableRLSManager will include any foreign keys leading to the tenants table in its trees.

To exclude (opt-out) a path, add a no-rls comment to any foreign key column:

$table->foreignId('comment_id')
    ->comment('no-rls')
    ->constrained('comments')
    ->onUpdate('cascade')
    ->onDelete('cascade');

If you’d prefer to use an opt-in approach, set the scopeByDefault static property on TableRLSManager to false:

TenancyServiceProvider.php

TableRLSManager::$scopeByDefault = true;

Then you can enable RLS on specific foreign keys by including a 'rls' comment:

->comment('rls')

Nullable paths

The table manager also takes into consideration that foreign keys may be nullable.

It works like this:

1. If there is only a nullable path available, it will be scoped using RLS. Meaning, you won’t be able to access rows where the column is set to null in the tenant context. In the central context, you will be able to access all rows.
2. If there are multiple paths available, the manager will choose a non-nullable path even if it’s longer.

Recursive relationships

WIP

Currently an exception is always thrown when a recursive path is encountered. This will be fixed to match the behavior described below.

If the table manager encounters a recursive relationship when generating a path, for instance:

• comments.post_id referencing posts.id
• posts.highlighted_comment referencing comments.id

it will throw RecursiveRelationshipException unless another path is available.

To solve this issue, simply introduce another path. The easiest way to do this is to add a tenant_id column directly referencing the tenants table.

Automatically filling tenant relationships

The TraitRLSManager requires that you use the BelongsToTenant trait which automatically fills the tenant relationship with the current tenant.

To achieve the same with the TableRLSManager approach, use the FillsCurrentTenant trait:

app/Models/Post.php

class Post extends Model
{
    use FillsCurrentTenant;
}

Optimizing query speed

As you may have noticed from all of the above, there are cases when RLS policies can become very indirect and will have to include a lot of WHERE clauses referencing multiple tables. This can lead to inefficient queries.

To solve this, either add indices (where it makes sense), or shorten the paths by adding more foreign keys that produce shorter paths. As mentioned in the section above, the easiest way to do this is to simply add a tenant_id foreign key to tables that are too-indirectly related to the tenant.

Configuration

The tenancy.rls config lets you choose which RLS manager you want to use, the credentials for the RLS user (you need to set these, either in the config or via the default environment variables), and the name of the session variable you want to use to keep track of the current tenant.

config/tenancy.php

/**
 * The RLS manager responsible for generating queries for creating policies.
 *
 * @see Stancl\Tenancy\RLS\PolicyManagers\TableRLSManager
 * @see Stancl\Tenancy\RLS\PolicyManagers\TraitRLSManager
 */
'manager' => Stancl\Tenancy\RLS\PolicyManagers\TableRLSManager::class,

/**
 * Credentials for the tenant database user (one user for *all* tenants, not for each tenant).
 */
'user' => [
    'username' => env('TENANCY_RLS_USERNAME'),
    'password' => env('TENANCY_RLS_PASSWORD'),
],

/**
 * Postgres session variable used to store the current tenant key.
 *
 * The variable name has to include a namespace – for example, 'my.'.
 * The namespace is required because the global one is reserved for the server configuration
 */
'session_variable_name' => 'my.current_tenant',

Creating a user with RLS policies

After you’ve set up your models or tables and configured the right RLS manager, use the tenants:rls command to create a database user with the RLS policies generated by the configured manager:

php artisan tenants:rls

This command needs to be executed after each schema change (e.g. migrations), or in the case of the TraitRLSManager after you’ve added/removed the relevant traits in any models or customized which models are discovered by the manager.

In deployment scripts, you should always run this command after php artisan migrate.

Enabling the bootstrapper

After configuring the manager and creating the RLS user, the only remaining step is to enable the bootstrapper:

config/tenancy.php

'bootstrappers' => [
    Bootstrappers\PostgresRLSBootstrapper::class,
],

This bootstrapper will create the tenant connection and set it as the default connection — same as the DatabaseTenancyBootstrapper — except it will point at your central schema. The only difference is that it will be connected via the user created using the tenants:rls command, and the session variable will be set.

Migrations

Once RLS policies are created for a table, it’s impossible to modify it until the policy is dropped.

To solve this, you may use the tenancy()->dropRLSPolicies() helper in your migrations:

add_foo_column_to_users_table.php

public function up(): void
{
    tenancy()->dropRLSPolicies('comments');

    Schema::table('users', function (Blueprint $table) {
        $table->string('foo');
    });
}

And run tenants:rls immediately after running migrations to re-create these policies.

Since migrations can fail, you should make sure tenants:rls always executes after php artisan migrate no matter what exit code that command returns. Alternatively, if a failing command halts your entire deployment script, use Laravel’s maintenance mode so that the application remains in maintenance mode if tenants:rls didn’t execute.

Deployment script

php artisan down
php artisan migrate
php artisan tenants:rls
php artisan up

Policy hashing

The tenants:rls command calls the generateQueries() method on the configured RLS manager. After these queries are generated, it updates each query’s name to include a hash of the policy body².

This is used as a simple approach to versioning the policies — the command knows to create policies that don’t exist yet, but thanks to hashing it can also drop and re-create any policies that are outdated.

Accessing central data

As mentioned at the start of this page, the bootstrapper creates a new connection (tenant), so you can still use the central connection to access central data.

The easiest way to do this is to apply the CentralConnection trait on a model. That will make Eloquent use the central connection instead of the default connection.

For raw DB queries, you may use DB::connection(...).

Footnotes

1. The session variable is also set (on bootstrap) and reset (on revert), thus rendering the connection mostly unusable when tenancy is not initialized. This is helpful in case the connection gets accidentally injected somewhere — accessing data from the wrong tenant is prevented. ↩

2. The hash used is substr(sha1($policyBody), 0, 6) to be specific. This produces a short but unique enough version identifier. It doesn’t have to be cryptographically secure, and the odds of collisions are basically nonexistent even with this short substring. ↩