Centrifugal vs centripetal is certainly confusing!
You have one OR the other depending on how you look at the situation.
If you look from the point of view of the object in circular motion, you have centrifugal force - outward. If you look from the point of view of the outside world (not in circular motion) there is centripetal force inward toward the center, holding the object in its circular path. The two forces are both equal to mv²/R.
I suggest you take the point of view of the water, moving in circular motion, so you have centrifugal force away from the center. When the pail is at the bottom of its path, the force is downward and it just adds to the gravity force to hold the water more strongly inside the pail. At the top of the path, the centrifugal force is upward. If it is at least as strong as gravity (pulling down), it will hold the water in the upside down pail.