What is Row-Level Security (RLS)? Data Isolation in PostgreSQL for SaaS

What is Row?

Row-Level Security (RLS) is a built-in database feature in PostgreSQL that controls at the row level which data a user can read, create, update, or delete. Through declarative policies, access rules are defined per table that are automatically and transparently enforced on every query, regardless of the source of the request. RLS operates at the database level, fully independent of application code, making it a fundamental defense layer for data isolation in multi-tenant SaaS applications and compliance with privacy regulations like GDPR.

How does Row work technically?

RLS in PostgreSQL is activated per table with ALTER TABLE tablename ENABLE ROW LEVEL SECURITY. Once enabled, the default policy is deny-all: no rows are visible unless an explicit policy grants access. Policies are created with CREATE POLICY and can be scoped to specific operations: FOR SELECT, FOR INSERT, FOR UPDATE, and FOR DELETE. A typical multi-tenant policy filters on tenant_id: CREATE POLICY tenant_isolation ON orders USING (tenant_id = current_setting('app.tenant_id')::uuid). The USING clause filters which existing rows are visible, while WITH CHECK validates which rows may be inserted or modified. The user context is typically set via SET LOCAL or set_config() at the beginning of each request. In Supabase, the tenant_id is automatically derived from the JWT token of the authenticated user. The auth.uid() function returns the user ID and auth.jwt() provides access to custom claims in the token. RLS integrates seamlessly with Role-Based Access Control (RBAC). Policies can combine conditions: an admin role sees all rows, a user role sees only their own data, and a viewer role sees all organization data but cannot modify anything. This is implemented through OR conditions or multiple policies per table. For performance, it is crucial that the columns referenced in RLS policies are indexed. A policy on tenant_id without an index on that column results in a full table scan on every query. PostgreSQL applies RLS filters as additional WHERE conditions that are optimized by the query planner together with the original query. The BYPASSRLS privilege allows specific database roles to circumvent RLS, which is necessary for administrative tasks like migrations, data exports, and background processes. In Supabase, this is the service_role key that must only be used server-side. For complex authorization scenarios, policies can leverage subqueries and functions. A policy can verify whether a user is a member of a specific team through a subquery on the team_members table. Security definer functions encapsulate complex authorization logic that is reused across multiple policies. This prevents duplication and makes the authorization model centrally manageable from a limited number of functions.

How does MG Software apply Row in practice?

MG Software implements RLS in every multi-tenant SaaS application we build on Supabase. Our standard approach begins with enabling RLS on all tables containing tenant data, combined with a default-deny policy that prevents new tables from being accidentally unprotected after a migration. Policies are based on the tenant_id stored as a custom claim in the Supabase JWT token. Upon authentication, the tenant context becomes automatically available via auth.jwt()->'tenant_id'. This eliminates the need to implement tenant filtering in application code and guarantees isolation regardless of how the data is accessed. For administrative tasks and background processes, we use the Supabase service_role key that bypasses RLS. This key is used exclusively server-side in protected API routes and background processes, never in client-side code. For every new table, we document the expected RLS policies in the migration script and test them with automated tests that execute queries from different user contexts to detect cross-tenant leaks.

Why does Row matter?

Row-Level Security provides a defense layer for data isolation that functions independently of application code. In multi-tenant SaaS, RLS prevents bugs, API errors, or incorrect queries from accidentally exposing data belonging to other tenants. This is not merely a best practice but a requirement for compliance with regulations like GDPR. During security audits, RLS serves as concrete evidence that data isolation is implemented at the infrastructure level. Without RLS, data isolation depends entirely on correct WHERE clauses in every application query. A single forgotten filter in a new endpoint or an error in an ORM query can lead to a data breach affecting all tenants. RLS shifts this responsibility to the database itself, where it is enforced consistently and automatically regardless of how data is queried, whether through the application, an admin tool, or a direct database connection.

Common mistakes with Row

Teams often forget to test RLS policies with different user contexts, leaving permission leaks undetected until a security audit or incident. Write automated tests that execute queries as users from different tenants and systematically validate that cross-tenant data is invisible for both read and write operations. A second common mistake is not setting a default-deny policy when enabling RLS. Without an explicit default-deny, new tables without policies are accessible to all authenticated users. Always establish restrictive policies as the baseline and grant access only through explicit GRANT statements. Additionally, performance implications are underestimated: policies on unindexed columns cause full table scans that progressively slow the database as tables grow in size. Always add an index on the column being filtered in the RLS policy.

What are some examples of Row?

• A multi-tenant SaaS application where the orders table has an RLS policy filtering on tenant_id. The policy uses auth.jwt()->'tenant_id' to identify the current tenant. Users from Organization A see exclusively their own orders, even if the API accidentally omits a tenant filter.
• A project management tool where team members only see projects they have been invited to. The RLS policy combines tenant isolation with project-level access: USING (tenant_id = auth.jwt()->'tenant_id' AND id IN (SELECT project_id FROM project_members WHERE user_id = auth.uid())).
• An admin dashboard that uses the service_role key to bypass RLS for cross-tenant reporting and user management. The service_role is invoked exclusively from a server-side API route that enforces its own authentication and authorization.
• A document-sharing feature where an RLS policy supports multiple access levels: owners can read and write, team members can read, and publicly shared documents are visible to all authenticated users within the same tenant.
• Automated RLS tests in the CI/CD pipeline that verify for each table that a user from Tenant A cannot read, create, or modify data from Tenant B. The tests use Supabase client libraries with tokens from different test users.

Related terms