RLS, Partitioning & FDW — Series 2

Preview — 3 of 10 questions

A table has two PERMISSIVE RLS policies: one allowing rows where department = 'sales', another allowing rows where owner_id = current_user_id(). How do multiple PERMISSIVE policies combine?

javascript

CREATE POLICY sales_view ON deals USING (department = 'sales');
CREATE POLICY own_view ON deals USING (owner_id = current_user_id());

-- A row is visible if it's in sales, OR owned by the current user, OR both

AMultiple PERMISSIVE policies are combined with OR — a row is visible if it satisfies any of the permissive policies that apply to the current operation

BMultiple PERMISSIVE policies are combined with AND — a row must satisfy all of them to be visible

COnly the first PERMISSIVE policy created is evaluated; subsequent ones are ignored

DHaving more than one PERMISSIVE policy on the same table raises an error at creation time

How does adding a RESTRICTIVE policy change the combination logic from Question 1?

javascript

CREATE POLICY restrict_active ON documents AS RESTRICTIVE
  USING (status != 'archived');

ARESTRICTIVE policies replace all PERMISSIVE policies entirely

BRESTRICTIVE policies are ANDed with the combined result of all PERMISSIVE policies — a row must both satisfy at least one PERMISSIVE policy AND satisfy every RESTRICTIVE policy to be visible; RESTRICTIVE policies narrow down what PERMISSIVE policies already allowed, rather than expanding access

CRESTRICTIVE policies only apply to superusers

DRESTRICTIVE and PERMISSIVE policies are functionally identical; the keyword is purely cosmetic

A policy has both a USING clause and a WITH CHECK clause. What's the difference in when each applies?

javascript

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::int)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::int);

AUSING and WITH CHECK are synonyms; specifying both is redundant

BWITH CHECK only applies to DELETE statements

CUSING filters which existing rows are visible/affected for SELECT, UPDATE, and DELETE; WITH CHECK validates new row values being written by INSERT or UPDATE — a row could pass USING (so you can see/target it) but fail WITH CHECK (so you can't write a value that would violate the policy), and vice versa

DUSING only applies to INSERT statements

Answer all 10 questions (7 more), see explanations for every answer, and track your score.