Role-based access for an internal finance portal: approval limits and segregation of duties in PostgreSQL

create schema fin;

create table fin.departments (id int primary key, name text not null);

create table fin.users (
  id    uuid primary key,
  email text unique not null
);

create table fin.roles (name text primary key);
insert into fin.roles values ('ap_clerk'), ('approver'), ('controller'), ('cfo'), ('auditor');

create table fin.permissions (name text primary key);
insert into fin.permissions values
  ('bill.view'), ('bill.enter'), ('bill.approve'), ('bill.export'), ('supplier.bank.edit');

create table fin.role_permissions (
  role       text references fin.roles,
  permission text references fin.permissions,
  primary key (role, permission)
);
insert into fin.role_permissions values
  ('ap_clerk', 'bill.view'), ('ap_clerk', 'bill.enter'),
  ('approver', 'bill.view'), ('approver', 'bill.approve'),
  ('controller', 'bill.view'), ('controller', 'bill.approve'), ('controller', 'bill.export'),
  ('controller', 'supplier.bank.edit'),
  ('cfo', 'bill.view'), ('cfo', 'bill.approve'), ('cfo', 'bill.export'),
  ('auditor', 'bill.view'), ('auditor', 'bill.export');

-- department_id null means "all departments"
create table fin.user_roles (
  user_id       uuid references fin.users,
  role          text references fin.roles,
  department_id int references fin.departments,
  unique (user_id, role, department_id)
);

create function fin.current_user_id() returns uuid
language sql stable as $$
  select nullif(current_setting('app.user_id', true), '')::uuid
$$;

create function fin.has_permission(p text, dept int default null) returns boolean
language sql stable security definer set search_path = fin as $$
  select exists (
    select 1
      from fin.user_roles ur
      join fin.role_permissions rp on rp.role = ur.role
     where ur.user_id = fin.current_user_id()
       and rp.permission = p
       and (ur.department_id is null or dept is null or ur.department_id = dept)
  )
$$;

-- in the app, per request:  begin; select set_config('app.user_id', $1, true); ... commit;

create table fin.bills (
  id            uuid primary key default gen_random_uuid(),
  supplier      text not null,
  department_id int not null references fin.departments,
  amount        numeric(12, 2) not null check (amount > 0),
  status        text not null default 'pending' check (status in ('pending', 'approved', 'rejected')),
  entered_by    uuid not null references fin.users,
  approved_by   uuid references fin.users,
  approved_at   timestamptz
);

alter table fin.bills enable row level security;

create policy bills_read on fin.bills for select
  using (fin.has_permission('bill.view', department_id));

create policy bills_enter on fin.bills for insert
  with check (fin.has_permission('bill.enter', department_id)
              and entered_by = fin.current_user_id());

create table fin.approval_limits (
  role          text references fin.roles,
  department_id int references fin.departments,  -- null = any department
  max_amount    numeric(12, 2) not null
);
insert into fin.approval_limits values
  ('approver', null, 5000), ('controller', null, 50000), ('cfo', null, 99999999);

create table fin.audit_log (
  id        bigint generated always as identity primary key,
  actor     uuid not null,
  action    text not null,
  bill_id   uuid not null,
  amount    numeric(12, 2),
  at        timestamptz not null default now()
);

create function fin.approve_bill(p_bill uuid) returns void
language plpgsql security definer set search_path = fin as $$
declare
  b       fin.bills;
  me      uuid := fin.current_user_id();
  my_max  numeric;
begin
  select * into b from fin.bills where id = p_bill for update;
  if not found then raise exception 'Bill not found'; end if;
  if b.status <> 'pending' then raise exception 'Bill is already %', b.status; end if;

  if b.entered_by = me then
    raise exception 'You cannot approve a bill you entered';
  end if;

  select max(al.max_amount) into my_max
    from fin.user_roles ur
    join fin.role_permissions rp on rp.role = ur.role and rp.permission = 'bill.approve'
    join fin.approval_limits al on al.role = ur.role
   where ur.user_id = me
     and (ur.department_id is null or ur.department_id = b.department_id)
     and (al.department_id is null or al.department_id = b.department_id);

  if my_max is null then raise exception 'You cannot approve bills for this department'; end if;
  if b.amount > my_max then raise exception 'Amount % is above your approval limit of %', b.amount, my_max; end if;

  update fin.bills set status = 'approved', approved_by = me, approved_at = now() where id = p_bill;
  insert into fin.audit_log (actor, action, bill_id, amount) values (me, 'approve', p_bill, b.amount);
end $$;

create table fin.suppliers (
  id             int primary key,
  name           text not null,
  bank_account   text,
  bank_sort_code text
);

create role finance_app nologin;
grant usage on schema fin to finance_app;
grant select (id, name) on fin.suppliers to finance_app;   -- no bank columns
grant select, insert on fin.bills to finance_app;
grant execute on function fin.approve_bill(uuid) to finance_app;

-- clerk enters a 3,200 bill for Operations (dept 1)
select set_config('app.user_id', :'clerk', true);
insert into fin.bills (supplier, department_id, amount, entered_by)
values ('Acme Supplies', 1, 3200, fin.current_user_id());

-- the clerk tries to approve it          -> ERROR: You cannot approve a bill you entered
-- (the clerk also lacks bill.approve, but the duty check fires first)

-- Operations approver approves it        -> ok, audit row written
-- Marketing approver tries a dept 1 bill -> ERROR: You cannot approve bills for this department
-- approver tries a 12,000 bill           -> ERROR: Amount 12000.00 is above your approval limit of 5000.00
-- controller approves the 12,000 bill    -> ok
-- finance_app selects bank_account       -> ERROR: permission denied for table suppliers