Row-Level Security Patterns for Postgres

Overview

Row-Level Security (RLS) in PostgreSQL is a database mechanism for enforcing fine-grained, per-record access control within a table. This feature is crucial in multi-tenant applications and systems handling user-specific data, where a single database serves multiple tenants or users. In such scenarios, one small mistake – for example, forgetting to add a WHERE tenant_id = ... filter in an SQL query – can leak tenant A’s data to tenant B (ricofritzsche.me). This class of vulnerability, known as broken access control, is ranked as the top security risk in web applications (owasp.org). RLS directly addresses this risk by shifting the enforcement of data access policies from application code into the database engine itself. Introduced in PostgreSQL 9.5, RLS allows defining security policies on tables so that every SQL operation (SELECT, INSERT, UPDATE, DELETE) is automatically restricted to authorized rows (ricofritzsche.me). In essence, the database attaches an implicit WHERE clause to each query based on the active user or context (ricofritzsche.me). This ensures that a query only returns or modifies rows the current user (or tenant) is permitted to access, greatly reducing the chance of a developer oversight leading to a data leak (ricofritzsche.me).

RLS provides defense in depth for data access. Even if application code has a bug or missing check, the database will still prevent unauthorized records from being returned or modified (ricofritzsche.me). This flips the default security posture: instead of relying on every developer to consistently implement filters in every query, we centrally enforce tenant and user isolation at the data layer (ricofritzsche.me) (ricofritzsche.me). The result is a secure-by-default model where cross-tenant data access is blocked by PostgreSQL itself, drastically lowering the risk of accidental exposure. For AppSec engineers, RLS is an important tool to implement least privilege and need-to-know data access: each user or component should only see the rows relevant to its role or tenant. This mechanism is complementary to PostgreSQL’s standard role-based privileges (GRANT/REVOKE), adding an additional row-level filter on top of table-level permissions (www.postgresql.org). The combination of traditional ACLs with RLS means that even if a role has broad SELECT rights on a table, it will only see specific rows allowed by the RLS policy. By adopting RLS in a multi-tenant or multi-user application, organizations can significantly harden their data layer against horizontal escalation attacks such as Insecure Direct Object References (IDOR) (owasp.org).

Threat Landscape and Models

In any system where multiple users or tenants share the same tables, the threat model must consider unauthorized data access across boundaries. The primary threat is a malicious user (or an attacker who has compromised a user’s credentials) attempting to read or modify data belonging to another user or tenant. Traditionally, applications mitigate this by implementing access control checks in the business logic. However, history shows that these checks are often implemented inconsistently or can be bypassed via crafted requests (owasp.org). For example, an attacker might simply change a record’s ID in an API call (a classic IDOR attack) and retrieve another user’s information if the application fails to properly enforce ownership checks (owasp.org). In a multi-tenant SaaS application, such an exploit could expose one customer’s data to another, violating confidentiality and potentially breaching regulatory obligations. The threat landscape also includes insider threats (a user from Tenant A intentionally or unintentionally accessing Tenant B’s data) and web vulnerabilities like SQL injection being used to pivot across tenants. Without row-level isolation, a single injection flaw could expose an entire table of sensitive records rather than just a single victim’s data.

Row-Level Security alters this landscape by introducing a trusted enforcement layer in the database. Under RLS, each query is executed in a context that inherently limits which rows are visible or writable, according to policies defined by the security model. This means that even if an SQL injection attack occurs, the damage can be contained to the attacker’s own records or tenant space, assuming the database role used by the application is confined by RLS policies. The threat model for a system using PostgreSQL RLS must take into account how RLS could be bypassed. By design, superuser accounts and any roles granted the BYPASSRLS privilege ignore row-level security restrictions (www.postgresql.org). Thus, an attacker who gains database superuser access (through credential compromise or exploitation of a misconfiguration) could still see and manipulate all data. Similarly, if the application inadvertently uses a highly privileged database role (for example, the table owner or a superuser) for routine queries, then RLS policies will not be applied to those queries (www.postgresql.org). A robust threat model therefore assumes that RLS is effective only when the application connects with roles that are subject to RLS. Another potential threat vector is misuse of database functions or features: a SECURITY DEFINER function (which executes with the privileges of its creator) could circumvent RLS policy checks if not carefully designed, allowing a clever attacker to invoke it and retrieve data beyond their rights (www.postgresql.org). When designing an application’s security model, architects must enumerate these scenarios: What if an attacker can escalate their role? What if a developer writes a leaking function? What if RLS is mistakenly disabled or not yet enabled on a critical table? Effective use of RLS requires addressing these questions in the threat model, ensuring there are no hidden “back doors” that allow row-level policies to be subverted.

Common Attack Vectors

Insecure Direct Object Reference (IDOR) is one of the most common attack patterns RLS is meant to prevent. In an IDOR attack, an authenticated user simply changes a reference to a record they don’t own (for instance, guessing another user’s order ID in a REST API endpoint) and attempts to retrieve or modify it (owasp.org). Without RLS, such attacks rely entirely on application-layer checks. If any of those checks are missing or flawed, the unauthorized access succeeds. We have seen numerous real-world incidents where shallow validation or developer oversight led to massive data leaks across accounts. By enforcing per-row access control in the database, RLS ensures that an IDOR attempt fails by making the targeted row invisible to the attacker’s database role. The attacker’s query returns an empty result or an error, as if the record doesn’t exist, because the RLS condition (e.g., tenant_id = current_setting('app.tenant')) silently filters it out (ricofritzsche.me). This holds true even if an attacker finds an SQL injection vulnerability: when the injection executes in the context of a constrained RLS role, the injected query cannot access rows outside of that role’s permitted subset (it may execute, but will return no sensitive data beyond what that user could normally see).

However, misconfiguration or poor implementation of RLS can introduce new attack vectors. One such vector is the “all-or-nothing” query run by a privileged role. For example, an application might use a highly privileged account for background maintenance tasks or bulk reporting. If that same account (with RLS bypass) is ever used to handle a tenant-specific query (perhaps for performance reasons or by mistake), an attacker who can trigger that code path may retrieve data unrestricted by RLS. Attackers also look for inconsistencies in how RLS is applied. Consider a scenario where most tables in an app have RLS enabled except one newly added table; a savvy attacker or tester will target that unprotected table to extract cross-tenant information. Similarly, if certain SQL operations are not covered by any RLS policy (e.g., a policy exists for SELECT but not for UPDATE, and the database did not enforce a default block), an attacker might exploit that gap to alter data they shouldn’t. Another subtle attack vector involves role escalation within the database: if an attacker can find a pathway to change their PostgreSQL role (say, through an injection that calls SET ROLE or through abuse of a stored procedure), they might elevate to a role not restricted by RLS. While PostgreSQL’s privilege model normally prevents arbitrary role switching without the proper permissions, any misstep in role configuration (such as an overly broad GRANT or a forgotten default password for an auxiliary role) could be leveraged. Attackers may also attempt to create or abuse a SECURITY DEFINER function that selects data from a table without applying RLS (since such functions execute with the owner’s rights, RLS policies are bypassed within them (www.postgresql.org)). If the application allows creation or execution of user-defined functions (for example, in a self-service reporting feature), this becomes a serious vector to consider. In summary, the most common attacks against multi-tenant data—IDOR, SQL injection, privilege escalation—are all mitigated by RLS when properly configured, but any lapse in that configuration (using the wrong role, missing policies, privileged side-channels) can reintroduce the vulnerability.

