Thread: [HACKERS] RLS in CTE incorrect permission failure

[HACKERS] RLS in CTE incorrect permission failure

From
Rod Taylor
Date:
In the attached script, the second insert into t2 (as part of the CTE) should succeed. My actual use case isn't much more complex; the function is used primarily to allow peaking at columns that the function definer has access to but a typical user does not. Function also makes it easy to copy this policy to a number of structures.

The function within the policy doesn't seem to be able to see records inserted by earlier statements in the CTE. Perhaps this is as simple as adding a command counter increment in the right place?

Fails in 9.5.7 and HEAD.

--
Rod Taylor
Attachment

Re: [HACKERS] RLS in CTE incorrect permission failure

From
Tom Lane
Date:
Rod Taylor <rod.taylor@gmail.com> writes:
> In the attached script, the second insert into t2 (as part of the CTE)
> should succeed.

No, I don't think so.  You declared the check function as STABLE which
means it is confined to seeing the same snapshot as the surrounding query.
So it can't see anything inserted by that query.

Possibly it'd work as you wish with a VOLATILE function.
        regards, tom lane



Re: [HACKERS] RLS in CTE incorrect permission failure

From
Rod Taylor
Date:


On Wed, Jun 21, 2017 at 7:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Rod Taylor <rod.taylor@gmail.com> writes:
> In the attached script, the second insert into t2 (as part of the CTE)
> should succeed.

No, I don't think so.  You declared the check function as STABLE which
means it is confined to seeing the same snapshot as the surrounding query.
So it can't see anything inserted by that query.

Possibly it'd work as you wish with a VOLATILE function.

Indeed, that works as expected.

Sorry for the noise.
 

--
Rod Taylor