Enhancing Data Security at Rocketlane: Adopting Row-Level Security (RLS) - Part 1

At Rocketlane, we build software to help professional services business and post-sale teams nail their service delivery and client collaboration.

Think of Rocketlane as a digital workspace where our customers and their customers team up to seamlessly manage onboarding tasks, integrations, and ongoing projects.

But sharing a workspace means making sure everyone sees only what they're supposed to see. Mess that up and suddenly you've got data leaks, unhappy customers, and a lot of awkward conversations.

How things worked

Rocketlane is a multi-tenant SaaS platform—fancy jargon meaning lots of customers share the same software and database infrastructure but have their data strictly isolated.

While this setup is great for efficiency, it puts a spotlight on data security. We manage this isolation through Role-Based Access Control (RBAC), which assigns permissions based on roles rather than individual users.

RBAC helps us manage permissions at two critical levels:

• Between accounts: Ensuring one customer's data never mingles with another's.
• Within an account: Making sure everyone, from admins to customers to their customers, only sees what's meant for their eyes.

Let's talk specifics. Imagine our task table, which stores things like setting up dashboards or configuring integrations:

‍

1. A Customer from Account A sees tasks 1 and 2, managing internal tasks, plus tasks assigned to their customers.
2. A Customer of Customers from Account A sees only task 2.
3. A Customer from Account B sees only task 3.

Seems straightforward, right? But the reality isn't always so smooth.

Why security is hard (but crucial)

Misconfigured permissions can cause big trouble. We've all heard horror stories of companies accidentally exposing customer data due to sloppy permission setups. Each mistake risks trust, reputation, and bottom lines.

Until recently, Rocketlane embedded permissions directly into application code. This meant developers had to constantly juggle intricate security rules every time they wrote or modified queries.

As we grew, this became increasingly messy and risky.

Searching for simpler solutions

We decided to step back and reconsider our approach. Two main ideas emerged:

1. A dedicated permission layer

Imagine having a standalone, dedicated permissions service acting as a gatekeeper between our application and the database. Each time our app wanted to retrieve or modify data, this service would automatically check permissions first, effectively acting like a security guard at the database's front door.

While this sounds neat and tidy in theory, it introduced several practical challenges.

Firstly, every query would have to pass through an additional layer, potentially slowing things down and affecting the overall performance of our application.

Secondly, adding an extra layer meant increased complexity, making debugging and maintenance harder.

Lastly—and crucially—there was the risk of developers accidentally bypassing or misconfiguring this permission layer. Just one oversight or loophole could inadvertently expose sensitive data, defeating the purpose entirely.

2. Baking permissions into our ORM (JOOQ)

Another idea we considered was integrating permission logic directly into our ORM layer JOOQ, a powerful Java-based tool for building database queries. The concept here was to embed permission checks automatically into every query generated by JOOQ. At first glance, this approach seemed elegant, as it promised seamless permission enforcement directly within the queries themselves, reducing manual intervention from developers.

However, upon closer inspection, we realized this approach had significant drawbacks. Tightly coupling permission logic to our ORM meant that any changes or upgrades to JOOQ could break our carefully embedded security rules. Additionally, this coupling would limit our flexibility, making it difficult to adapt our permission model quickly or adopt alternative tools in the future. Lastly, developers unfamiliar with the nuances of these embedded security rules might unintentionally introduce vulnerabilities by improperly modifying queries or bypassing established patterns.

Both approaches still left too much room for human error and complexity. Then we discovered something better: PostgreSQL's built-in Row-Level Security (RLS).

Hello, row-level security (RLS)

Row-Level Security, or RLS, is a powerful yet straightforward feature available directly within PostgreSQL databases. Think of RLS like an automatic filter built into your database, ensuring users only see the data they're allowed to view. Instead of manually coding security rules into every query or feature, you set the rules once at the database level. Then, PostgreSQL automatically applies these rules every single time someone tries to access data.