Impact and Risk Assessment

The impact of failing to enforce row-level isolation in a multi-tenant environment is severe. A single successful exploit can lead to a data breach spanning multiple customers or users, turning what might have been an isolated incident into a systemic failure. From a risk perspective, not using RLS (or an equivalent mechanism) in a scenario that demands strict data partitioning is inviting a catastrophic outcome. For instance, without RLS, a trivial flaw in a single SQL query could allow an attacker to iterate over all user IDs and download an entire database table of private information. Such an incident would likely be classified as a high-impact breach, potentially triggering compliance violations (e.g., GDPR or HIPAA violations if personal data is involved) and resulting in reputation damage and legal liability. OWASP statistics indicate that broken access controls are not only common but also typically have a very high impact when exploited (owasp.org). In a multi-tenant SaaS context, exposing customer A’s data to customer B can undermine trust in the service and lead to contract cancellations, regulatory fines, or even class-action lawsuits.

Employing RLS significantly reduces these risks by minimizing the blast radius of any single application-layer failure. With properly configured RLS, even if an attacker compromises the application or finds a novel injection point, the database will not allow cross-tenant data to be accessed or altered (ricofritzsche.me). This built-in containment transforms many potential “serious” vulnerabilities into limited-scope issues. For example, an SQL injection might only reveal the attacker’s own data, or at worst, result in a denial of service for their account (if they corrupt their own records), rather than a full data breach. From a risk assessment standpoint, RLS shifts the likelihood of a mass data leak from probable (given enough time and growth of a codebase without it) to improbable, assuming no misconfiguration. That said, RLS is not a panacea. If implemented incorrectly, it can introduce availability risks (e.g., a misconfigured policy that denies all access to a table, causing application failures) or a false sense of security. An overlooked table or a wrongly granted privilege can nullify the RLS guarantees. Therefore, the residual risk when using RLS is tied to configuration management. Security practitioners must assess not just whether RLS is in use, but whether it’s consistently and correctly applied everywhere it should be. The impact of misapplying RLS can be subtle: for example, if a policy’s logic is too permissive due to an OR condition or a faulty check, it might inadvertently allow some unauthorized rows. Such mistakes could be sporadic and hard to notice until an incident occurs. In summary, the risk profile with RLS in place is greatly improved (fewer paths to mass data exposure), but the operational diligence required remains high. The organization must treat RLS configuration as critical security infrastructure: changes to policies should undergo threat modeling and testing, just as code changes do, because the consequences of a mistake in an RLS policy could be just as impactful as a bug in application code.

Defensive Controls and Mitigations

To secure a PostgreSQL database at the row level, several controls and best practices should be layered together. First and foremost, enable RLS on all tables that contain sensitive or tenant-specific data. By default, RLS is off for tables (unless you create a policy, in which case the table is implicitly enabled or the ALTER TABLE ... ENABLE RLS command has been issued) (pgdash.io). Enabling RLS activates the policy engine: if no policy exists, PostgreSQL falls back to a “deny by default” stance, meaning no rows can be accessed until a policy is added (www.postgresql.org). This default-deny behavior itself is a mitigation, as it prevents unforeseen data exposure in the interim between enabling RLS and defining proper policies. Next, create explicit policies for each table to govern SELECT, INSERT, UPDATE, and DELETE operations. These policies define the boolean expressions that determine row visibility. A common pattern is to tie rows to roles or tenants via an identifier column. For example, a policy might allow SELECT on the “orders” table only for rows where orders.tenant_id matches a value representing the current tenant’s identity. This value can come from the built-in current_user (if each tenant uses a distinct DB login role) or from a session variable that the application sets (if using a shared role). Both approaches are valid: one can either create a database role per tenant/user and rely on current_user in the policy condition (www.postgresql.org), or use a single application role and set a custom parameter like app.current_tenant on each connection to identify the tenant context (ricofritzsche.me). In either case, consistency in context-setting is crucial: ensure the application always sets the correct tenant or user identifier as soon as a session starts or a transaction begins, before any data queries are executed.

In addition to selecting the right policy conditions, one must also apply WITH CHECK constraints for data-modifying operations. A USING clause in an RLS policy governs which existing rows are visible or updatable, while a WITH CHECK clause governs what new data can be inserted or what modifications are allowed. For instance, a WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid) on an insert policy ensures that a client cannot create a new row under a different tenant’s ID (ricofritzsche.me). Without this, an attacker might insert or update a row and deliberately set its tenant_id to a value not equal to their own, thereby potentially smuggling data into another tenant’s domain. Proper WITH CHECK constraints mitigate this by enforcing that any row a user adds or alters still adheres to the same row-level rules as existing data. Another defensive control is to use permissive vs. restrictive policies wisely. By default, multiple RLS policies on a table are permissive and combine with OR logic (a row is accessible if it passes any policy) (www.postgresql.org). PostgreSQL also allows restrictive policies that combine with AND logic (a row must satisfy all policies) in cases where you want to globally restrict certain access patterns. For example, you might have a permissive policy allowing “own tenant data”, combined with a restrictive policy that forbids any data dated in the future or past (to prevent tampering with historical records). Using a restrictive policy is a way to inject a universal rule (like “no one can delete records from before today”) that must hold in addition to the tenant-specific rule. AppSec engineers should design policy sets that cover both general business constraints and tenant/user isolation, double-checking how those policies interact.

Beyond the RLS policies themselves, role management is a vital mitigation. The roles used by the application should not have the BYPASSRLS attribute and should not be superusers (www.postgresql.org). In practice, this means you create one or more roles for your app (e.g., app_user) that have only the minimum required privileges (SELECT/UPDATE on the needed tables, etc.), and you ensure those roles are subject to RLS. This may involve refraining from using the table owner role for routine queries. By default, the owner of a table is exempt from RLS enforcement (www.postgresql.org) (www.postgresql.org), so if your application connects as the table owner, it will bypass all policies. Mitigate this either by using a different, non-owner role for the application or by enabling FORCE ROW LEVEL SECURITY on the table to even subject the owner to RLS (www.postgresql.org). The latter can be useful if changing roles is not feasible, but it’s generally cleaner to separate the concerns (have an “admin” role that owns objects and is used for migrations, and a distinct “application” role for runtime that has no special exemptions). Finally, apply the principle of least privilege everywhere: do not grant roles more rights than necessary. If certain roles only need read access, avoid granting write permissions. If some administrative function needs to bypass RLS, isolate that in a controlled environment (for example, a stored procedure that a privileged role can execute, rather than giving the role carte blanche). The fewer entry points you have that bypass RLS, the easier it is to reason about the system’s security. Combined, these controls—enabling RLS, writing strict policies with both USING and WITH CHECK, avoiding privileged roles for data access, and least-privilege grants—form a robust mitigation strategy against horizontal data leaks.

Secure-by-Design Guidelines

