PostgreSQL RLS

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 at the application level because it is enforced at the database level.

Read first

By default, Tenancy uses a multi-database setup. You should follow the steps on the Single-database tenancy page (except the Tenant model change section, and the section talking about model traits). Essentially just make sure you disable database-related bootstrappers and remove the CreateDatabase / MigrateDatabase / DeleteDatabase jobs from job pipelines.

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. This is also why the Tenant model still uses the TenantWithDatabase interface even though the tenant doesn’t have an actual separate database: the tenant connection is still used.

Not a security boundary

RLS in Tenancy is primarily a way to move tenant scoping from your application code to the database. It isn’t meant as a security boundary against code running in the tenant context:

• Queries on tables without RLS policies aren’t scoped (see Tables without policies).
• Foreign key actions, such as ON DELETE CASCADE, bypass RLS. For example, if the scoped posts table references a global (unscoped) categories table with ON DELETE CASCADE, deleting a category in the tenant context also deletes other tenants’ posts in that category, even though the current tenant can’t access them.

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. It creates RLS policies for the models using those traits, and lets you turn off the traits’ global scopes so that the scoping is done via RLS, not Eloquent global scopes (see Implicit RLS).

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, enable implicit RLS, 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.

Basic setup

This section covers the basic steps to get PostgreSQL RLS working, though it’s recommended to go over this page in full to get a full understanding of each step.

1. Set up Tenancy by following the Getting Started guide, skipping the steps related to database/migrations/tenant — we won’t be using multiple databases
   1. Composer require the package
   2. Run php artisan tenancy:install
   3. Set up your tenant model just like in the Getting Started guide and configure it in config/tenancy.php
2. In app/Providers/TenancyServiceProvider.php disable (comment out) the CreateDatabase, MigrateDatabase, and DeleteDatabase jobs
3. In config/tenancy.php disable the DatabaseTenancyBootstrapper and DatabaseSessionBootstrapper and enable PostgresRLSBootstrapper (if you use SESSION_DRIVER=database, keep DatabaseSessionBootstrapper enabled and scope the sessions table — see Database session driver with RLS)
4. Configure TENANCY_RLS_USERNAME and TENANCY_RLS_PASSWORD in .env, see the relevant section below.
5. Run php artisan tenants:rls

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

The tenants:rls command creates RLS policies for all models discovered by the trait manager. However, by default, the traits also keep applying their global scopes, so these models are scoped twice — once by Eloquent and once by RLS.

To have a model scoped only using RLS, make it implement the RLSModel interface. The traits then won’t apply their global scopes on that model:

app/Models/Post.php

class Post extends Model implements RLSModel
{
    use BelongsToTenant;
}

Alternatively, if you want all models using the traits to be scoped only using RLS, enable implicit RLS:

TenancyServiceProvider.php

TraitRLSManager::$implicitRLS = true;

With implicit RLS, you don’t need the RLSModel interface at all.

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

TenancyServiceProvider.php

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

Or exclude specific models directly:

TenancyServiceProvider.php

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

Excluded models don’t get an RLS policy, so they keep the traits’ global scopes — even with implicit RLS enabled, or if they implement RLSModel. If you really want an excluded model to be unscoped, use withoutGlobalScope().

Models without RLS policies

Models using the traits that aren’t discovered by the trait manager (models outside $modelDirectories, or not returned by $modelDiscoveryOverride) don’t get an RLS policy. Unlike excluded models, with $implicitRLS enabled or if they implement RLSModel, they also don’t get the global scopes, so they would not be scoped at all. To avoid this, add the directories containing such models to $modelDirectories (or return the models from $modelDiscoveryOverride), or keep implicit RLS disabled and only implement RLSModel on the models that do have an RLS policy.

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 = false;

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

->comment('rls')

Comment constraints

Besides opting into and out of RLS, comments can be used to define constraints instead of foreign keys:

->comment('rls users.id');

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

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. There are ways to achieve the same with TableRLSManager.

One option is to use the FillsCurrentTenant trait:

app/Models/Post.php

