Postgres : Using WITH .... AS insert into to return non-inserted column
One possible solution is adding userid to the staging table you already have:

CREATE TABLE temp_import (
    row_id int,
    name varchar(32),
    email varchar(255),
    userid int not null default nextval('tmp_uid_seq'::regclass)
);


Use the same column definition above as you do in users - this way you use the same sequence, and even the nextval() will be correct after the import.

When doing the COPY, the userid will be filled.  This way you have the mapping and the staging all in the same place.  Now you have to copy the userid into the final table, too:

INSERT INTO users (userid, name)
SELECT userid, name
  FROM temp_import;

INSERT INTO mapping (userid, row_id)
SELECT userid, row_id
  FROM temp_import;


Notes: 


in PostgreSQL, unlimited text and varchar have some performance advantage over the limited types.  Use the latter if you really have a limit.  With varchar(255) the case is usually 'We don't know how long it can be, but definitely not that long'.  Email addresses are usually considered as limited to 254 characters, not 255: https://www.rfc-editor.org/errata_search.php?eid=1690  Also, are you sure that nobody can have a name longer than 32 characters, including spaces, punctuation and so on?
it usually makes sense to use some consistent naming scheme in any software system, including databases.  I'm already confused a bit about the different styles used in userid and row_id.
if the import will happen repeatedly or in parallel, just use the same sequence all the time.  Sequences are transaction-safe and won't reuse any values until they wrap over (which can be mostly prevented by using bigint/bigserial).