Building RLS into an application from the design phase greatly increases its effectiveness and reduces potential integration problems. At design time, architects should decide how tenant or user identity will be mapped into the database layer. There are two primary secure design patterns for RLS in Postgres: role-based isolation and session-context isolation. In the role-based pattern, each tenant or user is associated with a dedicated database role (usually created at onboarding time). All queries run as that role will automatically have current_user (or current_role) set to the tenant’s context, and policies can simply use current_user in their conditions. For example, you might create roles tenant_A, tenant_B, etc., and a policy like USING (tenant_id = current_user) on your tables. PostgreSQL’s role inheritance can be used to collect common privileges: e.g., all tenant roles are members of a group role that has SELECT privileges on the tables, so you don’t need to grant privileges to each tenant individually. This design has the advantage that the database itself is aware of distinct identities for each tenant or user, and auditing which tenant accessed what is straightforward. However, it comes with the overhead of managing potentially many roles and handling role switching in the application (the application must login or SET ROLE for each tenant context, which can complicate connection pooling).

The session-context pattern, on the other hand, uses a common database role for the application and distinguishes tenants via a runtime parameter. This is often implemented by using PostgreSQL’s SET command to set a custom parameter, such as SET app.current_tenant = 'tenantA', after connecting. Policies then use the function current_setting('app.current_tenant') to retrieve this value and enforce that, for example, tenant_id = current_setting('app.current_tenant') for each row (ricofritzsche.me). This approach only requires managing one role (or a small set of roles by permission level, e.g., reader_role, writer_role), which simplifies pool management and role provisioning. It’s crucial in this design to ensure the application always sets the context variable and resets or changes it appropriately. A secure-by-design principle here is to tie the setting of the context to the authentication or request handling logic in the application. For instance, middleware in a web application can, upon each request, fetch the authenticated user’s tenant ID and execute a SET LOCAL app.current_tenant = '<tenant-id>' at the start of a database transaction. Using SET LOCAL within a transaction or using short-lived connections ensures the tenant context does not accidentally leak across requests. This prevents a scenario where data from one tenant might be visible in another tenant’s session due to a leftover variable. Designing the application’s database access layer to encapsulate this context management (for example, always using a function that wraps query execution and handles the context) will make it less likely for developers to forget these steps.

Another secure design guideline is to model your schema in a way that makes RLS policies straightforward and foolproof. This often means every table that requires row isolation has an explicit column identifying the tenant or owner of that row. Avoid designs where the tenant context is implicit or spread across multiple columns in a complex way, because that complicates policy logic and increases the chance of mistakes. If certain data is truly global and not tenant-specific (for example, reference data that all tenants can read), that can reside in tables without RLS or with a simple TRUE policy (allow all for SELECT). Keeping a clear separation between tenant-scoped tables and global tables in the schema will simplify the mental model. It’s also wise to adopt a naming convention or documentation practice indicating which tables have RLS enabled, so developers know that special rules apply when querying those tables. For instance, you might suffix tables with _sec or maintain an architectural decision record that lists all RLS-protected tables. From a secure-by-design perspective, always prefer a “deny by default, then selectively allow” philosophy. That means when writing policies, err on the side of being too restrictive and then consider if specific exceptions are needed, rather than writing a broad permissive policy and trying to patch holes with additional ones. PostgreSQL’s default behavior helps here: if no policy permits a row, it won’t be visible (www.postgresql.org). Embracing this, you might start with a single policy that only allows rows for the current tenant, and only add more complex conditions if absolutely required (and each additional condition should be scrutinized). Finally, incorporate RLS considerations into your user stories and requirements from the start. For example, when defining a story like “User from Tenant A can view their order history,” also define the negative: “User from Tenant A cannot view Tenant B’s order history.” This aligns with OWASP ASVS requirements for object-level access control (github.com). By capturing these as formal requirements, you ensure the development and QA process will cover verification of RLS (or equivalent logic) as a first-class acceptance criterion, rather than an afterthought.

Code Examples

In the following sections, we present code snippets in several languages to illustrate anti-patterns (bad practices) and secure patterns when using PostgreSQL RLS to enforce per-tenant and per-user data access. Each example focuses on how the application interacts with the database. While the core RLS logic resides in SQL policies, the application code must be written with RLS in mind – establishing the correct context and avoiding bypasses. We show both a vulnerable approach and a robust approach in Python, JavaScript, Java, .NET/C#, and in generic pseudocode.

Python

Insecure Example (Python): Consider a Flask web application that needs to fetch account records for the logged-in user. In an insecure implementation, the developer might forget to include a tenant or user filter in the SQL query and might also use a highly privileged database role for the connection:

import psycopg2

# Using a high-privilege role (e.g., the table owner or superuser) – RLS will be bypassed for this connection
conn = psycopg2.connect(dbname="appdb", user="app_owner", password="secret")

def get_account_data(user_id):
    # BAD: Missing explicit user filter and constructing SQL unsafely
    query = f"SELECT * FROM accounts WHERE user_id = {user_id};"
    cur = conn.cursor()
    cur.execute(query)  # If user_id is not properly validated, this is an SQL injection risk
    return cur.fetchall()

# Suppose an attacker passes user_id=5 (not their own ID) or manipulates the query string    
results = get_account_data(5)
print(results)  # This may return data for user 5, potentially an unauthorized disclosure

In this bad example, there are multiple issues. The code connects as app_owner, which likely owns the accounts table – since table owners are exempt from RLS by default (www.postgresql.org), any RLS policies on accounts won’t apply. This means the query could retrieve all accounts or any account regardless of the caller. Moreover, the query string is built via Python’s f-string with no parameterization, making it vulnerable to SQL injection (an attacker could input user_id = "5 OR 1=1" to fetch every row, for instance). Even without injection, the lack of a proper filter means a user can potentially supply someone else’s ID and the database (being queried as a privileged role with no RLS) will return those rows.

Secure Example (Python): A robust implementation uses a dedicated least-privilege role and sets the session context for RLS. We assume an RLS policy on accounts like USING (tenant_id = current_setting('app.current_tenant')::text) is in place. The application should set the app.current_tenant parameter for each session and use parameterized queries:

import psycopg2

# Connect as a non-owner role that is RLS-restricted (e.g., 'app_user' with no BYPASSRLS)
conn = psycopg2.connect(dbname="appdb", user="app_user", password="secret")
cur = conn.cursor()

# After user login, set the RLS context to that user's tenant or ID
tenant_id = get_authenticated_user_tenant()  # e.g., returns 'tenant_123' for Tenant 123
cur.execute("SET local app.current_tenant = %s", (tenant_id,))  # use SET local inside a transaction for safety

# Now perform queries normally; RLS will automatically restrict results
cur.execute("SELECT * FROM accounts;")  
rows = cur.fetchall()
for row in rows:
    print(row["account_id"], row["balance"])