class Post extends Model
{
    use FillsCurrentTenant;
}

Alternatively, if this would be too much work in your app, or if you need this automatic filling in models outside your control (e.g. third-party packages), you can use a custom event listener like this:

AppServiceProvider.php

// Place this in the register() method of the provider
Event::listen('eloquent.creating: *', function (string $eventName, array $data) {
    $model = $data[0];

    if (! method_exists($model, 'tenant')) {
        return;
    }

    $tenantRelation = $model->tenant();

    if (! $tenantRelation instanceof BelongsTo) {
        return;
    }

    if ($tenantRelation->getRelated()->getTable() !== 'tenants') {
        return;
    }

    if (! $model->getAttribute(tenancy()->tenantKeyColumn()) && ! $model->relationLoaded('tenant')) {
        if (tenancy()->initialized) {
            $model->setAttribute(tenancy()->tenantKeyColumn(), tenant()->getTenantKey());
            $model->setRelation('tenant', tenant());
        }
    }
});

Another option that’s useful especially for tables that don’t have a model is to make the tenant_id column default to the current tenant’s key (read from the session variable) using an ALTER TABLE statement:

php artisan tinker

$sessionVariable = config('tenancy.rls.session_variable_name');

// current_setting('{$sessionVariable}', true) will return
// null in the central context,
// and the current tenant's key in the tenant context.
DB::statement("ALTER TABLE sessions ALTER COLUMN tenant_id SET DEFAULT current_setting('{$sessionVariable}', true)");

Rows created in the tenant context then get the current tenant’s key automatically.

If you change the tenancy.rls.session_variable_name config, the column will still reference the old session variable name, so you’ll need to ALTER the table again.

For a complete usage example, see Database session driver with RLS.

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.

Permissions

The RLS user is granted full access to all tables in the central schema, except for the TRUNCATE and TRIGGER privileges, which are revoked:

• TRUNCATE isn’t subject to RLS policies, so while Post::query()->delete() in the tenant context only deletes the current tenant’s posts, Post::truncate() would delete the posts of all tenants (and since Laravel truncates with CASCADE on PostgreSQL by default, also every row in tables referencing posts). With the privilege revoked, it fails with a “permission denied” error instead.
• TRIGGER would let the RLS user attach triggers to tables, which would then run with the privileges of whoever modifies the table (e.g. the central user).

If you do need to truncate a table, use the central connection:

DB::connection('central')->table('posts')->truncate();

Or call truncate() on a model that uses the CentralConnection trait.

These privileges are granted (and revoked) only for tables that exist when tenants:rls runs — another reason to run the command after each migration.

Tables that stop being scoped

When a table stops being scoped (e.g. its foreign key’s comment is changed to no-rls, TableRLSManager::$scopeByDefault is changed, or a model’s RLS trait is removed), tenants:rls drops its leftover policy and disables RLS on the table. This works both when the old policy still exists, and when it was already dropped in a migration using dropRLSPolicies().

This matters because a table with RLS enabled and no policies denies all rows to the RLS user.

Specifically, tenants:rls disables RLS on tables in the current schema that have RLS enabled, have no policies at all, and aren’t scoped by the configured RLS manager. Tables that still have other policies (e.g. ones you’ve created yourself) are left alone.

If you have tables that have RLS enabled without any policies on purpose (e.g. to block the RLS user from accessing them), you can turn this off:

TenancyServiceProvider.php

CreateUserWithRLSPolicies::$disableRlsOnTablesWithoutPolicies = false;

Forced RLS and BYPASSRLS

By default, tenants:rls both enables and forces RLS on each scoped table:

ALTER TABLE {table} ENABLE ROW LEVEL SECURITY;
ALTER TABLE {table} FORCE ROW LEVEL SECURITY;

In PostgreSQL, a table’s owner (and any superuser) normally bypasses its RLS policies. FORCE ROW LEVEL SECURITY changes that — with it, even the owner is affected by the policy, unless that user has the BYPASSRLS option.

