redz1029
b307f66f2c
Projects: Brick-backed model with list/detail screens and nav entry. Work Logs: per-contributor time ledger, assignee running-clock + helper logged minutes with Hours/Minutes input; pause-after-save and helper-deduct prompts. Comments: threaded comments with file attachments and Quill composer. Detail screen: tabbed layout (Work Log / Comments / Activity), editable title + description via edit dialog, Quill-rendered descriptions with legacy plain-text fallback. Create dialog: fixed-width (480px max), Quill description editor. Shared QuillFieldEditor + QuillReadOnly widgets extracted for reuse. RLS: hardened activity-log actor binding, immutable creator_id/ created_at triggers, SECURITY INVOKER visibility helper, append-only work-log and scoped comment policies. Brick migration for project_id FK on programmer_tasks. Co-Authored-By: claude-flow <ruv@ruv.net>
52 lines
2.0 KiB
PL/PgSQL
52 lines
2.0 KiB
PL/PgSQL
-- Hardens the programmer_tasks RLS shipped in 20260927120000, per the commit
|
|
-- security review (two MEDIUM findings). The base migration is already applied
|
|
-- remotely, so this is a corrective follow-up.
|
|
|
|
-- 1) audit-log-forgery: the activity-log INSERT policy did not bind actor_id to
|
|
-- the caller, so an authorized user could attribute a log entry to someone
|
|
-- else. Require actor_id to be the caller (or null for system rows).
|
|
drop policy if exists "programmer_task_activity_logs_insert" on programmer_task_activity_logs;
|
|
create policy "programmer_task_activity_logs_insert" on programmer_task_activity_logs
|
|
for insert to authenticated
|
|
with check (
|
|
(actor_id = auth.uid() or actor_id is null)
|
|
and exists (
|
|
select 1 from programmer_tasks t
|
|
where t.id = task_id
|
|
and (
|
|
t.assignee_id = auth.uid()
|
|
or t.creator_id = auth.uid()
|
|
or exists (
|
|
select 1 from profiles p where p.id = auth.uid() and p.role in ('admin', 'programmer')
|
|
)
|
|
)
|
|
)
|
|
);
|
|
|
|
-- 2) column-immutability: the UPDATE policy allowed an assignee to mutate any
|
|
-- column, including creator_id/created_at (the DELETE policy keys off
|
|
-- creator_id). Make those columns immutable via a trigger.
|
|
create or replace function programmer_tasks_guard_immutable()
|
|
returns trigger
|
|
language plpgsql
|
|
set search_path = ''
|
|
as $$
|
|
begin
|
|
if new.creator_id is distinct from old.creator_id then
|
|
raise exception 'creator_id is immutable';
|
|
end if;
|
|
if new.created_at is distinct from old.created_at then
|
|
raise exception 'created_at is immutable';
|
|
end if;
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
drop trigger if exists trg_programmer_tasks_guard_immutable on programmer_tasks;
|
|
create trigger trg_programmer_tasks_guard_immutable
|
|
before update on programmer_tasks
|
|
for each row execute function programmer_tasks_guard_immutable();
|
|
|
|
-- Note: activity-logs UPDATE/DELETE remain denied (no such policies exist),
|
|
-- keeping the audit trail append-only. New log/ledger tables follow the same shape.
|