What I don't understand is, what is the overnight fee on - since the underlying assets are neither bought nor sold?
The affect of a CFD is supposed to be the same as if the underlying assets were bought and sold except that you get some leverage. So reasoning on the basis that the underlying assets aren't bought or sold is not sensible.
The overnight charge prevents you from just predicting that currencies will continue to do what everyone expects them to do and profiting from near certainties. The underlying currencies have to move more than expected or less than expected for you to make a profit. The overnight fee is the expected relative movements of the currencies.
When you bet against trends, the "fee" is paid to you.
Another way to understand these fees is that leveraged positions have to be rebalanced daily. This is another way of viewing precisely the same thing -- rebalancing a leveraged position for constant leverage requires adding funds to it or taking funds out of it and that's your overnight fee.
One last way to view it: You want CFDs to be cheap. Do you want to pay today for all the costs and risks that would be borne by the counterparty to your CFD indefinitely? I don't think so, because you may not hold it for very long, and that would make all CFD's much more expensive.
So instead you only pay today for the costs and risks borne by the counterparty today (and they pay you for the costs and risks you bear today). That means a constant flow of money daily if the CFD is held longer. The party taking more risk pays a daily fee to the party taking less risk.
These three things are three different ways of viewing precisely the same thing.