Re: Have an encrypted pgpass file

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