Fine Grained Access Control with Row Level Security

2025-07-09 17:53:00
www.cockroachlabs.com

Controlling who can access what data is more important than ever as organizations scale and modernize their data infrastructure.

Enterprises are modernizing their critical databases, and they need fine-grained, built-in access controls that go beyond table-level permissions. That’s why, with the 25.2 release of CockroachDB, we’re introducing Row-Level Security — a powerful feature that allows you to define and enforce access policies at the row level, directly within the database.

This form of mandatory access control enables developers and operators to tightly govern data visibility based on user roles or attributes. That makes it a natural fit for securing sensitive workloads and building robust multi-tenant applications.

In this article we’ll explore how CockroachDB makes it simple to isolate data, comply with regulatory requirements, and reduce application-side complexity, all while maintaining performance at scale and with minimal application changes using Row Level Security.

Why row-level security matters

CockroachDB already has robust support for managing privileges at the table, database or the schema level. While table-level permissions provide essential control, they often lack the granularity needed to handle nuanced access scenarios effectively.

For instance, consider a multi-tenancy situation where multiple customers or tenants share a single table within an application. Without granular, row-level access control, ensuring that each tenant can access only their own data becomes challenging and risky.

Another critical scenario, multi-region, involves data restriction based on geography. Consider an enterprise where users should only have access to rows related to a specific geographical region: Table-level privileges alone would not be sufficient to enforce this policy without complicating the data model.

Next we’ll explore these two scenarios in greater detail, and show you how Row Level Security in CockroachDB effectively addresses each use case.

Multi-Tenancy + Row Level Security

Multi-tenancy in databases is a design where a single database instance serves multiple customers (tenants), with each tenant’s data kept logically isolated. This approach helps reduce infrastructure costs, streamline operations, and scale more easily, which is especially valuable for SaaS applications.

There are two common approaches to implementing multi-tenancy:

A separate database or cluster per tenant provides strong isolation but is expensive and operationally complex as the number of tenants increases.
Separate schema or table per tenant lowers infrastructure costs but becomes hard to manage at scale, requiring duplicated schema changes and inconsistent performance tuning.

A third and more scalable option is Row-Level Security (RLS). With RLS in CockroachDB, all tenant data lives in shared tables, and access is controlled at the row level based on tenant identity. This model combines strong logical isolation with lower infrastructure and operational costs, making it well suited for high-scale multi-tenant environments.

From an operational perspective, Row-Level Security (RLS) makes management simpler. Instead of maintaining a separate schema or table for each tenant, you use a single shared schema.

This means schema changes, like adding a column or modifying an index, only need to be applied once. It reduces the risk of inconsistencies between tenants and eliminates the need for complex migration processes or tenant-specific logic in deployment pipelines.

On the infrastructure side, RLS allows for more efficient resource usage. By consolidating tenant data into shared tables, CockroachDB can optimize performance across tenants without duplicating storage or compute resources. This lets you host more tenants on the same cluster, improving hardware utilization and reducing the need for additional infrastructure to support growth.

To demonstrate how Row Level Security can enable multi-tenant setups with minimal application changes, let’s look at the following example:

First, we create an in-memory cluster for demo purposes.

cockroach demo --no-example-database --insecure

Then we will create our database and a multi-tenant table, note the tenant_id column.

CREATE DATABASE store;

CREATE TABLE tenant_data (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    tenant_id UUID NOT NULL,

    data TEXT NOT NULL

);

Next, we’ll create an index on the data column, prefixed with tenant_id. This is a crucial step because including tenant_id at the beginning of the index ensures that queries scoped to a specific tenant can efficiently locate relevant rows.

Without these indexes, the database may need to scan large portions of the table, which can degrade performance as your dataset grows.

CREATE INDEX ON tenant_data(tenant_id, data);

Next we will create a user that we will later login as:

CREATE ROLE secureuser LOGIN;

GRANT ALL ON store.* TO secureuser;

Now we can enable row level security. Note the FORCE option below, this enforces the row level security policy for everyone including the table owner:

ALTER TABLE tenant_data ENABLE ROW LEVEL SECURITY;

Next we will create our Row Level Security Policy which checks the value of a current_tenant session variable against the values in the tenant_id column:

CREATE POLICY tenant_isolation_policy ON tenant_data FOR secureuser