In this secure example, the database role app_user is subject to RLS (not a table owner or superuser). The code explicitly sets a session variable app.current_tenant to the ID of the current tenant or user. Because the RLS policy references current_setting('app.current_tenant'), the subsequent SELECT will only return rows matching that tenant ID. There is no need to hard-code a WHERE clause on tenant_id in every query – the database enforces it. The use of SET local ensures the setting is transaction-scoped (preventing it from persisting accidentally across different requests on the same connection). Additionally, parameterized query usage (%s with a tuple) protects against injection when setting the context and in any other queries. The outcome is that even if a developer mistakenly writes cur.execute("SELECT * FROM accounts;") without a filter, the user still only sees their own accounts. This relieves the application code from constantly managing filters and makes security less error-prone.

JavaScript

Insecure Example (Node.js): In a Node environment (e.g., using the pg library), an insecure pattern might occur if the application either omits tenant filtering or uses a privileged role that ignores RLS. For instance:

const { Client } = require('pg');
// Connecting as an overly privileged role (possibly an admin or owner)
const client = new Client({ connectionString: 'postgres://app_owner:secret@localhost/appdb' });
await client.connect();

// BAD: No tenant context set, and no WHERE clause restricting the query
async function listOrders(userId) {
  const res = await client.query(`SELECT * FROM orders WHERE user_id = ${userId}`);
  return res.rows;
}

// If an attacker calls listOrders with another user's ID:
listOrders(42).then(rows => console.log(rows));

In this flawed code, the listOrders function tries to fetch orders for a given userId but assumes that providing the userId in the query is sufficient control. If app_owner is the table owner of orders, RLS policies on orders (if they exist at all) will not apply (www.postgresql.org), so the WHERE user_id = 42 condition is the only barrier. If the developer forgot or misconstructed that condition (or if an API allows calls to this function with arbitrary userId), a malicious tenant could retrieve another user’s orders. Furthermore, using template string interpolation for the query (`SELECT * ... ${userId}`) is dangerous: if userId were derived from untrusted input without validation, it could lead to SQL injection. Even if injection is not possible here, the main issue is relying on the developer to always include the correct WHERE clause and using a role that undermines RLS.

Secure Example (Node.js): A secure Node.js implementation leverages RLS by using a safe role and setting the session context for each connection or transaction:

const { Client } = require('pg');
// Connect as a role subjected to RLS (e.g., 'app_user')
const client = new Client({ connectionString: 'postgres://app_user:secret@localhost/appdb' });
await client.connect();

// After user authentication, set the tenant context for this session/connection
const tenantId = authenticatedUser.tenant;  // e.g., "tenant_123"
await client.query('SET app.current_tenant = $1', [tenantId]);

async function listOrders() {
  // No need to include tenant or user filter in the SQL; RLS policy enforces it
  const res = await client.query('SELECT * FROM orders');
  return res.rows;
}

// Usage: simply call listOrders() after context is set
listOrders().then(rows => console.log(rows));

Here, the app_user role is a least-privilege role that cannot bypass RLS. We immediately execute a parameterized SET command to establish the 'app.current_tenant' value for the session after a user logs in or at the start of a request. From that point on, the application can query orders (and any other RLS-protected tables) without worrying about tenant filters in the SQL; if the RLS policy on orders is defined (for example) as USING (tenant_id = current_setting('app.current_tenant')::text), PostgreSQL will internally ensure every query sees only rows for tenantId. The query text is much simpler (SELECT * FROM orders) and thus less prone to developer error. We also avoid string interpolation by using $1 placeholders – this is both good for security and for performance (the database can cache query plans, etc.). If an attacker somehow invokes listOrders() out of context, they will still only get rows for whatever tenantId was set; they cannot change that context unless they have access to execute another SET (which our application would only allow with proper authentication flow). Additionally, because the Node process might reuse the client for multiple requests (especially if using a connection pool), one must ensure that every time a connection is checked out for a tenant’s query, the tenant context is set accordingly. In practice, frameworks or custom code should reset the context when releasing connections or use separate connections per tenant request. This example assumes the context is correctly managed. With this design, even if a developer forgets to put a WHERE clause, the isolation holds, and Node’s role in security is mainly to reliably set the context and handle data, rather than enforce policy logic.

Java

Insecure Example (Java): In a Java application using JDBC or an ORM (like Hibernate), a common anti-pattern is performing data access without incorporating the user’s tenancy context, especially if using a shared connection. For example, using JDBC directly:

Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost/appdb", "app_owner", "secret");
try {
    // BAD: Query lacks tenant criteria, and the connection uses an RLS-exempt role.
    String sql = "SELECT * FROM customer_profiles";
    PreparedStatement ps = conn.prepareStatement(sql);
    ResultSet rs = ps.executeQuery();
    while (rs.next()) {
        // This will retrieve all profiles in the system if no application filter is applied
        System.out.println(rs.getInt("profile_id") + ": " + rs.getString("name"));
    }
} finally {
    conn.close();
}

This code connects as app_owner (the table owner of customer_profiles presumably). Because app_owner bypasses any RLS on that table, the query SELECT * FROM customer_profiles will return every row in the table (www.postgresql.org). The developer might have intended for some higher layer to filter the results by customer, but fetching all data into the app not only is inefficient but also completely defeats the purpose of data isolation (and if that filtering layer has a bug, the data could be sent to the wrong user). Even if the intention was to rely on an ORM that automatically adds filters, by using a raw query here without any filters, it circumvents those safeguards. This pattern is unfortunately seen when developers assume “the application will handle multi-tenancy” but then write low-level queries that ignore it. The impact is that any user who can trigger this code path would receive data they shouldn’t. Additionally, since we’re using app_owner, a malicious user who finds a way to run custom SQL (through a debug endpoint or injection) could extract any data from the database without restriction, RLS or not.

Secure Example (Java): A secure approach in Java leverages RLS by ensuring the connection uses a restricted role and setting the necessary context. If using a connection pool (e.g., HikariCP), one can execute a setup SQL each time a connection is borrowed. Here’s a simplified example using JDBC:

Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost/appdb", "app_user", "secret");
try {
    // Set the tenant context for this session or transaction
    String tenantId = currentUser.getTenantId();  // e.g., "tenant_123"
    try (PreparedStatement ps1 = conn.prepareStatement("SET LOCAL app.current_tenant = ?")) {
        ps1.setString(1, tenantId);
        ps1.execute();  // This will apply only for the current transaction if inside one
    }
    // Now query without explicit tenant filter; RLS enforces it in the database
    try (PreparedStatement ps2 = conn.prepareStatement("SELECT * FROM customer_profiles")) {
        ResultSet rs = ps2.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getInt("profile_id") + ": " + rs.getString("name"));
        }
    }
} finally {
    conn.close();
}

In this Java snippet, we connect as app_user, a role that does not own the tables and has no BYPASSRLS privilege. We then set app.current_tenant via a parameterized statement – note that we use SET LOCAL if this code is running inside a transaction (ensuring the setting will reset after commit). If not in a transaction, one could just use SET but must be careful to reset it or use a fresh connection per operation. After setting the context to the current user’s tenant, we execute the main query. The absence of a WHERE clause is intentional: the underlying RLS policy on customer_profiles (for example, USING (tenant_id = current_setting('app.current_tenant')::text)) will transparently limit the ResultSet to only those profiles belonging to tenantId. This means the developer can write simpler queries and let the database enforce security. It also means that if a developer accidentally writes an incorrect query (like forgetting a join condition or filter), it won’t leak data across tenants — it will either return nothing or just that tenant’s portion. This pattern works nicely with ORMs as well: for instance, one could set the context at the start of a Hibernate Session. Many connection pool implementations allow running an initialization SQL for each connection, where you could SET app.current_tenant to some default (like NULL or a placeholder) to ensure it’s always set to something; the application then must set the correct value before use. The key takeaway is that we rely on the database role’s restrictions and the session variable for security, rather than on every query manually appending tenant conditions. The Java code’s responsibility is mainly to ensure the context is set and to avoid using any accidentally privileged connection.

