Indeed having unencrypted password lying (.pgpass or PGPASSWORD or -W) around is making my auditors unhappy, and forcing me to enter the password over and over again. With a simple test it seems the password entered by the user also stays in memory, since it is able to reset a broken connection. Finding the password in memory is not trivial, but prevention is always preferred.
It might be an idea to wipe the password after the login, and decrypt/read it again if it needs to reconnect. Would this make the solution more secure? I had a quick look at the code and the patch would stay compact. Please let me know of doing this would make sense. Regards, Marco On Tue, Jul 24, 2018 at 4:56 AM Thomas Munro <thomas.mu...@enterprisedb.com> wrote: > On Tue, Jul 24, 2018 at 2:10 PM, Craig Ringer <cr...@2ndquadrant.com> > wrote: > >> Grabbing it from a process's memory is a bit harder than grabbing > contents > >> of a file, but not much harder. If the agent is remote then that's > harder, > >> but you can just ask the script to decrypt the pgpass for you, so > again, not > >> much of a win. > >> > >> Even with a hardware crypto offload device the advantage here seems to > be > >> mainly limited to making it harder to capture data from backups or > >> file-lifting attacks. Anything that can execute code or commands on the > host > >> can still get the credentials. > > > > To be clear I'm not saying not to do this. I think it'd make more sense > to > > do it via an agent and socket, where libpq learns to ask the agent for > > credentials (and certs!). That way we could finally support libnss, etc. > > It's not perfect, and it doesn't magically make unattended storage > secure, > > but it sure helps slow down file-based password theft. > > Yeah, the idea that you can defend yourself against root is obviously > not a good one. But the Apple keychain example (see the commands I > showed earlier) does seem to protect you against some threat > scenarios: if your computer is stolen, that password can't be > extracted from the disk. You'd need to unlock the keychain first by > logging in. It's not 'unattended': you have to be in attendance to > unlock the keychain. > > I'm deeply suspicious of unattended use cases for encrypted passwords > though. I have heard requests for this. Encrypting it with a key > that is present in the configuration file, derived from well known > things, or stored in a nearby file doesn't change that. The encrypted > secret + easily available key is still a secret you must protect > exactly as hard. I suspect it is a misapplication of the advice that > you should never store (other people's) passwords in your database -- > instead you should store something that allows you to check if *they* > have the password, without storing the password itself. But that > doesn't mean that you should throw away your own passwords: instead > you should recognise that they are secrets, and treat them as such. > > An idea I wondered about: if the goal is to allow psql (not libpq) to > use a secret from <insert your favourite password keeper technology>, > then perhaps psql could have a machine-friendly > --read-password-from-stdin feature for use by wrapper scripts (it > seems to be hard to send passwords to -W, though maybe I'm just doing > something stupid). Or if you could wrap it in a script that provides > one end of a named pipe as PGPASSFILE (is that 'on disk'?). Or uses a > temporary file in tmpfs with swap not configured (is that 'on disk' > yet? Yeah, I bet that's against the rules...) Of course you could > write a wrapper script that sets PGPASSWORD, but some people don't > like putting secrets in environment variables. Supposedly there are > operating systems where anyone can see your environment (which?), but > on the systems I know only root can. Then you run into the thorny > question of why you don't trust root not to peer at your > /proc/{$PID}/environ (or local equivalent), but do trust them not to > core dump your whole process and kernel. (It is interesting that > Linux pam_exec.so chooses to pass the username via env var PAM_USER > but send the password via a pipe connected to stdin, considering all > the ways root could intercept that.) > > -- > Thomas Munro > http://www.enterprisedb.com >