How to link rows in a many-to-many relationship where types have parents, grandparents etc
Option 2.

Only save a row for the assigned category, no extra rows for category parents. Don't store redundant information in your tables. That's bad for general performance and for maintenance. (Also don't save the name of the product in table prod_cat redundantly)

A recursive query on category is cheap and simple. You might encapsulate it in a function for convenience:

CREATE OR REPLACE FUNCTION f_cats_of_id(_id int)
  RETURNS TABLE (category_id int) AS
$func$
WITH RECURSIVE cte AS (
   SELECT $1 AS parent_id  -- not checking existence, cheapest

   UNION ALL
   SELECT c.id
   FROM   cte
   JOIN   category c USING (parent_id)
   )
TABLE cte
$func$  LANGUAGE SQL;


And this query to ...


  ... get all products that 'are' in category with id 1 (including subcategories)


SELECT DISTINCT p.*  -- assuming you want all columns of table product.
FROM   f_cats_of_id(1)
JOIN   cat_prod cp USING (category_id)
JOIN   product p ON p.id = cp.product_id;


DISTINCT because ...


  ... a Product can be in multiple categories


The same product might be listed more than once, via category and sub-category. DISTINCT folds duplicates.

db&lt;>fiddle here