If within PG, then I’d view that as something I should explore. Otherwise, I’m not keen, at present, to push things into an application when they can be achieved within PG - this being my own (fairly uneducated) preference.
Then grant permission to call that procedure to roles that need to create new host records. Your original CTE would then be executed within the procedure. Roles would not be given permission to insert directly into the host or related tables - but the owner of the procedure would.
