Furthermore, the more "useful" they become, with browser extensions etc., the greater the attack surface becomes.
Because of this fear, I generate random passwords and memorise them, which is not ideal.
In my password vault I have over 500 logins, each with unique password. Unless you are reusing passwords it doesn't seem feasible to have a unique password for each site (or you have a much better memory than me :D).
In terms of your fears, I remember unique passwords that aren't stored in the vault for things like my main email account and banks. Everything else goes in the vault - if it gets lost, at worse I can reset the password via email, and if it gets stolen at worse they get access to my Spotify account.
Also, the more popular the password manager becomes, the more valuable cracking it becomes. One exploit can yield the email, banking, workplace, confidential document, and other passwords for many millions of people. If you are an attacker, it would be worth it to have the exploit on file, proactively, for the next time you attack someone using that password manager.
I agree, in the sense that one successful attack on the supposed centralized database containing all user credentials would have a high ROI.
But it also applies to local password managers. If 20 million people use the same password manager and I have an exploit for it, if I'm in the business of stealing data I'm likely to find a use for my exploit.
Someone is going to exploit my local password manager remotely?
I don't know - who are you?
Even if you are not an especially valuable target, malware is pretty widespread. It steals credit card numbers and other data; if the attacker had a hack for a popular password manager, why wouldn't they go for it?
It may be unlikely, but it's still putting all eggs in one basket. Just one failure, and you are truly fucked.
If you have an eidetic memory and can remember 20 digit random passwords for every service after securely generating them then keepassx increases your risk.
If however you behave like a 'normal' user and use the same one or three passwords on everything I'd estimated keepassx improves your security.
The truth is that I don't give a fuck about losing 90% of accounts I use (and I guess I'm not the only one). Many of them I could even give you myself as a birthday present. Using password manager as a rule of thumb would imply that these accounts are as important as the most important ones. Which is nonsense. Even if we discard all the disposable accounts, I still doubt that losing your twitter would hit you nearly as hard as losing your main email account or bank account.
However, exposing that all these trash accounts are mine might make me feel uncomfortable.
If that makes sense, then we must actually stop using the rule "password managers FTW" and start using the rule "consider how important is every given account to you, and treat it accordingly, chosing between several kdbx files". Which is much more complicated rule, obviously. I would even say it creates much higher mental load than remembering several sufficiently complicated passwords.
So I just use keepass (synced on spideroak, although I am considering alternatives as they still can't be arsed to do 2FA and I am still sceptical of some of their claims). No browser extension, just the app on my PC (and phone).
If it is a site I use regularly, I actually know the password because I generate it in a memorable way. if it isn't, I unlock the database, find the entry, copy it, and paste it.
To be compromised, I need:
1. My device to already be compromised, at which point I am boned anyway
2. Someone to get a hold of the database through stealing my device: I would know about this and would take action for the more sensitive passwords while filing a police report. The hope is that it would take long enough to compromise the database that I would have time
3. Someone to compromise spideroak and get the database. This is my big worry right now as I won't know about this (see my concerns over spideroak), but I put this in the "it is worth the risk" category as it avoids desync between my devices. But, as mentioned, I am looking in to alternatives or at least an extra layer of security/encryption to add in between.