Re: Have an encrypted pgpass file

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(dot)munro(at)enterprisedb(dot)com>
wrote:

> On Tue, Jul 24, 2018 at 2:10 PM, Craig Ringer <craig(at)2ndquadrant(dot)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
>