December 15, 2015 at 15:19How PostgreSQL security_barrier views work
- Transfer
You may have noticed that support for security_barrier views has been added in PostgreSQL 9.2. I looked into this code with the aim of adding automatic update support for them, as part of the evolving line-level security work for the AXLE project , and I thought I'd try to explain how they work.
Robert has already explained what the benefits of such representations are and what they protect against (in addition, this was still discussed in “ What's New in PostgreSQL 9.2 ”). Now, I’d like to move on to how they work and discuss how security_barrier views interact with automatically updated views.
A simple regular representation is expanded in macro form as a subquery, which is usually then optimized by putting out its predicate and adding it to the conditions of the contained query. This can be made more clear with an example. The table is given:
and presentation:
type request:
will be converted inside the request handler into the following form:
which the optimizer will then turn into a query executed at once, taking out the subquery and WHERE conditions in an external query:
You won’t be able to see instant queries directly and they never exist in real SQL, but you can see this process by including debug_print_parse = on , debug_print_rewritten = on and debug_print_plan = on in postgresql.conf. I will not reproduce parsing and planning trees here, as they are rather bulky and easy to generate based on the above examples.
You might think that giving someone access to a view without giving access to the table itself will not allow them to see even rows. In fact, it looks like the truth:
but when you look at the plan, you can see the potential problem:
The subquery of the view has been optimized and its identifiers have been rendered into an external query.
In SQL, AND and OR are not ordered. The optimizer / executor has complete freedom in choosing a branch to run, which they consider to be faster in terms of issuing a response and will probably allow them to avoid starting other branches. Those. if the scheduler thinks n <4 is much faster than n% 2 , he will run it first. It looks harmless, right? Try:
Oops! As you can see, a function with a user-supplied predicate was considered cheaper to run than other tests, so it skipped all the lines before the presentation predicate ruled out the unsuitable ones. A malicious function may use the same trick to copy strings.
Security_barrier views correct this by causing the view conditions to be executed first before any conditions created by the user are applied. Instead of expanding the presentation and staking out any conditions for the presentation into an external request, they replace the link to the presentation with a subquery. This subquery has the security_barrier flag set across the entire range of its entry in the table, which tells the optimizer that he should not touch the subquery, or make conditions out of it, as he would in a normal case.
Thus, a performance with a protective barrier:
we will get:
The query execution plan should tell you what is happening, although it does not show the attribute of the security barrier in the output of the explanation. The nested subquery forces to scan t with the conditions of the subquery of the view, after which the conditions of the function written by the user are satisfied on the received data.
But. Wait a second. Why does the user-defined predicate n <4 also appear in the subquery? Isn't that a potential security hole? If n <4 is omitted, then why not f_leak (secret) ?
The explanation for this is that the < statement is marked as LEAKPROOF . This attribute indicates that the given operator, or the function will not allow information to be trusted, can accordingly be safely applied to security_barrier views. For obvious reasons, you cannot set the LEAKPROOF attribute as a regular user:
superusers can do whatever they want and they don’t have to resort to tricks with the functions of information leakage in order to pass the protective barrier of representations.
Regular views in PostgreSQL 9.3 are automatically updated , but security_barrier views do not imply “simplicity”. This is because view updates rely on the ability to remove the view subquery, turning the update into a regular table update. The whole point of security_barrier submissions is to not allow this exception to condition submissions. UPDATE currently cannot work directly with a subquery, so PostgreSQL will reject any attempt to update the security_barrier view:
This is exactly the restriction that I am interested in canceling, as part of the work of developing line-level security for the AXLE project . Kohei KaiGai has done a tremendous amount of work with row-level security, and things like security_barrier and LEAKPROOF have come a lot from his job of adding row-level security to PostgreSQL. The next challenge is how to deal with updating the protective barrier safely and in such a way that it is serviced in the future.
You may wonder why we use subqueries for this. I thought about it. The short version is that we should not, but if we do not use subqueries, we will instead have to create new sort-sensitive variations of the AND and OR operators and teach the optimizer that he cannot pass conditions through them. Since views are already extended by subqueries, it is much easier to mark subqueries as fences that do not allow you to retrieve / add data to them.
PostgreSQL already has a simplified ordered operation - CASE . The problem with using CASE is that no operations can cross CASE boundaries , even LEAKPROOF. As well as the optimizer and can not make decisions about the use of indexes? based on the expression inside the CASE block. So if we used CASE as I asked about it here, we would never have been able to use the index to satisfy the user-provided condition.
Security_barrier support was added in 0e4611c0234d89e288a53351f775c59522baed7c and is enhanced by LEAKPROOF support in cd30728fb2ed7c367d545fc14ab850b5fa2a4850 . Thanks go to commit notes. Thanks to everyone who participated.
PS. The article is relatively old, but important as an introduction to the translation of the next article.
Robert has already explained what the benefits of such representations are and what they protect against (in addition, this was still discussed in “ What's New in PostgreSQL 9.2 ”). Now, I’d like to move on to how they work and discuss how security_barrier views interact with automatically updated views.
Ordinary views
A simple regular representation is expanded in macro form as a subquery, which is usually then optimized by putting out its predicate and adding it to the conditions of the contained query. This can be made more clear with an example. The table is given:
CREATE TABLE t AS SELECT n, 'secret'||n AS secret FROM generate_series(1,20) n;
and presentation:
CREATE VIEW t_odd AS SELECT n, secret FROM t WHERE n % 2 = 1;
type request:
SELECT * FROM t_odd WHERE n < 4
will be converted inside the request handler into the following form:
SELECT * FROM (SELECT * FROM t WHERE n % 2 = 1) t_odd WHERE n < 4
which the optimizer will then turn into a query executed at once, taking out the subquery and WHERE conditions in an external query:
SELECT * FROM t t_odd WHERE (n % 2 = 1) AND (n < 4)
You won’t be able to see instant queries directly and they never exist in real SQL, but you can see this process by including debug_print_parse = on , debug_print_rewritten = on and debug_print_plan = on in postgresql.conf. I will not reproduce parsing and planning trees here, as they are rather bulky and easy to generate based on the above examples.
Problem Using Security Views
You might think that giving someone access to a view without giving access to the table itself will not allow them to see even rows. In fact, it looks like the truth:
regress=> SELECT * FROM t_odd WHERE n < 4;
n | secret
---+---------
1 | secret1
3 | secret3
(2 rows)
but when you look at the plan, you can see the potential problem:
regress=> EXPLAIN SELECT * FROM t_odd WHERE n < 4;
QUERY PLAN
---------------------------------------------------
Seq Scan on t (cost=0.00..31.53 rows=2 width=36)
Filter: ((n < 4) AND ((n % 2) = 1))
(2 rows)
The subquery of the view has been optimized and its identifiers have been rendered into an external query.
In SQL, AND and OR are not ordered. The optimizer / executor has complete freedom in choosing a branch to run, which they consider to be faster in terms of issuing a response and will probably allow them to avoid starting other branches. Those. if the scheduler thinks n <4 is much faster than n% 2 , he will run it first. It looks harmless, right? Try:
regress=> CREATE OR REPLACE FUNCTION f_leak(text) RETURNS boolean AS $$
BEGIN
RAISE NOTICE 'Secret is: %',$1;
RETURN true;
END;
$$ COST 1 LANGUAGE plpgsql;
regress=> SELECT * FROM t_odd WHERE f_leak(secret) AND n < 4;
NOTICE: Secret is: secret1
NOTICE: Secret is: secret2
NOTICE: Secret is: secret3
NOTICE: Secret is: secret4
NOTICE: Secret is: secret5
NOTICE: Secret is: secret6
NOTICE: Secret is: secret7
NOTICE: Secret is: secret8
NOTICE: Secret is: secret9
NOTICE: Secret is: secret10
NOTICE: Secret is: secret11
NOTICE: Secret is: secret12
NOTICE: Secret is: secret13
NOTICE: Secret is: secret14
NOTICE: Secret is: secret15
NOTICE: Secret is: secret16
NOTICE: Secret is: secret17
NOTICE: Secret is: secret18
NOTICE: Secret is: secret19
NOTICE: Secret is: secret20
n | secret
---+---------
1 | secret1
3 | secret3
(2 rows)
regress=> EXPLAIN SELECT * FROM t_odd WHERE f_leak(secret) AND n < 4;
QUERY PLAN
----------------------------------------------------------
Seq Scan on t (cost=0.00..34.60 rows=1 width=36)
Filter: (f_leak(secret) AND (n < 4) AND ((n % 2) = 1))
(2 rows)
Oops! As you can see, a function with a user-supplied predicate was considered cheaper to run than other tests, so it skipped all the lines before the presentation predicate ruled out the unsuitable ones. A malicious function may use the same trick to copy strings.
Security_barrier views
Security_barrier views correct this by causing the view conditions to be executed first before any conditions created by the user are applied. Instead of expanding the presentation and staking out any conditions for the presentation into an external request, they replace the link to the presentation with a subquery. This subquery has the security_barrier flag set across the entire range of its entry in the table, which tells the optimizer that he should not touch the subquery, or make conditions out of it, as he would in a normal case.
Thus, a performance with a protective barrier:
CREATE VIEW t_odd_sb WITH (security_barrier) AS SELECT n, secret FROM t WHERE n % 2 = 1;
we will get:
regress=> SELECT * FROM t_odd_sb WHERE f_leak(secret) AND n < 4;
NOTICE: Secret is: secret1
NOTICE: Secret is: secret3
n | secret
---+---------
1 | secret1
3 | secret3
(2 rows)
regress=> EXPLAIN SELECT * FROM t_odd_sb WHERE f_leak(secret) AND n < 4;
QUERY PLAN
---------------------------------------------------------------
Subquery Scan on t_odd_sb (cost=0.00..31.55 rows=1 width=36)
Filter: f_leak(t_odd_sb.secret)
-> Seq Scan on t (cost=0.00..31.53 rows=2 width=36)
Filter: ((n < 4) AND ((n % 2) = 1))
(4 rows)
The query execution plan should tell you what is happening, although it does not show the attribute of the security barrier in the output of the explanation. The nested subquery forces to scan t with the conditions of the subquery of the view, after which the conditions of the function written by the user are satisfied on the received data.
But. Wait a second. Why does the user-defined predicate n <4 also appear in the subquery? Isn't that a potential security hole? If n <4 is omitted, then why not f_leak (secret) ?
LEAKPROOF operators and functions
The explanation for this is that the < statement is marked as LEAKPROOF . This attribute indicates that the given operator, or the function will not allow information to be trusted, can accordingly be safely applied to security_barrier views. For obvious reasons, you cannot set the LEAKPROOF attribute as a regular user:
regress=> ALTER FUNCTION f_leak(text) LEAKPROOF;
ERROR: only superuser can define a leakproof function
superusers can do whatever they want and they don’t have to resort to tricks with the functions of information leakage in order to pass the protective barrier of representations.
Why can't you update security_barrier views
Regular views in PostgreSQL 9.3 are automatically updated , but security_barrier views do not imply “simplicity”. This is because view updates rely on the ability to remove the view subquery, turning the update into a regular table update. The whole point of security_barrier submissions is to not allow this exception to condition submissions. UPDATE currently cannot work directly with a subquery, so PostgreSQL will reject any attempt to update the security_barrier view:
regress = > UPDATE t_odd
SET
secret = 'secret_haha' || n;
UPDATE 10 regress = > UPDATE t_odd_sb
SET
secret = 'secret_haha' || n;
ERROR: cannot UPDATE VIEW "t_odd_sb" DETAIL: SECURITY - barrier views ARE NOT automatically updatable. HINT: TO ENABLE updating the VIEW,
provide an INSTEAD OF UPDATE TRIGGER
OR an unconditional ON UPDATE DO INSTEAD RULE.
This is exactly the restriction that I am interested in canceling, as part of the work of developing line-level security for the AXLE project . Kohei KaiGai has done a tremendous amount of work with row-level security, and things like security_barrier and LEAKPROOF have come a lot from his job of adding row-level security to PostgreSQL. The next challenge is how to deal with updating the protective barrier safely and in such a way that it is serviced in the future.
Why subqueries?
You may wonder why we use subqueries for this. I thought about it. The short version is that we should not, but if we do not use subqueries, we will instead have to create new sort-sensitive variations of the AND and OR operators and teach the optimizer that he cannot pass conditions through them. Since views are already extended by subqueries, it is much easier to mark subqueries as fences that do not allow you to retrieve / add data to them.
PostgreSQL already has a simplified ordered operation - CASE . The problem with using CASE is that no operations can cross CASE boundaries , even LEAKPROOF. As well as the optimizer and can not make decisions about the use of indexes? based on the expression inside the CASE block. So if we used CASE as I asked about it here, we would never have been able to use the index to satisfy the user-provided condition.
In code
Security_barrier support was added in 0e4611c0234d89e288a53351f775c59522baed7c and is enhanced by LEAKPROOF support in cd30728fb2ed7c367d545fc14ab850b5fa2a4850 . Thanks go to commit notes. Thanks to everyone who participated.
PS. The article is relatively old, but important as an introduction to the translation of the next article.