.NET/C#

Insecure Example (C#): In a .NET application using, say, Npgsql (the .NET PostgreSQL driver), an insecure data access might look like:

using Npgsql;
...
// Connecting with a superuser or owner account (bypasses RLS)
await using var conn = new NpgsqlConnection("Host=localhost;Database=appdb;Username=postgres;Password=secret");
await conn.OpenAsync();

// BAD: No tenant constraint in query, retrieving all data
string sql = "SELECT * FROM transactions";
await using var cmd = new NpgsqlCommand(sql, conn);
await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    Console.WriteLine($"{reader.GetInt32(0)} - {reader.GetString(1)} - {reader.GetDecimal(2)}");
}

This C# code connects as the postgres superuser. By definition, superusers completely bypass RLS (www.postgresql.org) – the policy engine will not even be invoked. Therefore, SELECT * FROM transactions will return every row in the transactions table. If this code was meant to be used in a context where only the current user’s transactions should be shown, it’s a critical flaw. Using the postgres role in an application is almost always an anti-pattern from a security standpoint: not only RLS, but all security controls can be overridden by a superuser. Even if we used a less privileged role but still omitted a WHERE clause, the application would have to filter results in memory or assume only authorized rows are requested. For instance, a developer might have intended to call cmd.ExecuteReaderAsync() and then use application logic like if (reader["user_id"] != currentUser) skip; – which is inefficient and error-prone. Or perhaps this is a background job that processes transactions for all users but was accidentally exposed to a user context. In any case, it’s insecure as written.

Secure Example (C#): A secure .NET implementation with Npgsql would use a dedicated RLS role and set the context as part of the session establishment, possibly leveraging Npgsql features to do so. Example:

await using var conn = new NpgsqlConnection("Host=localhost;Database=appdb;Username=app_user;Password=secret");
await conn.OpenAsync();

// Assume we have a tenant or user ID from the context of the request
string tenantId = currentUser.TenantId;  // e.g., "tenant_123"
// Set the session parameter for RLS (using a parameterized command to avoid injection)
await using (var setCmd = new NpgsqlCommand("SET app.current_tenant = @tid", conn))
{
    setCmd.Parameters.AddWithValue("tid", tenantId);
    await setCmd.ExecuteNonQueryAsync();
}

// Now run the intended query without worrying about the tenant filter
string sql = "SELECT * FROM transactions";
await using var cmd = new NpgsqlCommand(sql, conn);
await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    Console.WriteLine($"{reader.GetInt32(0)} - {reader.GetString(1)} - {reader.GetDecimal(2)}");
}

In this code, app_user is a non-superuser role that inherits no special rights except those granted (likely just the needed SELECT on transactions). We open the connection and immediately execute SET app.current_tenant = @tid with a bound parameter. Npgsql supports parameterizing even statements like this, which ensures that if tenantId contains any unusual characters, they are properly handled and not interpreted as part of SQL. After setting the context, the SELECT * FROM transactions will be processed by PostgreSQL with RLS in effect. If the transactions table has an RLS policy such as USING (tenant_id = current_setting('app.current_tenant')), then only rows for tenantId will be visible to app_user. The code then simply iterates over those results. Notice that the code no longer has to include any explicit check if (reader["tenant_id"] == currentUser) ... or similar – the database already guarantees it. One must be careful in a pooled environment: if this connection is returned to a pool, app.current_tenant might remain set. To mitigate this, you can either reset the parameter (e.g., SET app.current_tenant = NULL or use SET LOCAL within a transaction as shown in earlier examples). Another approach is to use multiplexing or a connection per scope so that context isn’t shared. The above code is straightforward and safe: it uses parameterization, a restricted role, and relies on the database for enforcement. Even if a developer accidentally uses the same connection for another user without resetting the context (which is a bug), they would at worst see the previous tenant’s data – which is obviously bad, but the issue would be quickly apparent in testing. Proper use of SET LOCAL in a transaction can eliminate that risk by scoping the tenant variable to a single transaction. The example as shown would typically be part of a larger pattern, perhaps with each web request using a fresh connection or ensuring a reset on check-in. With these measures, the .NET application benefits from simplified code and strong, server-enforced security for row access.

Pseudocode

To solidify understanding, consider the difference between a naive implementation and an RLS-aware implementation in high-level pseudocode:

Insecure Pseudocode:

function getAllTickets(currentUser):
    // BAD: No row-level restriction; fetching all data and expecting the caller to filter
    db = connectAs("ticket_owner", password)
    results = db.query("SELECT * FROM support_tickets")
    return results  // returns tickets for all users, ignoring currentUser

In this insecure version, getAllTickets connects as a powerful role (e.g., the table owner of support_tickets) and runs a query without any filtering. The expectation might be that the calling code or the front-end will filter out tickets not belonging to currentUser, but if that step is forgotten or fails, the raw results contain every tenant’s support tickets. This is a classic anti-pattern: relying on the application layer to enforce something that the database can and should enforce. It also unnecessarily transfers possibly large amounts of data. An attacker or even a regular user who receives this data could simply read entries not meant for them.

Secure Pseudocode:

function getUserTickets(currentUser):
    db = connectAs("app_user", password)  // connect as low-privilege RLS role
    db.execute("SET LOCAL app.user_id = ?", currentUser.id)  // set RLS context to this user's ID
    tickets = db.query("SELECT * FROM support_tickets")
    return tickets  // only tickets for currentUser are included, enforced by RLS

In the secure pseudocode, we connect as an app_user role that is restricted by RLS. We then execute a context-setting operation (the ? represents parameter binding of the user’s ID) to establish the user’s identity in the session. The subsequent query does not specify any user criteria, but thanks to an RLS policy on support_tickets like USING (owner_id = current_setting('app.user_id')::int), the database only returns rows belonging to currentUser.id. This design cleanly offloads the security decision to the database. The code is simpler (no need to compose dynamic WHERE clauses or post-filter the data) and less prone to error. If getUserTickets is called in any context, it will not leak data beyond the intended user. Even if a malicious user somehow invokes getUserTickets with another user’s identity, they would need to also have the ability to impersonate that user at the database level (which in this design would require compromising the application’s control flow since the variable is set server-side). The pseudocode underscores a general principle: with RLS, you set the context and issue generic queries, whereas without RLS, you rely on specific query filters or external filtering which are easy to get wrong.

Detection, Testing, and Tooling

