I want Brave to permanently store cookies only for manually approved sites. For other sites, delete all cookies when I close all non-private windows (the same concept that is used in private mode). Note that changing something in Settings >> Privacy and security >> Site Settings >> Cookies has no effect at all (probably this is a bug).
P.S. If you are someone who can affect decisions about Brave Browser features, I highly recommend reading this extensive post which is also related to privacy.