How to build data apps with row-level security

Summary

• Row-level security (RLS) restricts users to see only data they are authorized for, essential for multi-tenant applications and sensitive data access control.
• Define RLS policies on Lakebase Postgres using the CREATE POLICY statement, scoped to tables and users.
• The Lakebase Data API enforces these policies automatically, inheriting each user's identity and permissions from Unity Catalog.
• Query via REST endpoints with OAuth 2.0 or PAT authentication, and each user's request applies their RLS policies transparently.
• Test isolation by querying the same table as different users to verify that RLS policies restrict results appropriately.

How to build data apps with row-level security

Row-level security (RLS) is a critical capability for building secure multi-tenant applications on Lakebase. When you enable RLS policies on your Postgres tables, each user sees only the rows they are authorized to access. Combined with the Lakebase Data API and Databricks Unity Catalog governance, RLS lets you build apps where data access is automatically enforced at the database level—no custom logic required.

What is row-level security and why your app needs it

Row-level security is a database-enforced access control mechanism that automatically filters query results based on the executing user's identity and permissions. Instead of building row filtering logic into your application layer, RLS policies are defined and enforced at the database level, making them:

• Secure by default: Even if your application has a bug or is misconfigured, the database enforces the policy.
• Consistent across all clients: Any client querying the same table—whether web app, API, or analytics tool—receives the same filtered results.
• Low-latency: Filtering happens at query execution, not in application code.

RLS is essential for:

• Multi-tenant SaaS applications where each customer should see only their own data.
• Team-based applications where data is scoped to org, department, or project.
• Regulated industries where data must be isolated by compliance boundary (e.g., healthcare by patient, finance by account).

Defining RLS policies on Lakebase Postgres

Lakebase is fully-managed Postgres, so you use standard Postgres RLS syntax. To enable RLS on a table:

-- Enable RLS on the table
ALTER TABLE sales_data ENABLE ROW LEVEL SECURITY;

-- Create a policy that restricts access by user
CREATE POLICY user_isolation ON sales_data
  FOR SELECT
  USING (salesperson_id = current_user_id());

In this example:

• FOR SELECT means the policy applies to SELECT queries.
• USING (condition) defines the row filter: rows are visible only if the condition is true.
• current_user_id() is a function that returns the authenticated user's ID.

You can create multiple policies on a single table to handle different access patterns:

-- Policy for regular users: see only their own sales
CREATE POLICY user_sales ON sales_data
  FOR SELECT
  USING (salesperson_id = current_user_id());

-- Policy for managers: see all sales
CREATE POLICY manager_bypass ON sales_data
  FOR SELECT
  USING (current_setting('app.user_role') = 'manager');

-- Policy for INSERT: users can only insert their own sales
CREATE POLICY user_insert ON sales_data
  FOR INSERT
  WITH CHECK (salesperson_id = current_user_id());

You can also use Databricks' system integration to scope policies by Unity Catalog principals:

-- Scope access by Databricks principal (user or service principal)
CREATE POLICY principal_isolation ON customer_data
  FOR SELECT
  USING (assigned_to_principal = current_principal());

Wiring the Lakebase Data API for RLS

The Lakebase Data API is a REST interface to your Postgres tables, built into the Databricks Platform. When you query a table through the Data API, RLS policies are applied automatically based on the authenticated user's identity.
To expose a table via the Data API:

1. Create or import your table into Lakebase with RLS policies enabled.
2. Share access via the Lakebase Data API using Databricks' API endpoint.
3. Authenticate your app with OAuth 2.0 or a Personal Access Token (PAT) scoped to the Databricks workspace.

Example: Query your table via REST:

curl -X GET https://your-workspace.cloud.databricks.com/api/2.0/lakebase/data-api/table/your_table \
  -H "Authorization: Bearer <token>" \
  -H "Content-Type: application/json"

When Alice queries the API, her token is resolved to her Databricks identity. Lakebase applies her RLS policies, and the API returns only rows she can access. When Bob queries the same endpoint, his identity is resolved differently, so he sees a different filtered result set.

Understanding permission inheritance

RLS policies are evaluated in the context of the authenticated principal (user or service principal). Here's the flow:

1. App user authenticates to Databricks via OAuth 2.0 or PAT.
2. Data API receives the request with the token.
3. Lakebase resolves the token to a Databricks principal (user or service principal).
4. RLS policies evaluate against that principal's identity.
5. Only authorized rows are returned to the app.

For multi-tenant apps, you typically:

• Authenticate app users via your identity provider (e.g., Auth0, Okta).
• Use a service principal with delegated trust to represent each tenant or user cohort.
• Configure RLS policies to scope data by tenant ID or user ID.

Testing RLS isolation

Before deploying to production, verify that your RLS policies work correctly:

1. Connect as User A via a Databricks SQL editor or the Data API:

   SELECT * FROM customer_data;
   -- Returns only rows where customer_tenant_id = 'tenant-a'
   

2. Connect as User B and run the same query:

   SELECT * FROM customer_data;
   -- Returns only rows where customer_tenant_id = 'tenant-b'
   

3. Verify the counts differ and that there is no data leakage across tenants.
4. Test edge cases:
   • Admin user with a bypass policy sees all rows.
   • Unauthenticated requests are rejected.
   • Tampering with the PAT or token does not escalate access.

FAQs

Q: Does RLS apply to all Lakebase tables or only those I explicitly enable?

A: RLS is opt-in per table. You must run ALTER TABLE <table> ENABLE ROW LEVEL SECURITY and define policies. Tables without RLS policies have no row-level restrictions.

Q: Can I use RLS alongside Unity Catalog permissions?

A: Yes. Unity Catalog controls whether a user can access a table (table-level permissions). RLS controls which rows they see within that table. Both are enforced together.

Q: What happens if my RLS policy references a function that's slow or unavailable?

A: Queries fail with an error. Test your policies and custom functions in a dev environment before production to ensure they perform well.

Q: Can I disable a policy without dropping it?

A: Yes, you can ALTER TABLE <table> DISABLE ROW LEVEL SECURITY to temporarily disable all policies on a table, or use ALTER POLICY <policy> ON <table> DISABLE if your Postgres version supports it. Check the Postgres version running on your Lakebase instance.

Q: How do I audit which users accessed which rows?

A: Enable Postgres query logging and audit Databricks workspace audit logs to see who authenticated and what endpoints they queried. Log rows returned alongside the querying principal to track data access.