Enhancing data security with Row-Level Security (RLS) - Part 2: Implementation and key learnings

In our previous blog, we covered why and how we adopted Row-Level Security (RLS) at Rocketlane to enhance data security, streamline permissions handling, and simplify query logic.

You can check it out here if you haven’t already. Now, let's dive deeper into the practical aspects of implementing RLS, sharing critical learnings and challenges along the way.

Implementing RLS: Our step-by-step approach

Our transition to RLS required careful planning and execution. Here's how we did it:

Step 1: Writing the policies

Initially, each table had complex permission logic with multiple JOIN operations across various permission-related tables. With RLS, our objective was clear: replace these JOIN-intensive queries with simpler direct SELECT queries on RLS-enabled tables.

To smoothly migrate each table, we:

• Identified all existing SELECT queries that involved complex permission logic.
• Replaced these queries with simpler versions, leveraging the RLS policies directly.
• Retained conditional backward compatibility via a Redis flag, allowing us to quickly revert to the old logic if necessary.

Step 2: Controlled testing and gradual rollouts

Our controlled rollout followed a meticulous testing strategy:

• Created a new DB user, test_rls_user, specifically for testing the new RLS policies without impacting the existing production user (prod_user_1).
• Deployed the new RLS-enabled queries to our staging environment first, ensuring thorough testing across all scenarios.
• Upon reaching 100% confidence, we gradually enabled RLS for selected production accounts, closely monitoring their performance.
• Once fully confident, we transitioned entirely to the new queries, removing any fallback logic.

This gradual rollout approach significantly reduced risk, providing us peace of mind as we enhanced security.

Leveraging RLS in the API filter layer

One significant advantage of RLS was simplifying authorization logic within our API filter layer. Previously, verifying user permissions required additional permission checks before executing API calls. Post-RLS, the filter layer streamlined this:

• For any API call requesting data manipulation (READ, UPDATE, DELETE), the filter layer executed a simple SELECT query for the resource.
• If the query returned a result, it validated the user's READ permissions implicitly. Otherwise, it returned a straightforward 403 error, significantly reducing complexity.

Optimizing RLS session variables

We soon noticed performance challenges as our session variables grew increasingly complex. Initially, these variables—stored as JSONB maps—contained extensive permissions data, much of it unnecessary for immediate query execution. A heavy query that originally took ~30 seconds dropped to ~9 seconds once we optimized these variables, stripping away irrelevant data.

The key takeaway here is that RLS session variables should ideally stay small and predictable in size. The amount of data going into them should be mostly fixed and not grow unbounded over time.

In our case, as the computation of these session variables itself became expensive, we introduced Redis-based caching. This ensured stable, predictable response times for authorization checks, even as the underlying data grew. Caching proved especially useful when growth in session variable complexity was unavoidable.

Additionally, by flattening critical permission details into columns within RLS-enabled tables, we eliminated the need for nested inner queries in RLS policies, further boosting query performance.

Key learnings on writing effective RLS policies

One insightful discovery emerged from scenarios where a resource (e.g., a task) inherently linked to another (e.g., a project): Initially, our task RLS policies redundantly included checks that duplicated project-level RLS permission checks.

Later, we realized we could simplify this significantly:

1. Instead of manually rewriting project permission checks within task policies, we executed an inner query within the task policy, such as:
   

SELECT * FROM project WHERE project.project_id = task.project_id;

1. This inner query automatically inherited the project's existing RLS policy, removing redundancy and simplifying policy management.

Addressing limitations beyond RLS

While RLS handled row-level permissions efficiently, we faced limitations when dealing with granular permission checks within JSONB column data. Specifically, scenarios required us to grant or deny permissions on individual key-value pairs inside a JSONB column, not just entire rows.

To handle this elegantly, we adopted a creative approach:

• Introduced an ORM-level interceptor using Java and jOOQ.
• After executing the query at the database level, this interceptor checked the returned results for permission-sensitive keys within JSONB columns.
• Leveraged precomputed permissions per request, setting unauthorized key values to null at the jOOQ level, thus abstracting complex permission logic away from individual developers.

This strategy allowed us to achieve a developer-friendly experience akin to RLS, automating complex permission scenarios with minimal manual overhead.

Wrapping up

Implementing RLS was a transformative journey for Rocketlane, drastically simplifying permission handling, improving query performance, and significantly enhancing data security. These key learnings and strategies underline how careful planning, incremental rollout, and targeted optimizations can make RLS implementation seamless and highly beneficial.

Stay tuned for more insights into our continuous journey towards enhanced security and better performance!