INSERT rows in two tables preserving connection to a third table
It took me an awfully long while to figure it out, but here it goes:

Setup:

CREATE TABLE sites
(
  site_id INT PRIMARY KEY
);

CREATE TABLE properties
(
  property_id SERIAL PRIMARY KEY,
  property_def_id INT NOT NULL
);

CREATE TABLE site_properties
(
  property_id INT NOT NULL,
  site_id INT NOT NULL,
  PRIMARY KEY (property_id, site_id),
  FOREIGN KEY (property_id) REFERENCES properties(property_id),
  FOREIGN KEY (site_id) REFERENCES sites(site_id)
);

INSERT INTO sites VALUES (101),(102),(103);

INSERT INTO properties(property_def_id) VALUES (3), (4);
INSERT INTO site_properties(property_id, site_id) VALUES (1, 101), (2, 101);


Actual query:

WITH props AS (
  INSERT INTO properties (property_def_id)
    SELECT 1 FROM sites
  RETURNING property_id
), new_props AS (
  SELECT row_number() OVER () AS row, property_id
    FROM props
), all_sites AS (
  SELECT row_number() OVER () AS row, site_id
    FROM sites
)
INSERT INTO site_properties (site_id, property_id)
SELECT s.site_id, np.property_id
FROM all_sites s
  JOIN new_props np USING (row)
;


Checking everything works as expected:

SELECT *
FROM site_properties
  JOIN properties USING (property_id);


property_id | site_id | property_def_id
---------------------------------------
     1          101            3
     2          101            4
     3          101            1
     4          102            1
     5          103            1