Re: Postgres trigger side-effect is occurring out of order withrow-level security select policy

The real issue here is to do with the visibility of the data inserted
by the trigger function from within the same command. In general, data
inserted by a command is not visible from within that same command.

The easiest way to see what's going on is with a simple example.
Consider the following (based on the original example, but without any
RLS):


DROP TABLE IF EXISTS a,b;

CREATE TABLE a (id text);
CREATE TABLE b (id text);

CREATE OR REPLACE FUNCTION reproHandler() RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE USING MESSAGE = 'inside trigger handler';
    INSERT INTO b (id) VALUES (NEW.id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER reproTrigger BEFORE INSERT ON a
FOR EACH ROW EXECUTE PROCEDURE reproHandler();

CREATE OR REPLACE FUNCTION check_b1(text) RETURNS boolean AS $$
BEGIN
  RETURN (EXISTS (SELECT * FROM b WHERE b.id = $1));
END
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION check_b2(text) RETURNS boolean AS $$
BEGIN
  RETURN (EXISTS (SELECT * FROM b WHERE b.id = $1));
END
$$ LANGUAGE plpgsql VOLATILE;

INSERT INTO a VALUES ('xxx')
  RETURNING id, check_b1(id), check_b2(id),
            (EXISTS (SELECT * FROM b WHERE b.id = a.id));

NOTICE:  inside trigger handler
 id  | check_b1 | check_b2 | exists
-----+----------+----------+--------
 xxx | f        | t        | f
(1 row)

INSERT 0 1


Notice that the functions check_b1() and check_b2() are identical,
except that check_b1() is declared STABLE and check_b2() is declared
VOLATILE, and that makes all the difference. Quoting from the
documentation for function volatility [1]:

    For functions written in SQL or in any of the standard procedural
    languages, there is a second important property determined by the
    volatility category, namely the visibility of any data changes that
    have been made by the SQL command that is calling the function. A
    VOLATILE function will see such changes, a STABLE or IMMUTABLE
    function will not.

[1] https://www.postgresql.org/docs/10/static/xfunc-volatility.html

Also notice that the inline EXISTS query behaves in the same way as
the STABLE function -- i.e., it does not see changes made in the
current query.

So returning to the RLS example, because the RLS SELECT policy is
defined using inline SQL, it cannot see the changes made by the
trigger. If you want to see such changes, you need to define a
VOLATILE function to do the RLS check.

Regards,
Dean