Here's how it works in practice:

1. Turn RLS on for the task table:

ALTER TABLE task ENABLE ROW LEVEL SECURITY;

‍

2. Define clear, simple policies‍

For account managers (customers):

CREATE POLICY customer_access ON task
FOR SELECT
USING (account_id = current_setting('app.account_id')::text);

‍

For their customers:

CREATE POLICY customer_of_customers_access ON task
FOR SELECT
USING (
    account_id = current_setting('app.account_id')::text
    AND assigned_to = 'Customer of Customers'
);

‍

3. Set session variables:

Just configure a quick session setting before queries:

SELECT set_config('app.account_id', 'A', true);

Now, with RLS in place, here's exactly how permissions are dynamically enforced by PostgreSQL whenever someone runs a database query.

Let's revisit the task table for clarity:

‍

Scenario 1: Customer user from Account A logs in

Step 1: When the Customer User from Account A logs into the app, the application sets a session variable that tells PostgreSQL who’s querying the database:

SELECT set_config('app.account_id', 'A', true);

‍

Step 2: The Customer User runs a simple query:

SELECT * FROM task;

‍

Here's what PostgreSQL does internally:

1. Applies the RLS policy named customer_access, which checks:

account_id = current_setting('app.account_id')::text

‍

1. Since current_setting('app.account_id') is 'A', PostgreSQL only returns rows with account_id = 'A'.

Query result:

‍

Scenario 2: Customer of Customers User from Account A logs in

Step 1: The session variable remains the same (app.account_id = 'A'). However, this user has a different role (Customer of Customers).

Step 2: The same query again:

SELECT * FROM task;

‍

Here's PostgreSQL’s internal logic now:

1. PostgreSQL applies the more restrictive policy, customer_of_customers_access, defined as:

account_id = current_setting('app.account_id')::text
AND assigned_to = 'Customer of Customers'

‍

1. This explicitly filters tasks not only by account but also by the assigned role.

Query result:

‍

Scenario 3: Customer User from Account B logs in

For completeness, let's quickly cover Account B:

Step 1: The application now sets the session for a user from Account B:

SELECT set_config('app.account_id', 'B', true);

‍

Step 2: Again, run the standard query:

SELECT * FROM task;

‍

PostgreSQL’s internal logic:‍

1. It applies the same customer_access policy, this time with app.account_id = 'B'.

Query result:

‍

The bottom line:

RLS automatically ensures every query returns exactly—and only—what the user's permissions allow, without developers manually coding intricate permission checks. This makes permissions crystal-clear, secure, and effortlessly enforced by PostgreSQL itself.

Why RLS Rocks for Rocketlane

Adopting Row-Level Security has been a massive win for Rocketlane. Previously, developers had to embed intricate, manual permission checks directly into application code, creating a tangled web of complexity. With RLS, all that complexity moves neatly into PostgreSQL. This means:

1. Simpler code: Developers no longer need to manage permission logic within the application. Queries become cleaner and more readable, significantly reducing the risk of mistakes.
2. Stronger security: With permissions consistently enforced at the database layer, there's far less room for human error. This automatic enforcement drastically reduces vulnerabilities and gives us greater peace of mind.
3. Faster development cycles: Developers now spend less time worrying about permissions and more time focusing on what really matters—building innovative features and improving user experiences.
4. Easier maintenance: Centralised security policies mean fewer changes to manage across the codebase, making updates quicker and more reliable.

In short, PostgreSQL's Row-Level Security has become a fundamental part of our toolkit, enhancing security, simplifying development, and empowering our developers to innovate confidently.

In our next post, we'll explore specific implementation challenges we faced, our strategies for overcoming them, achievements gained from adopting RLS, and key learnings from this transition.

Stay tuned!

**This work was brought to life through the collaborative expertise of Nishant Mahesh along with me, partnering closely from concept to completion.