USING (tenant_id = current_setting('app.current_tenant')::UUID);

INSERT INTO tenant_data (tenant_id, data) VALUES ('11111111-1111-1111-1111-111111111111', 'abc'), ('11111111-1111-1111-1111-111111111111', 'abcdef'), ('22222222-2222-2222-2222-222222222222', 'abc'), ('22222222-2222-2222-2222-222222222222', 'abcdef');

Now we will login as our secureuser that we created earlier. From another terminal run:

cockroach sql --insecure --url 'postgres://secureuser@127.0.0.1:26257/store?sslmode=disable'

Then we can set the tenant context using a session variable:

SET app.current_tenant = '11111111-1111-1111-1111-111111111111';

If we query the data, we will only see the rows that belong to tenant: ‘11111111-1111-1111-1111-111111111111’:

SELECT * FROM tenant_data WHERE data = 'abc';

                   id                  |              tenant_id               | data

---------------------------------------+--------------------------------------+-------

  e511fc8e-5621-48b6-b792-c4b12a07b2ca | 22222222-2222-2222-2222-222222222222 | abc

(1 row)

In fact even if we were to select everything in that table we would only see data for tenant ‘11111111-1111-1111-1111-111111111111’:

SELECT * FROM tenant_data;

                   id                  |              tenant_id               |  data

---------------------------------------+--------------------------------------+---------

  4143c0da-4fda-4f09-9149-5b7d2988d206 | 11111111-1111-1111-1111-111111111111 | abc

  4d39e12e-5e86-4c93-868f-41771ba46137 | 11111111-1111-1111-1111-111111111111 | abcdef

(2 rows)

Similarly, if we switch the current_tenant we can only see data for ‘22222222-2222-2222-2222-222222222222’:

SET app.current_tenant = '22222222-2222-2222-2222-222222222222';

SELECT * FROM tenant_data WHERE data = 'abc';

                   id                  |              tenant_id               | data

---------------------------------------+--------------------------------------+-------

  e511fc8e-5621-48b6-b792-c4b12a07b2ca | 22222222-2222-2222-2222-222222222222 | abc

(1 row)

This highlights the strength of Row-Level Security. With a few well-defined policies and session variables, and without any changes to the underlying application queries:

We can present each tenant with a logical view of the data.
To each tenant, it appears as if they have exclusive access to their own data, even though it is stored in the same physical table as other tenants’ data.
This keeps the application layer simple while ensuring mandatory access control at the database level.

Multi-region + row-level security

CockroachDB supports easy declarative syntax to home data using REGIONAL BY ROW (RBR) tables. In a regional by row table, each row in a single logical table can be stored in a specific geographic region. But what about safeguarding who has access to those rows?

Colocating data is only part of the story – when applications span multiple regions, access control must follow suit. This is where Row-Level Security and RBR complement each other perfectly.

Let’s imagine a scenario where data residency laws restrict users in the US from accessing data stored in the EU. Let’s walk through how we can help enforce compliance with this by combining RBR tables and Row Level Security together.

First we create a multi-region in memory cluster for demo purposes:

cockroach demo --nodes=9 --no-example-database --insecure

Then we create our schema:

CREATE DATABASE store

  PRIMARY REGION "us-east1"

  REGIONS "us-west1", "europe-west1";
USE store;
CREATE ROLE secureuser LOGIN;

GRANT ALL ON store.* TO secureuser;
CREATE TABLE user_profiles (

    user_id UUID PRIMARY KEY,

    name STRING,

    email STRING

) LOCALITY REGIONAL BY ROW;

INSERT INTO user_profiles (user_id, name, email, crdb_region) VALUES (gen_random_uuid(), 'Alice', 'alice@example.com', 'us-east1'), (gen_random_uuid(), 'Bob', 'bob@example.com', 'us-west1'), (gen_random_uuid(), 'Clara', 'clara@example.eu', 'europe-west1');

Now we enable row level policy and attach a policy to the table. If we look closely at our policy we have a USING clause that is checking if the crdb_region in the table is the same as the gateway region (ie; the region of the node in which the user is logged into):

ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY, FORCE ROW LEVEL SECURITY;

CREATE POLICY regional_access ON user_profiles USING (crdb_region = gateway_region()::crdb_internal_region);

