| |

Keep Data Safe in Multi-Tenant Systems: A Case for Supabase and Row Level Security

ByTomas Zezula

When managing multi-tenant systems, one common anxiety is that a coding error can unintentionally expose user data. Incorrectly routing a user’s data to the wrong tenant, or even distributing it to another user within the same tenant, is a very real fear. Additional precautions are necessary to safeguard against human errors. Supabase is an open-source platform that offers a viable alternative to Firebase. Similar to Firebase, it provides a frictionless user authentication process. Due to its integration with Postgres, it supports sophisticated access controls at a row level, aptly referred to as Row-Level Security (RLS). In this article, we’ll explore a concrete example of leveraging this feature and provide step-by-step implementation guidelines.

Table of Contents

Why Supabase?

Supabase is an open source alternative to Firebase. This gives you the possibility to install it on premise or set it up with a cloud provider of your choice. Obviously, you can also leverage the default hosting provided out of the box. This is what we are going to use in this tutorial for simplicity.

Why Ktor?

There are two primary reasons for this decision. Firstly, Supabase offers a seamless integration with Kotlin, having a dedicated SDK for it. Secondly, Ktor stands apart for its simplicity in comprehension, setup, and usage. You’re welcome to explore other frameworks, like Spring Boot with Web(Flux), or any other alternative that enables rapid initiation of a web server. My choice for Ktor is primarily due to its straightforward approach towards constructing scalable and resource-efficient web services.

Getting Ready

First things first. If you want to follow along, go to Supabase, register a new account and create a new project. Once done, head out to the project’s dashboard `Settings > API` and take note of your database url and the API key.

Copy the URL and API key. You’ll need it in your project.

Next, let’s create a table. Our application is a simple TODO list. I feel like it’s such a commonly understood concept that no other explanations are necessary.

Still in your project, navigate to the SQL editor and paste the following table definition. Unless you’re building your own thing, of course. In that case, please use the definition below as an inspiration.

create table
  public.todos (
    id uuid primary key,
    user_id uuid references auth.users,
    task text not null,
    is_done boolean not null default false
  ) tablespace pg_default;

Note that the table is in the public schema. Meaning, that without any additional protection this table’s content will be publicly accessible. Even to anonymous users.

Great, you’re almost done. For the rest of this tutorial, you can checkout the source code from GitHub and follow along.

Installing Dependencies

We’ll use Gradle. You can review the entire build config here.

First of all, let’s import dependencies necessary to start a Ktor web server. These include logging and content negotiation for a seamless deserialization of JSON responses.

implementation("io.ktor:ktor-server-content-negotiation:$ktor_version")
implementation("io.ktor:ktor-server-core-jvm")
implementation("io.ktor:ktor-server-netty-jvm")
implementation("ch.qos.logback:logback-classic:$logback_version")
implementation("io.ktor:ktor-server-config-yaml:$ktor_version")
implementation("io.ktor:ktor-server-html-builder:$ktor_version")

For Supabase, we use the officially recommended multimodule Kotlin client, along with a Ktor client, which is a required dependency.

implementation("io.github.jan-tennert.supabase:postgrest-kt:$supabase_version")
implementation("io.ktor:ktor-client-cio:$ktor_version")

Running the App

Key instructions regarding how to set up the project and run the app are on GitHub. Here, I’ll just focus on explaining the use case.

The Data Leak

The app we’ve developed allows registered users to create their personal TODO lists. Each list is specific to an individual user and should remain invisible to others. It’s crucial to understand that even anonymous users can access the database table. We could consider this situation a design fault or an unnoticed bug by the developers.

To make it clear, our app does not employ any data filtering. In fact, it returns an unfiltered set of all TODO lists created by every user. Our main objective is to demonstrate that even in cases where a developer neglects to apply data filtering (for instance, by user ID), the framework steps in, ensuring there’s no accidental data leakage. However, it’s important to note that, as of now, nothing prevents a random visitor from accessing all the data at will.

This is what a random visitor sees after the table has been populated with some data.

Data of all users are openly exposed to anonymous public.

The UI is truly horrible, but I hope it gets the message across. The User column displays UID of registered users, so that we can easily follow whose data are we looking at. The view comprises two different datasets from two distinct users:

  • user c5177... has created tasks 1 – 3
  • user 1d00f... has created tasks 4 and 5

To avoid confusion, this is the only data we will consider for the rest of this tutorial.

Right now, the situation is looking bleak. The data is open to anybody, even when they’re not registered in our system. Let’s fix it.

Row-Level Security to the Rescue!

Supabase allows to easily restrict data access. First of all, we need to enable row-level security (RLS) for our table.

-- Enable RLS
alter table todos enable row level security;

This is enough to prevent general public from accessing private data. For an anonymous user, the list is now empty.

Enabling RLS is enough to prevent public access to our database.

How about the registered users though? Have their privacy concerns been fully addressed? Well, not quite.

