Set created_by in a BEFORE INSERT trigger, never from the client

The answer. Anything the client could lie about should not be a client-supplied column. created_by, the tenant a child row belongs to, and denormalized display fields are set in a before insert trigger that reads the caller's identity from auth.uid(); an unauthenticated caller makes the lookup return null and the not null constraint rejects the row, which is the behavior you want. Trigger functions run as the calling role by default, so any table they read needs a policy for that caller (the profiles self-read policy in the example); mark a function security definer only when it must read rows the caller cannot (another user's name for a cached assignee_name), and then set search_path and qualify every identifier. The before update twin does the reverse job: copy immutable columns from old, stamp updated_at, and the client can send whatever it likes.

The pattern.

create or replace function public.set_created_by() returns trigger language plpgsql set search_path = '' as $$
begin
  new.created_by := (select id from public.profiles where user_id = (select auth.uid()));   -- null → not null violation
  new.author_name := (select full_name from public.profiles where id = new.created_by);   -- cached, historically correct
  return new;
end $$;
create trigger trg_comments_1_created_by before insert on public.comments for each row execute function public.set_created_by();

create or replace function public.protect_immutable() returns trigger language plpgsql set search_path = '' as $$
begin
  new.created_by := old.created_by; new.created_at := old.created_at; new.tenant := old.tenant;
  new.updated_at := now();
  return new;
end $$;
create trigger trg_comments_2_protect before update on public.comments for each row execute function public.protect_immutable();

Watch out.

Several triggers on one table fire in alphabetical order; number the names, one concern per trigger.
check constraints see only the row, so "at most one attachment per ticket" is a trigger or a definer helper, not a check.
Cached names are a product decision: they stay as they were when written. If you want live names, keep the id and join through a definer function.