Hiding cost and margin in Postgres, not in the UI

Row-Level Security decides which rows a user can see. It says nothing about columns. To keep cost and margin away from users without money access, revoke SELECT on the cost columns, read through views that return null for those fields unless the caller may see money, and let the interface hide whatever comes back null. The browser never receives a number it should not show.

This is part 2 of the build log of the ZS Tech CRM. Part 1 built the quote model that produces those costs.

The requirement

Technicians and salespeople need the deal, the line items, and the selling prices. They should never see what the company pays for a device, the margin on a line, the supplier's price list, or the payments a client has made. Only users with access to the Money module can.

Hiding a column in React is not security. The page might not render it, but the API response still contains it, and anyone with the browser's network tab can read it. The data has to be absent from the response, which means the database has to decide.

Step 1: take the columns away

Postgres grants privileges per column as well as per table. Revoke SELECT on the whole table from the authenticated role, then grant it back on every column except the sensitive ones:

revoke select on public.deal_items from authenticated;
grant select (id, deal_id, product_id, name, section_id, qty, unit_price,
              sort, created_at, updated_at)
  on public.deal_items to authenticated;

revoke select on public.product_sources from authenticated;
grant select (id, product_id, supplier_id, currency, lead_days,
              last_quoted_at, notes, created_at, updated_at)
  on public.product_sources to authenticated;

Now unit_cost on a quote line and unit_cost and fx_rate on a supplier source cannot be read by any signed-in user through the API at all. Asking for them returns a permission error. That is the floor. The app still needs those numbers for the people allowed to see them.

Step 2: views that decide column by column

Every screen reads through a view instead of the raw table. The view returns the same rows to everyone with access to the module, but each money column is wrapped in a check:

create view public.v_deal_items as
select di.id, di.deal_id, di.product_id, di.name, di.section_id,
       di.qty, di.unit_price,
       case when public.can_view('money') then di.unit_cost end as unit_cost,
       round(di.qty * di.unit_price, 2) as line_total,
       case when public.can_view('money')
            then round(di.qty * di.unit_cost, 2) end as line_cost,
       case when public.can_view('money')
            then round(di.qty * (di.unit_price - di.unit_cost), 2) end as line_margin
from public.deal_items di
where public.can_view('pipeline') or public.can_view('projects') or public.can_view('money');

A technician gets the line, the quantity, the price, and the line total. The cost, line cost, and margin come back as null. Not a 403, not a missing field: null. The same pattern covers the deal totals, the product list (cost, margin, margin percentage, cheapest source), and the supplier sources.

Why these views run as the owner

My first design used security_invoker views, which run with the caller's permissions. That cannot work here: the caller has no grant on unit_cost, so an invoker view that mentions the column fails for them before the case expression is ever evaluated.

So these views run with the owner's rights and gate themselves. Each one has a where clause for module access and a case for every money column. It is a real trade-off: the view, not the table policy, is now responsible for access, so every new money column added to a view needs its own check. The tests in part 5 exist mostly because of that.

The bug it caused

Adding a product to a quote went through a small SQL function. It inserted the line and, like most insert functions, ended with returning * so the app got the new row back.

Every call failed with error 42501, insufficient privilege. For everyone, including the owners who can see all the money. returning * asks for every column of the new row, and the function ran with the caller's rights, and authenticated no longer had SELECT on unit_cost. Owners see costs because the views let them, not because the role has the column.

-- the fix: return the new line's id, then read it through v_deal_items
create function public.add_product_to_deal(p_deal_id uuid, p_product_id uuid, p_qty numeric default 1)
returns uuid
language plpgsql security invoker set search_path = public as $$
declare v_id uuid;
begin
  insert into public.deal_items (deal_id, product_id, qty) values (p_deal_id, p_product_id, p_qty)
  on conflict (deal_id, product_id) where product_id is not null
  do update set qty = public.deal_items.qty + excluded.qty
  returning id into v_id;
  return v_id;
end $$;

Null-aware code on top

The interface treats null as "not yours to see". The totals function returns cost and margin as null if any line hides its cost, so a partial sum never pretends to be a real margin:

const hidden = lines.some((l) => l.unitCost === null)
const cost = hidden ? null : r2(lines.reduce((a, l) => a + l.qty * (l.unitCost ?? 0), 0))
const margin = cost === null ? null : r2(net - cost)

The quote builder then simply does not render the Cost and Margin columns, the price floor, or the internal preview when those values are null. The screen follows the data instead of guessing from the user's role, so the database stays the only place the rule is written.

Other ways to do this

• Move cost to a separate table that only money users can read. Clean, but every quote query becomes a join with a policy on both sides.
• A private schema with a narrow API of functions. Strong, but heavy for a small team and a single company.
• Separate API routes per role on a server. Works, but the rule then lives in application code, which is exactly what this design avoids.

For one company with seven modules, column grants plus gated views kept the rule in one layer and the app code simple. Part 3 moves on to the money itself: instalments, payments, and what counts as overdue. If you need this kind of permission design in your own system, that is the work on my CRM and ERP page, and the tenant-level version of the same idea is in Multi-tenant SaaS on Supabase.