In recent years, no-code and low-code platforms like FlutterFlow and WeWeb have gained significant traction in development. These platforms enable developers—and even non-developers—to build web and mobile applications quickly and with minimal coding. However, the ease of use provided by these platforms also makes securing data a critical concern, especially in applications that handle sensitive or user-specific information.

This is where Row-Level Security (RLS) plays a pivotal role.

What is RLS?

Row-Level Security (RLS) in PostgreSQL allows you to control access to specific rows within a table. Instead of applying permissions at the table level, RLS lets you define policies that filter or restrict the rows a user can view or modify.

Why RLS is Essential for Security in No-Code/Low-Code Development

In visual development platforms like FlutterFlow and WeWeb, where business users can directly interact with the app-building process, security policies must be enforced consistently to avoid data breaches and unauthorized access. With RLS, you can ensure that users only have access to the data they are authorized to see, without needing to add complex, custom logic on the front end.

Diagram Example

How RLS Enhances Data Security

Without RLS, developers often have to rely on front-end logic or middleware to filter sensitive data. This method, while functional, is prone to human error, and there’s a risk of exposing unintended data due to poorly configured permissions. RLS shifts this burden to the database level, offering a far more reliable and scalable way to secure data access.

By implementing RLS, you:

• Centralize data security at the database level, reducing reliance on client-side enforcement.
• Ensure compliance with data protection laws by guaranteeing that only authorized users can access specific records.
• Minimize risk of exposing sensitive data in no-code/low-code platforms, where the ease of creating applications could otherwise lead to security oversights.

Use Case

In this example, we will implement RLS on a profiles_roles table. Users can only select rows from this table if they are administrators (roleId = 1). However, instead of checking this directly in the RLS policy, we will create a secondary schema (back_safe), a view based on the profiles_roles table, and a custom function that will check whether a user is an admin. This setup allows us to avoid recursion and simplifies the management of complex access rules.

Table Structure

Here is the structure of the profiles_roles table that we'll use as an example:

Step 1: Create a New Schema (back_safe)

The first step is to create a new schema where we place the view and the function that checks the user’s admin status.

The schema will serve as a safe space where we define views and functions that do not interfere directly with the main public schema, helping to avoid potential recursion issues.

Step 2: Create a View Based on profiles_roles

Next, we create a view in the back_safe schema, based on the profiles_roles table, to provide a simplified way to check user roles.

This view acts as a mirror of the profiles_roles table, allowing us to perform role checks without directly querying the main table within the RLS policy, thus preventing recursion.

Step 3: Create a Custom Function to Check Admin Role

Now, we create a function in the back_safe schema that checks if a user is an admin (i.e., has roleId = 1).

- Function Purpose: This function checks whether the specified userId exists in the view_profiles_roles view with an admin role (roleId = 1).

-  SECURITY DEFINER: This ensures the function executes with the privileges of the function owner, not the caller, which is important for securely performing the role check.

-  Return Values: The function returns true if the user is an admin and false otherwise.

Step 4: Enable Row-Level Security on profiles_roles

Before creating an RLS policy, we need to enable row-level security on the table where we will apply the policies.

This action enables RLS functionality for the profiles_roles table. Once enabled, any policy created on the table will affect queries and access permissions.

Step 5: Create the RLS Policy

With the view and function in place, we can now create the RLS policy. This policy will allow users to perform SELECT queries on the profiles_roles table only if they are admins, as determined by the back_safe.is_admin() function.

Step 6: Testing the Policy

To verify that everything works as expected, we conduct the following tests:

1. Admin User:some text
   • Log in as a user with roleId = 1 and try querying the profiles_roles table. The query should return all records.
2. Non-Admin User:some text
   • Log in as a user without roleId = 1 and try the same query. The query should return no results, as the user is not allowed to see the data.

Conclusion

Implementing Row-Level Security (RLS) in PostgreSQL using a secondary schema, views, and custom functions offers a flexible and scalable solution for managing access control. In this guide, we demonstrated how to create an RLS policy that restricts access based on user roles, avoiding recursion by leveraging a secondary schema (back_safe). This approach is particularly useful in complex systems where access rules depend on user attributes stored in the database.

In addition to SELECT policies, similar policies can be applied for INSERT, UPDATE, and DELETE operations. By using the same approach—checking user roles through the back_safe.is_admin() function—you can define RLS policies that restrict who can add, modify, or delete records in the profiles_roles table based on the user's role.

By implementing policies for all CRUD (Create, Read, Update, Delete) operations, you ensure comprehensive access control and maintain security at all levels of your application.