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

Поиск
Список
Период
Сортировка
От Dean Rasheed
Тема Re: Postgres trigger side-effect is occurring out of order withrow-level security select policy
Дата
Msg-id CAEZATCWc2+s0CW1oCMVs8ciNJqTiH1KbWYdVC5znojJZn7GRQQ@mail.gmail.com
обсуждение исходный текст
Ответ на Postgres trigger side-effect is occurring out of order with row-levelsecurity select policy  (Carl Sverre <sverre.carl@gmail.com>)
Ответы Re: Postgres trigger side-effect is occurring out of order withrow-level security select policy
Список pgsql-general
On Mon, 1 Oct 2018 at 21:45, Carl Sverre <sverre.carl@gmail.com> wrote:
> Dean,
> Thank you for the pointer towards visibility/volatility.  I think that completely explains the effect that I am
seeingin my repro.  I experimented with using a VOLATILE function for the SELECT RLS using statement and while it
completelysolves my issue, it incurs too high a cost for query execution due to the RLS policy no longer being inlined
intothe scan. 
>
> I have documented your answer and my experimentation on the stack overflow answer:
>
https://stackoverflow.com/questions/52565720/postgres-trigger-side-effect-is-occurring-out-of-order-with-row-level-security-s
>

I had a quick look at that and found a bug in your implementation. The
RLS check function is defined as follows:

CREATE OR REPLACE FUNCTION rlsCheck(id text) RETURNS TABLE (id text) AS $$
    select * from b where b.id = id
$$ LANGUAGE sql VOLATILE;

which is incorrect because of the ambiguous reference to "id". That
final "id" will, by default, refer to the table column b.id, not the
parameter "id". Thus that function will return every row of b, and
your check won't be doing what you want. That's also going to hurt
performance, but you didn't provide enough information to diagnose the
actual performance problem that you are seeing.

In any case, the above needs to be written as

CREATE OR REPLACE FUNCTION rlsCheck(text) RETURNS TABLE (id text) AS $$
    select id from b where b.id = $1
$$ LANGUAGE sql VOLATILE;

to work as expected.

Regards,
Dean


В списке pgsql-general по дате отправления:

Предыдущее
От: magodo
Дата:
Сообщение: how to identify the timeline of specified recovery_target_time whendo multiple PITR
Следующее
От: pinker
Дата:
Сообщение: pg_sleep() inside plpgsql block - pro & cons