how does bitcoin's 6 confirmation really help?
Generally, all confirmations are relativistic: any chaintip could still be replaced with a heavier chain! However, reorganizations become increasingly costly and unlikely the more proof of work is piled on a block.
You are right, an attacker exerting a majority-attack can censor or rollback any transactions, and thus perform a doublespend-attack to return a payment they previously made to another party to themselves (also see What can an attacker with 51% of hash power do?).
If you are receiving payments that are orders of magnitudes larger than block rewards, you should consider additional precautions (e.g. KYC, collateral, waiting for more confirmations). The occurrence of a sustained majority attack on Bitcoin could be considered a fatal scenario for Bitcoin: no transaction could be trusted anymore at any number of confirmations.
You may have read that Bitcoin requires the majority of the hashrate to be &quot;honest&quot; to work. Honest in this context means &quot;not collaborating to attack the network&quot; or &quot;independently creating good-faith blocks&quot;. Fortunately, it is rather expensive to control the majority of the hashrate. Each block reward is currently worth about $70,000, so a six block majority attack would likely cost $350,000+ in electricity cost alone. Additionally, it would require access to a majority of highly coveted and expensive hardware. Industrial Bitcoin miners have made enormous investments into application specific integrated circuits (ASICs), which can be used for nothing else than SHA-256d mining. The most likely way for a majority attack to come to pass on Bitcoin would be for someone to take over the mining controllers of multiple of the largest mining pools. I would hope that this would be discovered and remedied quickly.
That is not to say that your majority attack scenario hasn't come to pass before. Especially minority forks of larger networks, or other coins that use the same hashing algorithm than bigger networks have been majority attacked in the past. For example Bitcoin Gold was attacked with a 1,300 block reorganization in January 2020.