Now to demonstrate the power of Row Level Security we will connect to nodes in different regions and see how it affects our query results.

When running cockroach demo, each node is accessible via a different port, starting from 26257. We can see the nodes and their region with port info by running the following from a new terminal window:

cockroach node status --insecure | awk 'NR > 1 && NF { print $3, $13 }'

127.0.0.1:26257 region=us-east1,az=b 127.0.0.1:26258 region=us-east1,az=c 127.0.0.1:26259 region=us-east1,az=d 127.0.0.1:26260 region=us-west1,az=a 127.0.0.1:26261 region=us-west1,az=b 127.0.0.1:26262 region=us-west1,az=c 127.0.0.1:26263 region=europe-west1,az=b 127.0.0.1:26264 region=europe-west1,az=c 127.0.0.1:26265 region=europe-west1,az=d

Now let’s connect to a node in us-east1 as secureuser and try to query some data:

cockroach sql --insecure --url 'secureuser://@127.0.0.1:26257/store?sslmode=disable'

As we can see, since we are connected to a node in us-east1 we are only seeing rows that are available in the us-east1 region. Similarly if we connect to a node in europe-west1, we will only see data in the europe-west1 region:

cockroach sql --insecure --url 'postgres://secureuser@127.0.0.1:26263/store?sslmode=disable'

Combining Row-Level Security with Regional by Row tables gives you precise control over both data location and access. Row-Level Security can also be used to enforce not just read access but also writes, ensuring that nodes in the US-east cannot insert or update EU-related data. This helps maintain strict regional boundaries and prevents accidental cross-region writes. You can also set up alerting around rejected writes to catch and respond to misrouted traffic early. This is crucial for applications that operate across regions with data residency rules, such as GDPR.

It allows you to keep data physically in the required location, and ensures users can only access what they are allowed to see based on their connection region.

All of this is done at the database level, which reduces complexity in the application.

Scale Your Data Securely and Efficiently

Row-Level Security (RLS) in CockroachDB is a major advancement for modern data access control. It provides a powerful, built-in way to enforce fine-grained access policies directly within the database.

What sets CockroachDB apart is its ability to pair RLS with multi-region data placement, enabling access control based not just on user identity but also on geographic context.

Whether you’re building a multi-tenant SaaS platform or enforcing regional data boundaries, CockroachDB is how to scale securely and efficiently without rewriting application logic.

Security Quick Start

Ready to try out Row-Level Security in CockroachDB? These resources will get you started with implementing fine-grained access control in your applications:

Row-Level Security documentation: The official docs cover everything you need to know about enabling RLS, writing policies, and understanding how access control is enforced at the row level.
Demo: A video from Cockroach Labs Technical Evenagelist Rob Reid that walks through setting up RLS in a multi-tenant and multi-region environment, showing how access can be scoped cleanly and securely.

Try CockroachDB Today

Spin up your first CockroachDB Cloud cluster in minutes. Start with $400 in free credits.
Or get a free 30-day trial of CockroachDB Enterprise on self-hosted environments.

Dikshant Adhikari is a Senior Product Manager for Cockroach Labs.

Matt Spilchen is a Senior Staff Software Engineer at Cockroach Labs

Source Link

Keep your files stored safely and securely with the SanDisk 2TB Extreme Portable SSD. With over 69,505 ratings and an impressive 4.6 out of 5 stars, this product has been purchased over 8K+ times in the past month. At only $129.99, this Amazon’s Choice product is a must-have for secure file storage.

Help keep private content private with the included password protection featuring 256-bit AES hardware encryption. Order now for just $129.99 on Amazon!

Unlock unlimited streaming with a free Amazon Prime trial!
Sign up today!

Help Power Techcratic’s Future – Scan To Support

If Techcratic’s content and insights have helped you, consider giving back by supporting the platform with crypto. Every contribution makes a difference, whether it’s for high-quality content, server maintenance, or future updates. Techcratic is constantly evolving, and your support helps drive that progress.

As a solo operator who wears all the hats, creating content, managing the tech, and running the site, your support allows me to stay focused on delivering valuable resources. Your support keeps everything running smoothly and enables me to continue creating the content you love. I’m deeply grateful for your support, it truly means the world to me! Thank you!

BITCOIN

Bitcoin Logo