parallel query and row-level security?

Поиск
Список
Период
Сортировка
От Karl Czajkowski
Тема parallel query and row-level security?
Дата
Msg-id 20161028004410.GA14502@moraine.isi.edu
обсуждение исходный текст
Ответы Re: parallel query and row-level security?  (Karl Czajkowski <karlcz@isi.edu>)
Список pgsql-general
Hi, I am struggling to find information on how the new parallel query
features intersect with row-level security.  I am wondering whether
this is expected to work at this time?

My cursory experiments indicate that we can get a parallel plan when
querying as the table owner or superuser but not when querying as a
role that is subject to the row-level security policies on the same
table.

Our policies on the table are something like:

   CREATE POLICY "p1" ON schema1.table1
      FOR SELECT
      USING (ARRAY['x', 'y'] && schema1.our_procedure());

   CREATE POLICY "p2" ON schema1.table1
      FOR SELECT
      USING ((SELECT True FROM schema2.foo_authz p
              WHERE p.sec_class = table1.sec_class
              LIMIT 1));

We've defined our_procedure as STABLE PARALLEL SAFE using plpgsql and
it makes a call to current_setting().  Should we ever expect to see
parallel query with policies such as these?  Are there any documented
limits where we might adjust our policy and gain parallel plans?

Thanks,


Karl

p.s. In case you are wondering about these odd policies, we are using
them to inject web client authorization decisions into our data access
layer...

We are running under a single application postgresql role and our app
passes web client authentication attributes as a session parameter
which we look up with current_setting() and convert back into an array
to intersect with the ACL content ['x', 'y'] in the first policy
above.

The second policy permits access to rows in a certain security class
managed by dynamic content in another mapping table (which of course
has limits as to how the web application will modify it).  This allows
some subset of rows to be exposed to less trusted clients, while the
first policy reveals all rows to a more trusted group of clients.

We also have some tables where the ACL is stored in a column and
intersected for each row, but that doesn't exist in this first case
where I tried to get a parallel plan.


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

Предыдущее
От: Rich Shepard
Дата:
Сообщение: Re: Save query results to new table [RESOLVED]
Следующее
От: Pavel Stehule
Дата:
Сообщение: Re: slow performance of array_agg after upgrade from 9.2 to 9.5