Foreign Key to Multicolumn Primary Key?
You need FKs in category_product table, not in categories and products:

CREATE TABLE IF NOT EXISTS categories (
    id serial,
    name text NOT NULL,
    is_active boolean NOT NULL DEFAULT true,
    CONSTRAINT PK_CATEGORIES_ID PRIMARY KEY (id)

);

CREATE TABLE IF NOT EXISTS products (
    id serial,
    name text NOT NULL,
    description text NULL,
    photo text NULL,
    price money NOT NULL,
    is_in_menu boolean NOT NULL DEFAULT true,
    is_from_kitchen boolean NOT NULL DEFAULT true,
    is_deleted boolean NOT NULL DEFAULT false,
    CONSTRAINT PK_PRODUCTS_ID PRIMARY KEY (id)    
);

CREATE TABLE IF NOT EXISTS category_product (
    cat_id integer NOT NULL,
    product_id integer NOT NULL,
    CONSTRAINT PK_CATEGORY_PRODUCT_ID PRIMARY KEY (cat_id, product_id),
    CONSTRAINT FK_CATEGORY_PRODUCT_CATEGORY FOREIGN KEY(cat_id) REFERENCES categories(id)
    ON DELETE CASCADE,
    CONSTRAINT FK_CATEGORY_PRODUCT_PRODUCT FOREIGN KEY(product_id) REFERENCES products(id)
);