Detecting whether RLS is properly enforced requires both configuration inspection and dynamic testing. From a configuration standpoint, developers and DBAs should verify that every table requiring row-level segregation has RLS enabled and the appropriate policies in place. PostgreSQL provides metadata views for this purpose: for example, querying pg_class.relrowsecurity and pg_policy can confirm RLS status and list policies. One might run a check during deployment or CI that all tables named in a certain convention (e.g., tables that have a tenant_id column) indeed have relrowsecurity = TRUE (meaning RLS is enabled). This can act as a backstop to catch a table that was created without enabling RLS. Additionally, pg_class.relforcerowsecurity indicates if FORCE RLS is on, which might be relevant for tables where the owner could otherwise bypass RLS. Such configuration checks can be automated with scripts or even a unit test that connects to a test database and queries the catalog for compliance.

Beyond configuration, dynamic testing is crucial. Security testers (or QA engineers in a multi-tenant application) should attempt to perform horizontal escalation: login as user/tenant A and try to access data of user/tenant B. This might be done via the application’s API (simulating an IDOR) or directly at the SQL level if appropriate. For example, a tester can open two sessions (as two different roles or with two different app.current_tenant settings) and ensure that queries in one session never return data for the other. Any results that violate this are red flags. Automated integration tests can incorporate this pattern: e.g., populate the database with tenant-tagged data, then, using the application or direct SQL with the app role, assert that Tenant A never sees Tenant B’s rows and vice versa. If the application has an admin mode that legitimately allows cross-tenant access, those operations should be tested separately and carefully (often via a different role that has such privileges by design).

Special tooling can assist in verifying RLS behavior. The pg_audit extension for PostgreSQL can log detailed information about queries, including which policies were applied or if any queries resulted in a filter. While pg_audit doesn’t directly tell you “RLS blocked X rows”, it will log SELECT statements and you might infer from context whether something was attempted. In a testing environment, one could intentionally attempt disallowed operations (like a user inserting a row with another tenant’s ID) and verify that PostgreSQL throws a rejection error (RLS violations typically manifest as an error like “new row violates row-level security policy for table...”). Capturing these errors in logs or test results confirms that the WITH CHECK constraints are effective. Another technique is using EXPLAIN on queries to see the plan – the presence of a “Filter: (tenant_id = current_setting(...))” in the plan indicates that RLS is being applied to the query plan. Penetration testers might also use SQL injection techniques to test RLS from the application side: for example, even if an injection is present, they may find that trying to UNION in data from another tenant yields no extra rows because RLS strips them out. This kind of test can demonstrate the value of RLS in containing injection impact.

It’s also important to test for the absence of RLS where it’s expected. A common oversight is forgetting to enable RLS on a new table or not having a policy that covers a particular type of query (e.g., maybe you wrote a SELECT policy but never created an INSERT policy, and then during an insert operation the database might actually allow it or deny it unexpectedly). Ensuring that test cases cover all CRUD operations is therefore vital. Tools like OWASP ZAP or custom scripts can be used to fuzz API endpoints with different IDs to see if data from another tenant ever slips through – essentially treating the multi-tenant enforcement as a black-box test. If the application is already deployed, monitoring and logging play a detection role: unusual access patterns, such as a regular user account suddenly retrieving a significantly larger set of data than normal, could indicate an RLS failure or bypass. While the DB won’t directly log “RLS bypass” (except if someone toggles the configuration), application-level logging that records the number of records returned for a query could be used to flag anomalies. For example, if a typical query returns ~10 rows for a user and suddenly one request returned 10,000 rows, that might warrant investigation for a potential policy misconfiguration that allowed it.

In summary, detecting issues with RLS involves verifying the presence and correctness of the configuration and performing offensive testing to ensure no cracks in the enforcement. Tooling exists in the form of Postgres’s system catalogs, audit extensions, and external testing frameworks. Incorporating these into your CI/CD and security testing pipeline is recommended so that any regression (like a table created without RLS or a policy changed incorrectly) is caught early, before it can be exploited.

Operational Considerations (Monitoring, Incident Response)

Operating a system with RLS requires awareness of how this security feature behaves under the hood, especially when things go wrong. Monitoring a live system for RLS-related issues might include tracking if any database roles unexpectedly gain the BYPASSRLS attribute or are used in contexts they shouldn’t be. In a well-configured environment, only specific administrative roles (DBAs) should have such privileges, and they should never be used by the application. It’s wise to have an audit log or monitoring on role changes: if someone attempts to ALTER ROLE to add BYPASSRLS or create a new superuser, it should trigger an alert. This guards against an attacker who somehow gets partial control and tries to elevate their DB permissions to defeat RLS.

From the application side, enabling detailed query logging in Postgres (e.g., log_statement = all, with log_line_prefix including user and maybe application name) can help trace which role executed what queries. If you suddenly see a sensitive query executed by an unexpected role (say a tenant role reading another tenant’s data via some join or function), that could indicate misuse. Monitoring tools like PgBouncer or custom middleware can enforce that certain roles (like an admin role) are never used in the connection pool that serves user requests. If an admin connection accidentally gets into the pool, RLS could be bypassed for whatever request lands on it. Thus, separating administrative and normal query channels is an operational best practice.

Performance monitoring is another consideration. RLS adds an extra predicate to queries, which in most cases is trivial (like checking tenant_id equality). However, if not indexed properly, this could cause performance issues. DBAs should monitor query performance to ensure that the RLS condition is supported by indexes (e.g., ensure an index on tenant_id exists on each partitioned table). The Postgres query planner generally does a good job, but complex RLS policies (especially those involving subqueries or function calls) can sometimes lead to inefficient plans. If an RLS policy uses a function that is not marked LEAKPROOF, the planner might be constrained in optimizations, potentially impacting performance for large tables (www.postgresql.org). Monitoring slow queries (via pg_stat_statements or APM tools) and attributing any slowness to the RLS component is necessary for capacity planning. In high-throughput multi-tenant systems, you might need to periodically review whether the RLS policies need tuning or if certain heavy administrative queries should be run with a privileged role outside of peak hours.

On the incident response side, when a security incident is suspected (e.g., a report that Tenant A saw Tenant B’s data), having audit trails is invaluable. If using RLS, one can leverage PostgreSQL’s session audit or manual logging to reconstruct what happened. For example, you might log all SET app.current_tenant commands and which user account triggered them. This way, if a context leaking issue occurred (the classic case being connection pool reuse without resetting tenant context), your logs would show the same connection ID or backend process serving two different tenant IDs in quick succession. That would immediately hint at a context leak flaw. In an incident, one of the first steps is to verify whether RLS was enabled and active for the affected tables. If not, the containment is likely lost and the focus shifts to how much was accessed. If yes, you investigate how the policy might have been circumvented: was there a misconfiguration (like using an owner role)? Did an admin maybe disable RLS temporarily (and forgot to re-enable)? Note that PostgreSQL does not presently log RLS policy evaluations or denials specifically, so you rely on inference: e.g., log the count of rows returned by queries (the application can do this) and see if any are abnormally high. Also, if you have a metric for “rows returned per query per user”, a spike might indicate someone accessed more than they should (though it could also be a legitimate large data export by that user).

