Mounting long pipe as curtain rod from ceiling between exposed beams. What are these hex bolt sleeves used for existing pipes / better alternative

It's called a tap connector (obvious now I've found it), and you should be able to unscrew it. You can see one here

It's the bottom nut you need to be turning. It's part of a joint that attaches the pipe to the tap.

Once you have this off you should be able to remove the tap as normal.

If it's stuck then something like WD40 or penetrating oil might help. Failing that carefully heating the nut with a heat gun might expand the various parts enough to loosen them. Take precautions to avoid getting direct heat onto the bath (it appears to be acrylic).

A more drastic approach would be to take a hacksaw to the thread to separate it that way.

When the two members are not touching, the bolt starts to act as a "cantilevered beam" (as you have alluded to) and you hit a point where it is not the shear value that governs, it is the moment (bending). You get additional support by the cleat trying to go down and into the wall which puts withdrawel load on the fastener effectively adding more support using the tensile strength of the fastener.

As a note standard zinc hardware store bolts are ungraded steel usually with no actual values assigned to them.

First, if you have the time, you might try to buy some graded bolts online or from a local supplier; not necessarily a high grade, just graded at all so them meet a minimum requirement for strength. Or use structural screws such as Simpson SDS screws.

Secondly, if you have effectively 1-1/4" of cantilever, I would recommend slightly more embedment into the stud. If you hold things flush to the wall with only a 1-1/4" cantilever, I would up the embedment to a minimum of 2-1/2" or more to ensure the wood stud does not crush at bolt under load due to the multiplying effect of the cantilever. Visualize a 12" long bolt embeded 1" in to a 2x4, the wood crushes on the 2x4, the hole enlarges, and the bolt comes out; the bolt does not necessarily even bend before failing. An increased embedment also increases the amount of withdrawal load the fastener can handle when it slides down the wall (if the bolt does bend) and tries to pull out.

Another option is to simply get more fasteners per stud. You can do this by upping the size of the cleat to a 2x6 and use two or three bolts. Or you can add a vertical member below the cleat at each stud and effectively add a bolt every 4" or so in the vertical member with predrilled holes plus the bolt(s) into the cleat. Key is more embedment and/or more bolts to handle the cantilever.

Or, just build them free standing.