How can I obtain the last inserted row with INSERT ... SELECT in PostgreSQL?
It seems that you need only the maximum of the last inserted (user_id,object_id) as a couple. The first INSERT could be:

WITH inserted_rows as (
INSERT INTO dest_table
SELECT user_id, object_id, object_type, colN, ...
FROM source_table 
ORDER BY user_id, colN, object_id  -- this is indexed
LIMIT 1000  -- batch size
RETURNING user_id, object_id
)
SELECT row(user_id,object_id) FROM inserted_rows
 ORDER BY 1 DESC LIMIT 1;


This form uses a row to refer to the (user_id,object_id) couple for convenience. Row elements are compared left-to-right as needed in your case to use this couple of values as a boundary. See 
 Row Constructor Comparison in the documentation.

With this solution, the subsequent INSERTs would inject the value returned by the last preceding insert. This avoids looking up the last inserted row in dest_table.

WITH inserted_rows as (
INSERT INTO dest_table
SELECT user_id, object_id, object_type, colN, ...
FROM source_table
  WHERE row(user_id,object_id) &gt; row(...inject the result of the previous query here...)
ORDER BY user_id, colN, object_id
LIMIT 1000
RETURNING user_id, object_id
)
SELECT row(user_id,object_id) FROM inserted_rows
ORDER BY 1 DESC LIMIT 1;



  Do I need to create a function for this (e.g. plpgsql) or am I missing
  something obvious in plain SQL that let me do this? That would be
  highly favorable.


In plain SQL, you would do a single INSERT in one step without any batching. Since you want to break it into batches, there has to be some loop driving these INSERTs with a stop condition, and this procedural logic goes beyond pure SQL.

If you don't feel like reinjecting the boundary through a variable, it could be persisted in a dedicated, single-row table.