The Illusion of Security with Row Level Security
Building a multi-tenant system for retail, especially one handling real money, demands stringent security. The author of this piece developed such a system using Supabase and PostgreSQL, enduring ninety-odd migrations. Over six weeks, three security reviews were conducted, and alarmingly, every identified vulnerability bypassed policies that were, by all accounts, correctly implemented. This highlights a critical misunderstanding: Row Level Security (RLS) is not an impenetrable fortress on its own. It is one layer in a complex security architecture. Beneath RLS lies the fundamental PostgreSQL privilege system, and above it sits the API layer, in this case PostgREST. The vulnerabilities emerged not from flawed RLS logic, but from the seams and interactions between these distinct layers.
The core issue is that RLS policies operate within the context of database sessions. They define what a specific user, or role, can see or do with data. However, they do not inherently dictate how an application layer interacts with the database, nor do they fully abstract away the underlying database permissions. This creates blind spots where data can be inadvertently exposed, even when the RLS rules themselves are technically accurate according to their own scope.
1. Revoking from anon is Misleading
A common security practice is to revoke execute permissions on functions for the anon (anonymous) role. The assumption is that if an anonymous user cannot execute a function, they cannot exploit it. However, this overlooks how functions are invoked and how permissions cascade. In PostgreSQL, if a function is created by a superuser or a role that has been granted EXECUTE permission on the function, and that function is declared SECURITY DEFINER, it executes with the privileges of its *owner*, not the caller. If the owner of the function is a privileged role (like postgres or a custom admin role), then revoking EXECUTE on that function from anon is effectively meaningless. The anon role can still trigger the function indirectly through another authenticated user's session, or if the function itself is called by another privileged object that anon *can* interact with. The correct approach involves not just revoking permissions from anon, but ensuring that the function owner is a role that has no elevated privileges and that SECURITY INVOKER is used where appropriate, forcing the function to run with the caller's permissions.
2. Functions Running with SECURITY DEFINER
This is closely related to the first point. When a PostgreSQL function is created with the SECURITY DEFINER clause, it executes with the privileges of the user who *created* the function, not the user who *calls* it. If a function is created by a superuser or a role with broad permissions, and it's intended to be used by less privileged users, it can become a vector for privilege escalation. An attacker could trick a less privileged user into invoking this function, thereby gaining access to data or performing actions that the less privileged user would otherwise be denied. Even if RLS policies are in place to restrict data access for the calling user, the SECURITY DEFINER function bypasses these RLS policies because it operates under a different, more privileged identity. The fix is to use SECURITY INVOKER whenever possible, ensuring functions run with the permissions of the user invoking them, and thus respecting RLS policies. If SECURITY DEFINER is absolutely necessary, the function owner must be a minimal-privileged role, and the function's internal logic must be meticulously audited to prevent misuse.
3. Stored Procedures and RLS Interaction
Stored procedures, much like functions, can also bypass RLS if not handled carefully. While RLS policies are typically applied to table access, stored procedures can encapsulate complex logic that interacts with multiple tables or performs operations that aren't directly subject to row-level restrictions. If a stored procedure is called by a user who has permission to execute the procedure, and the procedure itself performs actions that are not RLS-aware, data leakage can occur. For instance, a procedure might aggregate data from several tables and return a result set. If the procedure's logic doesn't explicitly filter data based on the calling user's permissions (which it often cannot directly do without explicit passing of user context), it can expose more data than intended. The key is to ensure that any stored procedure that accesses sensitive data is either SECURITY INVOKER and its internal queries are RLS-compliant, or that it explicitly passes the calling user's context to its internal queries, or that it is designed to return only aggregated, non-sensitive information that is already implicitly authorized by the caller's permissions.
4. The uuid-ossp Extension and Anonymous Access
This vulnerability is a specific instance of a broader class of problems related to database extensions and their default permissions. The uuid-ossp extension in PostgreSQL provides functions for generating universally unique identifiers (UUIDs). When this extension is enabled, and its functions are not properly restricted, an anonymous user might be able to call these functions. While generating a UUID might seem innocuous, it can be a stepping stone. For example, if the application relies on generated UUIDs for record identification and an attacker can flood the system with generated UUIDs, they might be able to infer patterns or probe system behavior. More critically, if the extension itself has underlying dependencies or triggers unintended side effects that are not RLS-protected, it can become a vector. The fix involves explicitly granting `EXECUTE` permission on the specific functions from the extension only to trusted roles, and revoking it from `anon` and other public roles. This ensures that only authorized parts of the application or specific users can leverage the extension's capabilities.
5. Leaking Data via pg_catalog
The pg_catalog schema in PostgreSQL contains system tables and views that provide metadata about the database, including information about tables, columns, functions, roles, and permissions. If a user has even minimal access to the database, they might be able to query pg_catalog to discover sensitive information. For example, they could list all tables and columns, revealing the names of tables that might contain sensitive data, even if they cannot access the data directly due to RLS. They could also potentially inspect function definitions or role privileges. While RLS policies are designed to protect data in user tables, they typically do not restrict access to pg_catalog unless explicitly configured to do so. The solution is to create a specific RLS policy that restricts access to pg_catalog for all roles except those that absolutely require it for administrative or operational purposes. Often, granting `USAGE` on the schema and `SELECT` on specific views is sufficient for application roles, while restricting broader access.
6. JSON Path Queries and RLS Bypass
Modern PostgreSQL supports JSON data types and powerful JSON path queries. These queries allow for granular access to data within JSON documents. However, RLS policies are often written for traditional relational data (rows and columns). When a query targets a JSON field and uses JSON path operators, the RLS policy might not be correctly applied to the extracted values. For instance, if a JSON column stores user preferences and an RLS policy is meant to restrict access to preferences based on user ID, a query that extracts a specific preference using a JSON path might bypass the RLS check for that specific preference value if the RLS policy is not designed to parse and filter within JSON structures. The fix requires ensuring that RLS policies are capable of inspecting and filtering data within JSON fields. This might involve using PostgreSQL's JSON functions within RLS policies or restructuring the data so that sensitive fields are stored in separate, RLS-protected columns.
The Seams are Where the Leaks Happen
The common thread across these six vulnerabilities is that they exploit the interaction points between different layers of the database and application stack. RLS is a powerful tool, but it needs to be implemented with a holistic understanding of the entire system. Developers must consider not only the RLS policies themselves but also the underlying PostgreSQL permissions, the behavior of functions and procedures (especially SECURITY DEFINER), the implications of database extensions, the visibility of system catalogs like pg_catalog, and the nuances of querying complex data types such as JSON. Each of these six issues demonstrates that a