Re: Row-security on updatable s.b. views

On 03/08/2014 01:56 AM, Tom Lane wrote:
> Craig Ringer <craig@2ndquadrant.com> writes:
>> What I'm concerned about is the locking. It looks to me like we're
>> causing the user to lock rows that they may not intend to lock, by
>> applying a LockRows step *before* the user supplied qual. (I'm going to
>> test that tomorrow, it's sleep time in Australia).
> 
> The fact that there are two LockRows nodes seems outright broken.
> The one at the top of the plan is correctly placed, but how did the
> other one get in there?

I initially thought it was the updatable security barrier views code
pushing the RowMark down into the generated subquery. But if I remove
the pushdown code the inner LockRows node still seems to get emitted.

In fact, it's not a new issue. In vanilla 9.3.1:


regress=> select version();                                                  version


--------------------------------------------------------------------------------------------------------------PostgreSQL
9.3.1on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
 
4.8.1 20130603 (Red Hat 4.8.1-1), 64-bit
(1 row)

regress=> CREATE TABLE t1(x integer, y integer);
CREATE TABLE
regress=> INSERT INTO t1(x,y) VALUES (1,1), (2,2), (3,3), (4,4);
INSERT 0 4
regress=> CREATE VIEW v1 WITH (security_barrier) AS SELECT x, y FROM t1         WHERE x % 2 = 0;
CREATE VIEW
regress=> CREATE OR REPLACE FUNCTION user_qual() RETURNS boolean AS $$         BEGIN RETURN TRUE; END;         $$
LANGUAGEplpgsql;
 
CREATE FUNCTION

regress=>  EXPLAIN SELECT * FROM v1 WHERE user_qual() FOR UPDATE;                             QUERY PLAN
-----------------------------------------------------------------------LockRows  (cost=0.00..45.11 rows=4 width=40)  ->
Subquery Scan on v1  (cost=0.00..45.07 rows=4 width=40)        Filter: user_qual()        ->  LockRows
(cost=0.00..42.21rows=11 width=14)              ->  Seq Scan on t1  (cost=0.00..42.10 rows=11 width=14)
  Filter: ((x % 2) = 0)
 
(6 rows)



so it looks like security barrier views are locking rows they should not be.

I can confirm that on 9.3.1 with:

CREATE OR REPLACE FUNCTION row_is(integer, integer) RETURNS boolean as
$$ begin return (select $1 = $2); end; $$ language plpgsql;

then in two sessions:

SELECT * FROM v1 WHERE row_is(x, 2) FOR UPDATE;

and

SELECT * FROM v1 WHERE row_is(x, 4) FOR UPDATE;


These should not block each other, but do.

So there's a pre-existing bug here.

-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services