Re: CREATE POLICY and RETURNING

Fujii,

* Fujii Masao (masao.fujii@gmail.com) wrote:
> While I was checking the behavior of RLS, I found that the policy for SELECT
> doesn't seem to be applied to RETURNING. Is this intentional? Please see
> the following example.

Yes, it was intentional.  That said, I'm not against changing it.

> CREATE ROLE foo LOGIN NOSUPERUSER;
> CREATE TABLE hoge AS SELECT col FROM generate_series(1,10) col;
> ALTER TABLE hoge ENABLE ROW LEVEL SECURITY;
> GRANT SELECT, DELETE ON hoge TO foo;
> CREATE POLICY hoge_select_policy ON hoge FOR SELECT TO foo USING (col < 4);
> CREATE POLICY hoge_delete_policy ON hoge FOR DELETE TO foo USING (col < 8);
> \c - foo
> DELETE FROM hoge WHERE col = 6 RETURNING *;
>
> The policy "hoge_select_policy" should disallow the user "foo" to see the row
> with "col = 6". But the last DELETE RETURNING returns that row.

The DELETE USING policy allows DELETE to see the record and therefore
it's available for RETURNING.

> One minor suggestion is: what about changing the message as follows?
> There are two more similar messages in policy.c, and they use the word
> "row-policy" instead of "policy". For the consistency, I think that
> the following also should use the word "row-policy".

I was looking at these while going over the larger "try to be more
consistent" concerns, but that was leading me towards 'policy' instead
of 'row-policy', as the commands are 'CREATE POLICY', etc.

Not against going the other way, but it seems more consistent to do
'policy' everywhere..
Thanks!
    Stephen