In terms of playbooks, an incident response team should include steps to examine RLS policies: dump the current policies (\d+ tablename in psql shows policies), check if any recent schema migrations touched them, and verify that the application was using the correct roles. A real-world consideration is that sometimes developers disable RLS during troubleshooting or migrations and forget to re-enable it. Operationally, it’s prudent to have guardrails: for example, a migration script could assert that RLS is enabled at the end (especially if it had disabled it at start for bulk loading). If an incident arises from a misstep like that, part of incident response is to identify the lapse in process and add checks to prevent it in the future. Lastly, consider backup and data export procedures: when backing up a multi-tenant database, RLS doesn’t apply (since typically a backup runs as a superuser to dump all data). Ensure that backups are secured and that any ad-hoc queries for analytics or support purposes are done carefully. If support engineers run queries to help a tenant, they should ideally also be constrained by RLS or use tooling that enforces tenant filters, otherwise the benefits of RLS can be undone by well-intentioned internal users.

Checklists (Build-time, Runtime, Review)

Build-Time Security: During development and build, treat RLS as a fundamental part of the schema. When adding a new table that will hold tenant- or user-scoped data, immediately plan its RLS policy. For example, as soon as a projects table with a tenant_id column is created, the developer should also write a migration to ALTER TABLE projects ENABLE ROW LEVEL SECURITY and CREATE POLICY ... ON projects USING (tenant_id = current_setting('app.current_tenant')) .... The build process (or code review) should include verifying that such migrations exist for new tables. Developers can adopt a checklist item: “Does this new database object require RLS and, if so, have I added it?” Additionally, build-time configuration should ensure that the application’s database user is appropriately limited. In practice, that means the application’s DATABASE_URL or connection config uses the intended app role and not postgres or an owner role. Mistakes in configuration (especially in dev/test environments) can sometimes slip into production, so it’s worth having a sanity check in the code that perhaps logs the current database user on startup and flags if it’s running as a superuser. Finally, any data-access code written (whether raw SQL or via an ORM) should be reviewed for assumptions about multi-tenancy. If a developer is writing a raw query for performance reasons, the review should ensure they either include necessary filters or, ideally, that the table in question has RLS so that even if the query is broad, it won’t violate security. Essentially, before merging code, ask: “Are we relying on RLS here, and if so, did we set the context? If we aren’t relying on RLS, are we sure we included all needed checks in the query or code?”

Runtime Security: At runtime, consistency and correctness of context is king. The operations team should ensure that each application instance or request properly sets the RLS context (like tenant ID) prior to executing business queries. This might involve configuring middleware or data access layers that automatically do SET app.current_tenant. It’s useful to have health checks or diagnostics that confirm the RLS context is set – for instance, a debug endpoint that returns the result of SHOW app.current_tenant from the database can verify that the application did, in fact, set it for a given session (only accessible by admins, of course). Another runtime concern is connection pooling: as described earlier, ensure that connections are not reused across tenants without a reset. If using a pool, one configuration is to use psql “application_name” parameter to tag connections by tenant or ensure a reset query (DISCARD ALL or RESET app.current_tenant) is issued on check-in. Many managed environments (like PaaS or frameworks) allow specifying a reset query. This should be verified in production because a misconfigured pool could be silently bleeding data between users. Monitoring should also watch for any anomalies like the ones discussed: queries that return unusually large results or any log entries indicating an unauthorized action. Some organizations implement a “canary” user approach: a dummy tenant with known data and then periodically attempt to access that data while logged in as another tenant, expecting no results. If that test ever returns the canary data, it indicates a breach in the isolation.

Security Review/Ongoing Assessment: Periodic reviews of the RLS configuration should be part of the security assessment of the application. This includes reviewing all RLS policies for correctness and necessity. Over time, business requirements evolve and sometimes exceptions creep in (“we need to allow a support role to see all data” etc.). Each of these should be scrutinized. For example, if a new policy is added to allow a support staff role to bypass tenant filters (USING (true) FOR SELECT TO support_role), that’s a significant change that should go through threat modeling. A security review checklist might entail: (a) List all tables with RLS and ensure the policies make sense for current requirements; (b) List any tables without RLS that perhaps should have it (maybe new tables were added without anyone remembering to add RLS – this can happen if new developers are unaware); (c) Check that no roles that the application uses have gained excessive privileges. It’s also important to review any stored procedures or database functions: ensure none of them are marked SECURITY DEFINER unless absolutely necessary, and if they are, verify that their code internally applies checks or only exposes non-sensitive aggregate info. If possible, use static analysis tools on the codebase to find raw SQL queries – then confirm that each either is safe via RLS or includes proper filtering. Code reviews should continue to emphasize that any data access either leverages the central RLS or contains its own access control logic (and the latter should be an exception, not the norm). By maintaining these build-time, runtime, and periodic review practices, the system can sustain a high assurance that RLS is doing its job effectively and no regressions or misconfigurations slip in.

Common Pitfalls and Anti-Patterns

Implementing RLS in PostgreSQL provides strong security guarantees, but there are several common pitfalls and anti-patterns that can undermine its effectiveness:

Using a superuser or owner role for all application queries: This is perhaps the most prevalent anti-pattern. Developers might use the default postgres user or a database owner account in the application connection string for convenience. As noted, these roles bypass all RLS policies (www.postgresql.org). The pitfall is that everything “appears to work” (the queries run, data comes back), but no filtering is actually happening. The application may not notice during development, but in production this means there’s zero row-level isolation. The correct approach is to use a role without superuser privileges and without BYPASSRLS (the default for normal roles) for the application. If an application must perform some admin-level tasks, those should be done using a separate connection or role, never intermixed with general tenant queries.

Forgetting to enable RLS on a table or assume that creating a policy is enough: A subtle pitfall is assuming that defining a policy on a table automatically turns RLS on. In PostgreSQL, you must both enable RLS and have at least one policy; if RLS is not enabled, the policies are ignored, and if RLS is enabled but no policy exists, PostgreSQL defaults to denying all access (www.postgresql.org). Sometimes, a developer might create a policy via CREATE POLICY but forget to run ALTER TABLE ... ENABLE ROW LEVEL SECURITY. The policy will quietly not be enforced in that case, which is dangerous because one would think the protection is active. Conversely, enabling RLS without creating any policy will block all accesses (safe from a confidentiality standpoint, but likely causing incidents in your application as all queries start failing). The best practice is to always do both steps together. Many ORMs or migration tools allow running raw SQL – it’s important to include these commands in migrations and not rely on manual DB changes that could be forgotten in a different environment.

Incorrect or overly broad policy logic: Writing the RLS policy expression is a critical step, and mistakes here can reintroduce security issues. An anti-pattern is using a condition that doesn’t properly tie the row to the user’s context. For instance, imagine a policy USING (company_name = current_setting('app.company_name')) where company_name is a non-unique field. If there are multiple tenants with the same company name, this policy would allow each of them to see each other’s rows inadvertently. The correct design would use a unique tenant identifier. Another example: using USING (tenant_id = ANY(%s)) for some list of tenants, but accidentally allowing a public role or a role inheritance such that the ANY clause includes tenants it shouldn’t. Simpler is safer; policy conditions should be straightforward equality checks to an identifier associated unambiguously with the current user. Overly broad policies, like a policy that unintentionally always returns true, are obviously bad (e.g., USING (true) applied to all roles would defeat RLS entirely – this might be done in error if someone tries to temporarily “open up” a table and forgets to remove it).

