Security barriers: Cheating on the planner

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.

Table of Contents

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:

-- funcs
CREATE FUNCTION slow_func(int4) RETURNS int4 AS $
        BEGIN
                EXECUTE 'SELECT pg_sleep(1)';
                RAISE NOTICE 'slow_func: %', $1;
                RETURN $1;
        END;
$ LANGUAGE 'plpgsql' 
        IMMUTABLE
        COST  10;

CREATE FUNCTION fast_func(int4) RETURNS int4 AS $
        BEGIN
                RAISE NOTICE 'fast_func: %', $1;
                RETURN $1;
        END;
$ LANGUAGE 'plpgsql' 
        IMMUTABLE
        COST  100;

-- funcs

CREATE FUNCTION slow_func(int4) RETURNS int4 AS $

BEGIN

EXECUTE 'SELECT pg_sleep(1)';

RAISE NOTICE 'slow_func: %', $1;

RETURN $1;

END;

$ LANGUAGE 'plpgsql'

IMMUTABLE

COST 10;

CREATE FUNCTION fast_func(int4) RETURNS int4 AS $

BEGIN

RAISE NOTICE 'fast_func: %', $1;

RETURN $1;

END;

$ LANGUAGE 'plpgsql'

IMMUTABLE

COST 100;

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:

-- test data

CREATE TABLE t_test (id int4);
INSERT INTO t_test SELECT * FROM generate_series(1, 20);

-- test data

CREATE TABLE t_test (id int4);

INSERT INTO t_test SELECT * FROM generate_series(1, 20);

Let's create a normal view with:

CREATE VIEW v AS
        SELECT  *
        FROM    t_test
        WHERE   id % 2 = 0
                AND fast_func(id) = 0;

CREATE VIEW v AS

SELECT *

FROM t_test

WHERE id % 2 = 0

AND fast_func(id) = 0;

Now let's run the code:

            SELECT * FROM v WHERE slow_func(id) = 0;

1	SELECT * FROM v WHERE slow_func(id) = 0;

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:

timing

SELECT * FROM v WHERE slow_func(id) = 0;
NOTICE:  slow_func: 2
NOTICE:  slow_func: 4
NOTICE:  slow_func: 6
NOTICE:  slow_func: 8
NOTICE:  slow_func: 10
NOTICE:  slow_func: 12
NOTICE:  slow_func: 14
NOTICE:  slow_func: 16
NOTICE:  slow_func: 18
NOTICE:  slow_func: 20
 id
----
(0 rows)

Time: 10012.580 ms

timing

SELECT * FROM v WHERE slow_func(id) = 0;

NOTICE: slow_func: 2

NOTICE: slow_func: 4

NOTICE: slow_func: 6

NOTICE: slow_func: 8

NOTICE: slow_func: 10

NOTICE: slow_func: 12

NOTICE: slow_func: 14

NOTICE: slow_func: 16

NOTICE: slow_func: 18

NOTICE: slow_func: 20

----

(0 rows)

Time: 10012.580 ms

We need 10 seconds to execute this one.

Adding a security barrier

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

CREATE VIEW v WITH (security_barrier) AS  
        SELECT  *
        FROM    t_test
        WHERE   id % 2 = 0
                AND fast_func(id) = 0;

CREATE VIEW v WITH (security_barrier) AS

SELECT *

FROM t_test

WHERE id % 2 = 0

AND fast_func(id) = 0;

We can execute the very same query again:

Timing is on.
NOTICE:  fast_func: 2
NOTICE:  fast_func: 4
NOTICE:  fast_func: 6
NOTICE:  fast_func: 8
NOTICE:  fast_func: 10
NOTICE:  fast_func: 12
NOTICE:  fast_func: 14
NOTICE:  fast_func: 16
NOTICE:  fast_func: 18
NOTICE:  fast_func: 20
 id
----
(0 rows)

Time: 0.685 ms

Timing is on.

NOTICE: fast_func: 2

NOTICE: fast_func: 4

NOTICE: fast_func: 6

NOTICE: fast_func: 8

NOTICE: fast_func: 10

NOTICE: fast_func: 12

NOTICE: fast_func: 14

NOTICE: fast_func: 16

NOTICE: fast_func: 18

NOTICE: fast_func: 20

----

(0 rows)

Time: 0.685 ms

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 Facebook or LinkedIn.

Security barriers: Cheating on the planner

Ordering restrictions

A little experiment using security barriers

Let's create a normal view with:

Now let's run the code:

Adding a security barrier

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

Bottom line on security barriers

Further important security info

Leave a Reply Cancel reply

Hans-Jürgen Schönig

Blog Tags

NEWSLETTER

Articles by our PostgreSQL Experts