Using the simplest case:
I create a rectangle and assign a colour.
I duplicate the colour palette, call it palette2, and change the assigned colour in the duplicated palette.
I use a colour override node on the layer, moving palette 2 into the right column for whole palette override.
Nothing happens to the colour.
Am I doing something wrong or is this broken?
If you are duplicating you are indeed doing something wrong. You should clone instead. If you have a palette and make a clone and a duplicate with two different colours and paint a drawing with the original, when you switch palettes on Scene Palettes the colour will change when you click on the clone but not when you click on the copy. The same happens when using the colour override node.