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

["Charles Clavadetscher (SwissPUG)" <clavadetscher@xxxxxxxxxxxx>]

An addition

On 01.10.2018 05:18:15, Charles Clavadetscher (SwissPUG) <clavadetscher@xxxxxxxxxxxx> wrote:

Hello

On 01.10.2018 05:00:02, Carl Sverre <sverre.carl@xxxxxxxxx> wrote:

Thank you for the detailed report Charles. I think you may be missing the “returning id” clause in the insert. Can you verify it works when you use “returning id”? Thanks!

[Charles] : You are right:

testuser@charles.localhost=> INSERT INTO a VALUES ('fails2') RETURNING id;

NOTICE:  inside trigger handler

ERROR:  new row violates row-level security policy for table "a"

This implies that the returning_id is evaluated before the trigger executes, i.e. Adrian's assumption is probably correct.

Regards

Charles

[Charles] : In the RLS documentation (https://www.postgresql.org/docs/current/static/ddl-rowsecurity.html):

"To specify which rows are visible or modifiable according to a policy, an _expression_ is required that returns a Boolean result. This _expression_ will be evaluated for each row prior to any conditions or functions coming from the user's query."

I assume that the returning clause is essentially a condition or function from the user query or it is implemented as such. Therefore the policy is applied prior to it.

The most knowlegdable person on this list on the RLS topic is Stephen Frost. He may shed light on the matter.

Regards

Charles