Missing WITH CHECK on policies for write operations: As discussed, having a USING clause without an equivalent WITH CHECK can allow malicious or accidental breaches of data integrity. A known pitfall is updating the tenant identifier of a row: if the policy permits a user to UPDATE a row (because the row initially satisfied USING by belonging to them), but there’s no WITH CHECK, they could change the tenant_id of that row to someone else’s. After the update, they might lose visibility of it (since it no longer satisfies USING for them), but now that row has effectively moved tenants. The victim tenant might start seeing a mysterious extra row that doesn’t actually belong (if their role can see it). This is not just a data integrity problem, it’s a security issue – data has “leaked” into another tenant’s view. Always include WITH CHECK (tenant_id = current_setting('app.current_tenant')) for any insert or update policy that allows users to create or modify rows. Another related anti-pattern is not using separate policies for different commands when needed. For example, you might allow broad SELECT access (maybe a support role can see all rows) but only narrow UPDATE (users can only update their own rows). This can be done by specifying FOR SELECT and FOR UPDATE separately; forgetting to do so might accidentally allow updates as broad as selects.

Not handling connection pooling context properly: Many have tripped over this operational pitfall. An application might pass all tests in a single-connection scenario, but when deployed with a pool, a connection that was set to Tenant A might be reused for Tenant B without resetting app.current_tenant. Suddenly, Tenant B’s request is executed with Tenant A’s context and RLS dutifully enforces... Tenant A’s filter, thereby showing Tenant A’s data to Tenant B. This is a real risk in environments like web servers where connections are reused. The anti-pattern is neglecting to reset or properly scope the context. The remedy, as noted, is using SET LOCAL in transactions or ensuring a RESET on connection release. This is less of a Postgres misconfiguration and more of an application infrastructure issue, but it’s common enough to highlight: RLS is only as good as the context you provide it, so you must manage that context life cycle diligently.

Using SECURITY DEFINER functions or leaking data through side channels: An advanced pitfall is when developers use functions or stored procedures that inadvertently bypass RLS. For example, a function defined as SECURITY DEFINER (running with, say, the owner’s rights) that performs a query like SELECT COUNT(*) FROM some_table will ignore RLS and count all rows. If that function is callable by regular users, they could infer information (like how many total records exist, or by subtracting their known count, infer others). Or worse, a function might return a set of rows from a table – if not carefully written, it could act as a loophole. The guideline should be: avoid SECURITY DEFINER for anything that reads tenant data, unless absolutely necessary, and if you must use it (perhaps for maintenance or performance reasons), ensure it implements its own filtration logic or checks the caller’s permissions. For instance, the function might accept a tenant_id as argument and internally enforce that it only returns that tenant’s data. But at that point, one should question if a definer function is needed at all or if a normal RLS-protected query would suffice. Leaking data can also happen through error messages or performance differences (timing side-channels) in extreme cases, but those are less direct. The main takeaway is to be cautious of any code running with elevated rights in the database.

Blind trust in RLS without testing: Finally, an anti-pattern from a process perspective is assuming that once RLS is enabled, everything is automatically safe. Developers might become complacent and not test access control thoroughly, thinking “the database has got it covered.” While RLS does provide a big safety net, it’s not a reason to skip testing or to ignore logs. There could be subtleties – maybe a particular query uses a cross-join or a lateral join that causes an unexpected behavior with policies (usually RLS still applies per table scan, but complex queries should be tested). If the application is using row security, it should be part of the test plan to simulate malicious scenarios. Teams should avoid the mentality of “we’ll just turn on RLS and forget it”; like any security control, it requires validation and periodic review.

Avoiding these common pitfalls is a matter of careful configuration, thorough understanding of how RLS works, and disciplined operational practices. When in doubt, consult PostgreSQL’s documentation and case studies of RLS usage to ensure you’re following a proven pattern rather than an anti-pattern.

References and Further Reading

PostgreSQL Official Documentation – Row Security Policies: The PostgreSQL documentation provides the authoritative reference on RLS configuration and behavior. See the chapter on Row Security Policies in the official docs (PostgreSQL 15 & 16) for details on enabling RLS, writing policies, and the effects of role privileges on RLS enforcement (www.postgresql.org) (www.postgresql.org).

OWASP Top 10 – Broken Access Control: The OWASP Top 10 2021 report highlights Broken Access Control as the most significant web application security risk. The discussion and examples (including IDOR) underscore why controls like RLS are necessary to prevent users from viewing or acting on data outside their authority (owasp.org) (owasp.org).

OWASP ASVS 4.0/5.0 – Access Control Verification: The OWASP Application Security Verification Standard includes requirements for object-level access control. Notably, ASVS 4.2.1 emphasizes protecting data against IDOR-style attacks (e.g., one user accessing another’s record) through systematic checks (github.com). RLS can be seen as an implementation meeting these requirements at the data layer.

“Mastering PostgreSQL Row-Level Security (RLS) for Rock-Solid Multi-Tenancy” by Rico Fritzsche (2023): An in-depth article exploring how to use RLS for multi-tenant architectures. This resource covers the rationale for RLS (ricofritzsche.me), step-by-step setup with examples, and important nuances like using current_setting for session context and the differences between separate roles vs. a shared role with context variables. It also discusses performance considerations and how RLS provides a secure-by-default approach to tenant isolation (ricofritzsche.me).

pgDash Blog – “Exploring Row Level Security in Postgres”: A tutorial-style article that introduces RLS and walks through enabling it, creating roles, and setting policies in a simple scenario. It explains the interaction between traditional GRANT privileges and RLS, and shows examples of enabling RLS on a table and how the ALTER TABLE ... FORCE ROW LEVEL SECURITY option works to include owners (pgdash.io). This is a good starting point for newcomers to RLS to see it in action.

Stack Overflow – RLS usage Q&A: Several Q&A threads (e.g., “Using PostgreSQL row level security (RLS) policies with current_setting() function”) provide insight into common issues developers face when first adopting RLS. These include troubleshooting why a policy isn’t working (often due to missing ENABLE RLS or role misconfiguration) and patterns for using current_setting safely. While not authoritative like the docs, these can be enlightening for specific gotchas.

PostgreSQL Mailing List Archives – RLS Discussions: The PostgreSQL hackers mailing list archives contain discussions around the design and edge cases of RLS. For instance, threads from when RLS was introduced (circa 2015) debate certain trust assumptions and behaviors. Reading these can give a deeper understanding of why RLS works the way it does (e.g., decisions about owners bypassing by default, or how leakproof functions are treated) for those interested in the implementation philosophy.

NIST Guidelines on Database Security: NIST Special Publication 800-53 (Access Control family) and related documents discuss principles like least privilege and isolation in multi-user systems. While they don’t mention PostgreSQL RLS specifically, they provide the high-level objectives that RLS fulfills. These can be useful for compliance mapping – showing that row-level controls help satisfy certain regulatory or security framework requirements for separation of data.

By reviewing the above materials, AppSec engineers and developers can gain both broad and deep understanding of row-level security. From formal documentation to community knowledge and real-case tutorials, the references offer a well-rounded perspective to implement RLS correctly and effectively.