Assuming I pay them down completely, and then destroy the card or
account, what's the catch?
For you it is not so good because it will grow your credit report enormously. It will also affect the score: each new account opened, each new inquiry - reduce your score. The more of those and the more frequent they are - the deeper the dip.
So the promoters know (assume, rather) that you would not do that, as it will harm you (in short term) and eventually will drive you out of the range that they accept. At some point if you continue doing this - you will no longer be approved.
It seems to me those companies are betting me that I'll lose self
control and use their cards.
Not necessarily. It is not in your best interest to get the card and then cancel it, so this means that a rational person will not cut up the card so quickly (especially if it's a no-fee card), and since it stays in the wallet it is likely to be used.
For the reward cards that do have fees, they add benefits to compensate you for these fees. For example, airline cards allow getting free/heavily discounted tickets, free luggage, free lounge access etc. You might want to consider keeping the card for these benefits despite the fees, and the benefits are virtually no-cost to provide. So again, they make you keep the card and since you already have it - it will occasionally be used.
If you lose control and use it extensively, they'll benefit from the interest. If not - they'll just get the charge fees. Either way, they'll earn something.
Is that all there is to it, or is there some sort of security behind
the scenes (credit report?) preventing me from exploiting all the
rewards I can?
As I said, doing this too often will lead to the credit card issuers to not approve your applications.
Also, many times the sign-up bonuses are for new clients, which means you cannot be an existing client, and also an ex-client who recently left them (IIRC usually they look back at least 6 months in these cases). There are not so many card issuers, so you may find yourself blacked out by the virtue of being too recent ex-customer and thus not eligible.