Rope ties should not be painful, per se; they should feel more like a sort of bear hug. As always, that depends on the person being tied. While different materials will hold knots differently and provide different sensations, there are other factors at play, from where on the body the rope is applied, to sensitivity to bruising, and more. Someone with pre-existing conditions like past injuries or nerve pain will have a different tolerance; what you might consider not painful at all, they may consider painful.
Anywhere rope is applied, there are risks of marks. In terms of pain, make sure you do not tie too tightly (you should have enough room between the rope and the body to fit a finger). Always check in with the person being tied - whether it's verbal communication or hand signals - as pain can lead to nerve damage and other injuries. Read up on signs of pain to watch out for as well. While checking in with your partner is a must, sometimes the partner is not in a state to reasonably assess what's going on due to bottom-space. Allergies to fibers like hemp do exist, too, and that can definitely ruin a scene.
In terms of rope materials, hemp and jute lose oil over time, and thus will be much rougher on the body. As rope gets used over time, the strength of the rope will also degrade. Suppliers often recommend rope conditioning for these fibers and recommend testing rope for tensile strength; once the rope is worn out, it should not be used as it is no longer safe. Other fibers, like cotton or silk, do not require oil but should be washed and reasonably maintained.
Basically: if there's pain, there might be a problem. Educate, communicate, and take care of your tools.
