When is a function leakproof?

The Limits of Row-Level Security

PostgreSQL's row-level security and security barrier views are designed to hide table rows from unauthorized users, typically to support multi-tenant applications that share a single table across multiple customers. The database-level enforcement promises to keep tenants from seeing or manipulating data that doesn't belong to them, independent of application logic.

Both features rely on a critical optimization technique involving functions marked LEAKPROOF. Understanding what this attribute actually provides — and what it doesn't — is essential for anyone building security-critical applications on PostgreSQL.

The Role of LEAKPROOF Functions

When PostgreSQL executes a query against a row-level security policy or a security barrier view, it must decide the order in which to apply conditions. Naively forcing all security conditions first would cripple performance. Consider a query like WHERE account_nr = 42 on a table with a primary key index. The equality operator int8eq would have to execute after the security filter, preventing use of the index entirely. The performance cost would render row-level security impractical.

PostgreSQL solves this problem by marking certain functions as LEAKPROOF: functions that reveal no information about their arguments other than through their return value. A function is not leakproof if it throws errors conditionally or includes argument values in error messages. The database considers such functions trustworthy enough to execute before security conditions, allowing the planner to choose efficient access paths.

LEAKPROOF indicates that the function has no side effects. It reveals no information about its arguments other than by its return value. For example, a function which throws an error message for some argument values but not others, or which includes the argument values in any error message, is not leakproof. This affects how the system executes queries against views created with the security_barrier option or tables with row level security enabled. The system will enforce conditions from security policies and security barrier views before any user-supplied conditions from the query itself that contain non-leakproof functions, in order to prevent the inadvertent exposure of data. Functions and operators marked as leakproof are assumed to be trustworthy, and may be executed before conditions from security policies and security barrier views.

Only superusers can set the LEAKPROOF attribute, and PostgreSQL has historically been conservative in applying it to system functions. On a standard v18 installation, only 345 functions are marked leakproof, mostly comparison operators on base types. In practice, this means that only B-tree index scans on such types can benefit from being pushed down past a security condition. The resulting performance loss is a common complaint from teams adopting row-level security.

Balancing Restriction and Performance

For views that serve a security purpose, you must also explicitly mark them as security barrier views:

PgSQL

This option restricts the optimizer's freedom to reorder conditions, which is why you apply it only to views that genuinely need it. Ordinary views don't warrant the performance penalty.

Decision-makers must carefully weigh how generously to mark functions as LEAKPROOF. Being liberal improves query performance significantly; being conservative keeps security guarantees intact. PostgreSQL's cautious tradition — the same mindset that prefers crashing over risking data corruption — favors the latter.

When "Secure" Isn't Bulletproof

Even with proper use of security barrier views and LEAKPROOF functions, row-level security has known limitations that may surprise developers expecting strict isolation. The PostgreSQL documentation is frank about the residual risks:

It is important to understand that even a view created with the security_barrier option is intended to be secure only in the limited sense that the contents of the invisible tuples will not be passed to possibly-insecure functions. The user may well have other means of making inferences about the unseen data; for example, they can see the query plan using EXPLAIN, or measure the run time of queries against the view. A malicious attacker might be able to infer something about the amount of unseen data, or even gain some information about the data distribution or most common values (since these things may affect the run time of the plan; or even, since they are also reflected in the optimizer statistics, the choice of plan). If these types of "covert channel" attacks are of concern, it is probably unwise to grant any access to the data at all.

A simple attack illustrates how easy such inference is. Querying a table protected by row-level security with an equality condition against an indexed column may succeed or fail depending on whether a hidden row matches. Even without the condition leaking through a non-leakproof function, the filter reports "Rows Removed by Filter" in an EXPLAIN (ANALYZE) output, directly revealing whether a matching row exists. No function leak is involved — the plan output itself carries the information.

Timing attacks provide another channel. Repeated queries against data that must be filtered take measurably longer than queries against empty result sets, allowing an observer to notice the difference. Diagnostic functions such as pg_stat_get_tuples_returned() can leak row counts when no concurrent users access the same table.

Attempts to Close the Holes

A recent proposal suggested restricting EXPLAIN (ANALYZE) to members of the pg_read_all_stats role whenever row-level security is involved. The idea was rejected because it addresses only one of several information channels. Limiting plan output wouldn't stop measure-timing or diagnostic-based inference, leading some to argue that plugging a single hole could give false confidence in the overall security of the feature. Restricting access to the primary performance tuning tool would also inconvenience many legitimate developers, possibly pushing them toward overly broad role assignments.

While the argument that partial fixes may be counterproductive isn't universally accepted, the consensus held. The proposed patch was withdrawn.

Practical Guidance

The safest way to think about row-level security isn't as defense against users executing arbitrary SQL. Rather, consider it a useful mechanism within a controlled application context, where the application itself generates all statements and user input only influences parameter values. Under such conditions, the security policies remain effective and the information channels no longer work against untrusted query writers. Treating row-level security as a complete isolation boundary in a public SQL environment overreaches what the feature was designed to provide.

What should LEAKPROOF really guarantee?

The documentation is blunt: a function cannot be LEAKPROOF if any error it raises can reveal information about its arguments. Taken literally, that standard disqualifies a surprisingly broad set of functions, and it is worth questioning whether the bar is set too high.

A concrete example comes from memory allocation failures. When PostgreSQL runs out of memory, error messages can expose the size of the failed allocation request:

If the amount of memory being requested depends on the size of an input datum, that error message leaks a clue about the datum's content. In theory, an attacker could exert memory pressure on the server, force an allocation to fail, and infer details about data that row-level security should hide. In practice, this is hard to weaponize: if you cannot run arbitrary SQL and do not control the server, how would you apply carefully calibrated memory pressure in the first place?

The bigger problem is that treating such out-of-memory errors as disqualifying would make it nearly impossible to mark almost any function that allocates memory as LEAKPROOF. The stricter the criteria, the fewer functions qualify, and the worse queries using row-level security perform. A more pragmatic rule would be: deny LEAKPROOF status only when an attacker can trigger the leaking error by supplying parameter values through an SQL statement that an application runs. Holding LEAKPROOF to a stricter standard risks making a well-designed feature impractical in real deployments.

A question of judgment, not engineering

The open questions here are more conceptual than technical. Should EXPLAIN (ANALYZE) access be restricted because it can help circumvent row-level security? Should the LEAKPROOF requirements be relaxed to ignore out-of-memory errors and others that cannot reasonably be triggered by maliciously crafted input values? Is a slightly lower standard for LEAKPROOF an acceptable trade-off for keeping row-level security fast enough to be worth using?

These are design trade-offs, not bugs. Feedback on the topic is welcome, and a discussion is already underway on the pgsql-hackers mailing list. For more on related pitfalls, see this article on the interaction between row-level security and view permissions.