What happens with WITH if later the query fails I am by no means a database expert so I might be asking something dumb. 

So I am using PostgreSQL and I want to do the insert to one table, keep the ID (ID is incrementing automatically) and insert a tuple to another table (tuple has that ID in it).

Obviously I want to keep things atomic. 

Query:

WITH inserted AS (
  INSERT INTO
    "public"."user" (email, first_name, last_name, username)
  VALUES
    (?, ?, ?, ?)
  RETURNING id
)
INSERT INTO 
  "public"."security" (user_id, security)
VALUES((
  SELECT id FROM inserted
), ?);


Question is, can this even work? What will happen if the second insert fails? Will the first remain persisted? 

The obvious answer is that I might put this into transaction and commit/rollback but will this be sufficient?
A single statement is always run atomically. 

Quote from the manual


  PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block


A set of common table expressions defined under the same WITH clause, however many of them (including writeable CTEs), is still part of a single statement.  

So the statement either fails completely or succeeds completely.

You only need (explicit) transactions if you want to run atomically more than one statement.

The handling of generated sequence values is no different than in any other situation: a nextval operation is never rolled back