As much as the data is now only accessible to registered users, once a user logs in they can still see not only their own TODO list, but also data of everybody else registered in the system. That’s certainly a problem. Even more so, when we’re supposed to flawlessly handle multiple tenants who must be strictly isolated from each other.

Before we move onto solving this challenge, let’s briefly talk about authentication.

Seamless Authentication

Similarly to Firebase, Supabase provides rich authentication options out of the box. In this tutorial, we only allow for an email / password scenario. In reality, there’s wide range of integrations with third-party providers: Google, GitHub, Microsoft and many others.

From developer’s perspective, the authentication module is super easy to configure and use. All we need is to instantiate a client.

import io.github.jan.supabase.createSupabaseClient
import io.github.jan.supabase.gotrue.GoTrue
import io.github.jan.supabase.postgrest.Postgrest

...
val client = createSupabaseClient(
   supabaseUrl = environment.config.property("ktor.supabase.url").getString(),
   supabaseKey = environment.config.property("ktor.supabase.key").getString()
) {
   install(GoTrue)
   install(Postgrest)
}
...

After passing the database url and the API key, we move onto installing the dependencies. While the Postgrest plugin handles access to our database (Postgres), the GoTrue plugin mediates user authentication.

User registration, login and logout are mere one-liners.

// User registration
client.gotrue.signUpWith(Email) {
    this.email = email
    this.password = password
}

// Login
client.gotrue.loginWith(Email) {
    this.email = email
    this.password = password
}

// Logout
client.gotrue.clearSession()

See the UserService for full details.

What’s Mine is My Own

Back to our problem with overly too permissive data access. Restricting users to viewing and working with only the data they own is straightforward. 

create policy "User can see their own todos only."
on todos
for select using ( auth.uid() = user_id );

Supabase allows for conditional constraints applied on the row level. Remember our table definition? Here is the part that matters.

create table
  public.todos (
    ...
    user_id uuid references auth.users,
    ...
  ) tablespace pg_default;

The column user_id references the users table managed by Supabase. Authentication data is automatically protected and cannot be directly accessed, not even from application code. However, Supabase allows for an internal use in foreign keys and particularly in restrictive policies, like the one above. With this new policy in place, not only is the data shielded away from anonymous users, but individual records are also protected from accidental access by a registered user who didn’t necessarily created them.

The column labeled user_id serves as a reference to the users table which is maintained by Supabase. Although the users table is particularly exclusive and difficult to access, Supabase permits its internal usage in foreign keys and in dedicated, limiting policies like the one stated above.

The record filtering tied to the currently authenticated user is key.

auth.uid() = user_id

Whoever logs in, their UID will be automatically applied as a filter when accessing the TODO table. This restrictions always applies, regardless of the code base or user activities.

By establishing this policy, we are not only secluding the data from the reach of anonymous visitors but also safeguarding individual records from accidental (or deliberate) access by registered users who may not be the original creators of these records.

With this new policy in place, even when the app continues to entirely disregard filtering, the users will only ever see their own records.

Summary

Supabase provides record-level data protection. Always consider ownership and eligibility to access user data. It’s a good practice to incorporate restrictive policies from the start.

-- 1. Create a table
create table
  public.todos (
    id uuid primary key,
    user_id uuid references auth.users,
    task text not null,
    is_done boolean not null default false
  ) tablespace pg_default;

-- Enable RLS
alter table todos enable row level security;

-- Define a restrictive policy
create policy "User can see their own todos only."
on todos
for select using ( auth.uid() = user_id );

Supabase supports most mainstream languages. Their SDK is intuitive and easy to use.

Thank you for reading. The source code is available on GitHub. Check it out and let me know your thoughts. Thanks.

Similar Posts

| |

JUnitParams – parameterized unit tests

ByTomas Zezula

Less unit test code doesn’t necessarily result into poor coverage. I’ve recently entered the paradigm of parameterized tests when refactoring a larger test suite. I ended up writing significantly less code without compromising on the extent. In fact, adding a few corner cases was almost a no-brainer. In this post I’d like to share my…

JWT embraces trust
| |

Conquer Authentication with Ktor: Part 5 – Introduction into JSON Web Tokens

ByTomas Zezula

Welcome back to our ongoing series on the Ktor framework and its impressive capabilities when it comes to web application security. In this article, we shift our focus from traditional form-based authentication to a modern, innovative one – JSON Web Tokens (JWT). JWT provides several key advantages that have led to its growing popularity. Firstly, its stateless nature promotes better scalability by eliminating the need to maintain user sessions. Secondly, JWT’s JSON structure guarantees cross-platform compatibility. This means your authentication mechanism works equally well for web apps as it does for mobile apps or other systems. Lastly, JWT proactively increases the security of your applications, preventing attacks like CSRF. So, join us today as we delve into the world of JWT, exploring its mechanics and benefits.