In a typical setup, the database user on the tenant connection is a separate, non-owner user, so it’s always scoped — that’s what does the actual tenant scoping — and the central user owns the tables (it runs your migrations) and is expected to have BYPASSRLS, so it can still read all data. In most local setups the default postgres superuser has this option by default.

Requiring BYPASSRLS (forcing RLS) is just a strict default in Tenancy to make it explicit when a user should be able to read all data in scoped tables — by being granted the BYPASSRLS option.

Forcing is controlled by the CreateUserWithRLSPolicies::$forceRls property, which is true by default.

A table’s forced (or not forced) state is only applied when its policy is created or recreated. Tables whose policies are already up to date keep their previous state, so after changing $forceRls, run the command with --force to recreate all policies:

php artisan tenants:rls --force

When to disable forced RLS

Some setups can’t grant the central user BYPASSRLS — for example Laravel Cloud doesn’t let you create a user with it. With forcing on, such a central user is scoped like any other user. Since no tenant is set in the central context, the policy’s current_setting('my.current_tenant') is undefined and any query touching a scoped table fails with unrecognized configuration parameter "my.current_tenant".

In those setups, you can disable forcing RLS:

TenancyServiceProvider.php

CreateUserWithRLSPolicies::$forceRls = false;

RLS is then enabled on tables without being forced, so table owners bypass RLS automatically — the central user (used for creating the tables in migrations, therefore also the table owner) can read everything, while the non-owner RLS user on the tenant connection stays scoped.

Sessions table

A common place where this error can come up (if you have enabled forceRls but your central user doesn’t have the BYPASSRLS option) is the sessions table, if you use the database session driver, since sessions would be used in both the central context and the tenant context. Besides the steps written above, there are a few more things you need to do to make the database session driver work correctly with RLS. The necessary steps are described here: Database session driver with RLS.

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

PostgreSQL doesn’t let you change the type of, or drop, a column that’s referenced by an RLS policy until the policy is dropped. Other changes work with the policy in place — e.g. adding columns, changing only a column’s nullability (ALTER COLUMN ... DROP NOT NULL), or renaming a column (the policy references the column itself rather than its name, so it keeps working, and tenants:rls then recreates it with an updated hash).

However, Laravel’s ->change() always re-specifies the column type (ALTER COLUMN ... TYPE ...), even if you only change e.g. the comment or nullability, so it counts as a type change. For example, changing a foreign key column’s comment from rls to no-rls using ->change() requires dropping the policy first.

To drop a table’s policies, use the tenancy()->dropRLSPolicies() helper in your migration before changing or dropping the column:

make_author_id_nullable_on_reactions_table.php

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

    Schema::table('reactions', function (Blueprint $table) {
        $table->foreignId('author_id')->nullable()->change();
    });
}

Also keep in mind that a column can be referenced by policies of other tables. With indirect scoping, a table’s policy uses a subquery on the parent table. In the example above, the policy of reactions (scoped via reactions -> authors -> tenants) references authors.tenant_id. So changing authors.tenant_id using ->change() requires dropping the policies of authors, reactions, and any other tables scoped via authors, otherwise the ALTER statement still fails.

After running migrations, run tenants:rls immediately to recreate the dropped policies (the command recreates every policy that’s missing).

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(...).

Tables without policies

Tables that don’t have RLS policies are fully accessible (both read and write) from the tenant context too. This is intended, and is equivalent to global models in single-database tenancy. It includes:

• tables not related to the tenants table,
• tables excluded using no-rls comments (or not opted into using rls comments when TableRLSManager::$scopeByDefault is false),
• models excluded using TraitRLSManager::$excludedModels,
• the tenants, domains, and tenant_user_impersonation_tokens tables.

The Tenant, Domain, and ImpersonationToken models use the CentralConnection trait, but raw queries on the tenant connection can still read and write these tables. Using CentralConnection (or DB::connection('central')) for central data is still recommended, since it makes it explicit that the query isn’t meant to be tenant-scoped.

Foreign key actions triggered by changes to these tables bypass RLS, so they can affect rows of all tenants — see Not a security boundary.

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. ↩