RLS is the foundation of BaaS

There has been massive innovation in the database and backend space for developers building applications. As a consequence, the emerging trend is the rise of new database cloud service providers like below:

• Convex: MySQL through Vitess. It can easily handle quite a large scale and also includes some nice features to speed up and monitor queries.
• Neon: Postgres with separation of storage and compute. Uniquely designed for serverless, and works with the native Postgres driver + supports database branching workflows.
• Supabase: Open-source, built-on pure Postgres. Database + Auth + Storage and more. Scales up on pay-as-you-go and working on scale to zero.

Among all of these, my favorite one is Supabase. The reason is that Supabase is more than just a managed database provider—it's a game-changer that offers a comprehensive Backend as a Service (BaaS) solution. With the all-in-one package of PostgreSQL database, authentication, real-time subscriptions, RESTful API generation, and file storage, you don’t even need an additional backend service for your web application. It is usually not possible to achieve this even with API generation because of a simple truth:

Thanks to the powerful Row-Level Security (RLS) of Postgres, it is possible to enable secure and controlled access to the database via API generation. In a nutshell, RLS allows you to define policies that restrict access to rows based on user attributes. Here is one simple example (with PostgreSQL):

-- source: https://www.2ndquadrant.com/en/blog/application-users-vs-row-level-security/

CREATE TABLE chat (
    message_uuid    UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    message_from    NAME      NOT NULL DEFAULT current_user,
    message_to      NAME      NOT NULL,
    message_subject VARCHAR(64) NOT NULL,
);

CREATE POLICY chat_policy ON chat
    USING ((message_to = current_user) OR (message_from = current_user))
    WITH CHECK (message_from = current_user)

In human language, it says:

1. A chat row is only visible when the current user is either the sender or the receiver.
2. When a chat row is inserted or updated, the sender must be the current user.

After writing fine-grained access policies, the database will ensure that only authorized users can access specific data based on the defined rules. Now it’s safe to expose the API to the frontend while ensuring data privacy and security.

What more do I want?

In 2007, Microsoft released its server protocol to comply with antitrust regulators in Europe. However, they faced a challenge when many of their protocols lacked technical documents, so teams had to create them based on implementation. To ensure accuracy, Microsoft created a dedicated team to test these protocol specifications. To automate the testing process, an internal tool team developed various tool suites.

I joined the tool team as a fresh graduate, and they were developing a new tool to address this issue fundamentally. Mistakes in specifications may occur because they are completely segregated from the implementation. Sharing a single definition between them would ensure consistency and eliminate the need for testing.

That’s how a new DSL(domain-specific language) called Open Protocol Notation(OPN) is introduced. It is designed to enable developers to model protocol architecture, behavior, and data. It can be used to generate protocol schema files (IDL, WSDL, etc.), message parsers, simulations, and technical documents. I can still remember the time when I finished an OPN for an RPC protocol that was used to both generate the publicly released technical document and parse/display messages. And it's the first time I've heard what people would call it:

Policies are segregated from the application code

Of course, this is the entire point of implementing security with database policy, but it hurts when you want to have a wholistic view of your application because a big chunk of logic does not stay with your source code. The specific hurtings might be:

• Simplicity and Maintainability: Not only does it make the system harder to understand, but it also makes it difficult to debug and test.
• Portability: Not consistently supported by all database vendors.
• Version Control: Although database providers often have their own versioning mechanisms, they can’t be easily integrated with our application code in Git.

I think it’s the same reason why you seldom see people using stored procedures of databases nowadays despite all the benefits they offer.

Single source of truth solution

The first question we need to answer is:

If we want to move the access policies from the database to be alongside the application code, where is the best place for it?

It is intuitive to think of Object-Relational Mapping (ORM) as the bridge between application code and the database, providing a convenient abstract access layer for code. So that’s the approach we choose to do for the ZenStack OSS project we are building. It is built above the Prisma ORM, and one of its focuses is to add access control capability. Here's an example schema for the same "chat" scenario that we've seen previously:

// auth() function returns the current user
// future() function returns the post-update entity value

model User {
  id Int @id @default(autoincrement())
  username String
  sent Chat[] @relation('sent')
  received Chat[] @relation('received')

  // allow user to read his own profile
  @@allow('read', auth() == this)
}

model Chat {
  id Int @id @default(autoincrement())
  subject String
  fromUser User @relation('sent', fields: [fromUserId], references: [id])
  fromUserId Int
  toUser User @relation('received', fields: [toUserId], references: [id])
  toUserId Int

  // allow user to read his own chats
  @@allow('read', auth() == fromUser || auth() == toUser)

  // allow user to create a chat as sender
  @@allow('create', auth() == fromUser)

  // allow sender to update a chat, but disallow to change sender
  @@allow('update', auth() == fromUser && auth() == future().fromUser)
}

When the application code uses the ORM to talk to the database, proper filters are injected into queries and mutations to enforce the security rules. For example:

• When you do db.chat.findMany(), only chats related to the current user are returned.
• When you do db.chat.create({ fromUserId: 1, toUserId: 2, subject: 'hello' }), the ORM will reject your request if the current user does not have ID 1.

See, the RLS policy rules have been successfully moved to the application code. Some individuals may ask, "Wait a minute, what about this new schema file that's been introduced? Doesn't that break the principle of a single source of truth?”

My short answer for it is the schema file is also part of the application code. If you think about it, the RLS functionality is achieved without compromising simplicity, portability, and version control mentioned above. Additionally, the schema file is transpiled into Typescript code during the building process. This is just one of two different approaches to ORM: "code-first" like TypeoRM or "schema-first" like Prisma.

While it's possible to achieve this using the "code-first" approach, it may be difficult and non-intuitive for developers to express the desired access policy without a schema. The "schema-first" approach offers additional benefits through code generation. If you're interested, you can check out another post I wrote on this topic.

Is Code Generation a Bad Idea? 🤔

JS for ZenStack ・ Jun 27

#webdev #javascript #development #api

Finally

To be fair, I cannot deny some of the advantages that RLS has over our approach, such as the ability of policies to work across multiple applications and its language agnosticism. However, we all know that there is no one-size-fits-all solution. It always involves a trade-off that needs to be made. As long as some people believe it’s the right way to go, then all the current disadvantages are just issues to be resolved by us.

Are you one of them? 😉 If so, check out our Github for more detail:

https://github.com/zenstackhq/zenstack