With Drizzle, you can enable Row-Level Security (RLS) for any Postgres table, create policies with various options, and define and manage the roles those policies apply to.
Drizzle supports a raw representation of Postgres policies and roles that can be used in any way you want. This works with popular Postgres database providers such as Neon and Supabase.
In Drizzle, we have specific predefined RLS roles and functions for RLS with both database providers, but you can also define your own logic.
Enable RLS
If you just want to enable RLS on a table without adding policies, you can use .enableRLS()
As mentioned in the PostgreSQL documentation:
If no policy exists for the table, a default-deny policy is used, meaning that no rows are visible or can be modified.
Operations that apply to the whole table, such as TRUNCATE and REFERENCES, are not subject to row security.
important
If you add a policy to a table, RLS will be enabled automatically. So, there’s no need to explicitly enable RLS when adding policies to a table.
Roles
Currently, Drizzle supports defining roles with a few different options, as shown below. Support for more options will be added in a future release.
If a role already exists in your database, and you don’t want drizzle-kit to ‘see’ it or include it in migrations, you can mark the role as existing.
Policies
To fully leverage RLS, you can define policies within a Drizzle table.
info
In PostgreSQL, policies should be linked to an existing table. Since policies are always associated with a specific table, we decided that policy definitions should be defined as a parameter of pgTable
Example of pgPolicy with all available properties
Policy options
as
Possible values are permissive or restrictive
to
Specifies the role to which the policy applies. Possible values include public, current_role, current_user, session_user, or any other role name as a string. You can also reference a pgRole object.
for
Defines the commands this policy will be applied to. Possible values are all, select, insert, update, delete.
using
The SQL statement that will be applied to the USING part of the policy creation statement.
withCheck
An SQL statement that will be applied to the WITH CHECK part of the policy creation statement.
Link Policy to an existing table
There are situations where you need to link a policy to an existing table in your database.
The most common use case is with database providers like Neon or Supabase, where you need to add a policy
to their existing tables. In this case, you can use the .link() API
Migrations
If you are using drizzle-kit to manage your schema and roles, there may be situations where you want to refer to roles that are not defined in your Drizzle schema. In such cases, you may want drizzle-kit to skip managing these roles without having to define each role in your drizzle schema and marking it with .existing().
In these cases, you can use entities.roles in drizzle.config.ts. For a complete reference, refer to the the drizzle.config.ts documentation.
By default, drizzle-kit does not manage roles for you, so you will need to enable this feature in drizzle.config.ts.
In case you need additional configuration options, let’s take a look at a few more examples.
You have an admin role and want to exclude it from the list of manageable roles
You have an admin role and want to include it in the list of manageable roles
If you are using Neon and want to exclude Neon-defined roles, you can use the provider option
If you are using Supabase and want to exclude Supabase-defined roles, you can use the provider option
important
You may encounter situations where Drizzle is slightly outdated compared to new roles specified by your database provider.
In such cases, you can use the provider option and exclude additional roles:
RLS on views
With Drizzle, you can also specify RLS policies on views. For this, you need to use security_invoker in the view’s WITH options. Here is a small example:
Using with Neon
The Neon Team helped us implement their vision of a wrapper on top of our raw policies API. We defined a specific
/neon import with the crudPolicy function that includes predefined functions and Neon’s default roles.
Here’s an example of how to use the crudPolicy function:
This policy is equivalent to:
Neon exposes predefined authenticated and anaonymous roles and related functions. If you are using Neon for RLS, you can use these roles, which are marked as existing, and the related functions in your RLS queries.
For example, you can use the Neon predefined roles and functions like this:
Using with Supabase
We also have a /supabase import with a set of predefined roles marked as existing, which you can use in your schema.
This import will be extended in a future release with more functions and helpers to make using RLS and Supabase simpler.
For example, you can use the Supabase predefined roles like this:
The /supabase import also includes predefined tables and functions that you can use in your application
This allows you to use it in your code, and Drizzle Kit will treat them as existing databases,
using them only as information to connect to other entities
Let’s check an example of adding a policy to a table that exists in Supabase
We also have a great example showcasing how to use Drizzle RLS with Supabase and how to make actual queries with it.
It also includes a great wrapper, createDrizzle, that can handle all the transactional work with Supabase for you.
In upcoming releases, it will be moved to drizzle-orm/supabase, allowing you to use it natively