Open cell foam applied in contact with a roof deck will cause the roof deck to accumulate water and eventually rot in many climates, as will cellulose. In cooling dominated and warmer mixed climate areas either can be good.

1-2" of closed cell against the roof deck is usually enough protection against both vapor diffusion and air-transported moisture to allow filling out the rest of the rafter bay with fiber (or open cell foam.)

For details on whether & how much closed cell it takes to work in YOUR climate refer to this document.

If you go with any foam product code requires that you provide an ignition barrier. Half-inch or thicker sheet rock works, but a good way to do this in an attic where you weren't planning to put sheet rock up is to use wet-spray cellulose (3" is enough) to finish filling out the rafter bay after the inch or so of closed cell. Some of the better super-fine spray fiberglass (notably JM Spider) is also fire-rated as an ignition barrier for foam if it's 3" or thicker, but not batts.

Cellulose is preferable to open cell foam in many applications, since it provides hygric buffering and a slightly higher R value, and also significant thermal mass. Open cell has the advantage of forming it's own air-barrier, but it's water-vapor permeable. Cellulose is also vapor permeable, but will wick water away from structural wood and can store significant amounts of water for weeks/months without damage. Water vapor that gets behind open cell foam accumulates in the structural wood whenever the wood is below the dew point of the room air.

Vapor retardent paints applied directly to open-cell foam do not perform to spec. (But they will when applied to air-tight sheet-rock.) In general it's better to go with a stackup that doesn't need a highly-retardent vapor barrier like polyethylene or foil, since that tends to cause as many problems as they solve especially in roof deck apps, where the assembly often has limited or no ability to dry toward the exterior due to rain/dew/snow keeping the moisture drive on the exterior at saturation sometimes for weeks or even months on end.