Security barriers: Cheating on the planner

12.2013 / Category: / Tags: |

UPDATED August 2023: I was inspired by some previous posts with the idea of abusing PostgreSQL security barriers to cheat on the optimizer. I have constructed an example of how a security barrier can theoretically be used to speed up things under some very nasty circumstances.

WARNING - Please keep in mind: This is a demo – it is not meant to be a tuning guideline. It is merely an example showing how things work and what PostgreSQL is capable of. Please don't use this to tune your databases.

Ordering restrictions

If you write a query in PostgreSQL, the optimizer will try to find a smart way to execute the query. Ideally any SQL database will try to get rid of as much data as possible as soon as possible. In addition it tries to reduce costs by executing cheaper restrictions before expensive restrictions. Usually the user has no control over this process, and there is usually no point in trying to control this process. Remember, the core idea of SQL is that a user can send a query and the database tries to find out the best way – it is not the user who is supposed to arrange restrictions in a clever way.

A little experiment using security barriers

Just for fun (and nothing more) I came up with a small example showing how the order of those quals can be tweaked using security barriers. Here is an example.

First of all we create two functions. One function is going to be fast and one function is going to be slow:

To make the function slow, we simply call pg_sleep to make it wait for a while. In this example we have also set cost parameters to tell PostgreSQL how expensive a function is. In my example I have marked the slow function as cheaper so that the planner will execute it first under normal circumstances.

Then we can create some test data. 20 rows should be sufficient for our purpose:

Let's create a normal view with:


Now let's run the code:

What we see here is that the slow function is now executed quite frequently. Since the function filters out all rows, there is no need to post-filter anything using the fast function. We've misled PostgreSQL into calling the slow function first using our cost parameters, and now we can observe the following result:

We need 10 seconds to execute this one.

Adding a security barrier

Now, let's abuse a security barrier and see what happens:

We can execute the very same query again:

What we see now is that the query is a lot faster because PostgreSQL was not allowed to swap the filters.

Bottom line on security barriers

As I have stated before, security barriers are a SECURITY issue and not a performance issue. However, you have to take into consideration that the optimizer is restricted by these barriers, so that execution plans may change and performance might differ significantly.

Further important security info

Find out all about View Permissions and Row Level Security as of PostgreSQL v15 here.


In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments
CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    Would love your thoughts, please comment.x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram