Re: [HACKERS] WIP: Data at rest encryption - Mailing list pgsql-hackers

On Thu, Jun 15, 2017 at 12:06 PM, Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
> Making this work well would be a major part of the usability story that
> this is being sold on.  If the proposed solution is that you can cobble
> together a few bits of shell, then not only is that not very
> user-friendly, it also won't work consistently across platforms, won't
> work under systemd (launchd? Windows service?), and might behave
> awkwardly under restricted environments where there is no terminal or
> only a limited OS environment.  Moreover, it leaves the security aspects
> of that part of the solution (keys lingering in memory or in swap) up to
> the user.
>
> There was a discussion a while ago about how to handle passphrase entry
> for SSL keys.  The conclusion was that it works pretty crappily right
> now, and several suggestions for improvement were discussed.  I suggest
> that fixing that properly and with flexibility could also yield a
> solution for encryption key entry.

That sounds like a good idea to me.

However, I'd like to disagree with the idea that key management is the
primary way in which this feature would improve usability.  To me, the
big advantage is that you don't need to be root (also, we can have
more consistency in behavior across operating systems).  I disagree
vigorously with the idea that anyone who wants to encrypt their
PostgreSQL database should just get root privileges on the system and
use an encrypted filesystem.  In some environments, "just get root
privileges" is not something that is very easy to do; but even if you
*have* root privileges, you don't necessarily want to have to use them
just to install and configure your database.

Right now, I can compile and install PostgreSQL from my own user
account, run initdb, and start it up.  Surely everyone PostgreSQL
developer in the world - and a good number of users - would agree that
if I suddenly needed to run initdb as root, that would be a huge
usability regression.  You wouldn't even be able to run 'make check'
without root privileges, which would suck.  In the same way, when we
removed (for most users) the need to tune System V shared memory
parameters (b0fc0df9364d2d2d17c0162cf3b8b59f6cb09f67), a lot of users
were very happy precisely because it eliminated a setup step that
formerly had to be done as root.  It is very reasonable to suppose
that users who need encryption will similarly be happy if they no
longer need to be root to get an encrypted PostgreSQL working.  Sure,
that's only a subset of users rather than all of them, but it's the
same kind of issue.

Also, I don't think we should be presenting filesystem encryption and
built-in encryption as if they were two methods of solving the exact
same problem.  In some scenarios, they are in fact solving the same
problem.  However, it's entirely reasonable to want to use both.  For
example, the hard disk in my laptop is encrypted, because that's a
thing Apple does.  If somebody steals the hard disk out of my laptop,
they may have some difficulty recovering the contents. However, as
Stephen also mentioned, that has not deterred me from putting a
passphrase on my SSH keys.  There are situations in which the
passphrase provides protection that the whole-drive encryption won't.
For example, if I copy my home directory onto a USB stick and then
copy it from there to a new laptop, somebody might steal the USB
stick.  If they manage to do that, they will get most of my files, but
they won't get my SSH keys, or at least not without guessing my
passphrase.  Similarly, if I foolishly walk away from my laptop in the
presence of some nefarious person (say, Magnus) without locking it,
that person can't steal my keys.  That person might be able to
impersonate me for as long as I'm away from the laptop, if the keys
are loaded into my SSH agent, but not afterwards.  The issues for the
database are similar.  You might want one of these things or the other
or both depending on the exact situation.

To be honest, I find the hostility toward this feature a bit baffling.
The argument seems to be essentially that we shouldn't have this
feature because we'd have to maintain the code and many of the same
goals could be accomplished by using facilities that already exist
outside the database server.  But that's also true for parallel query
(cf. Stado), logical replication (cf. Slony, Bucardo, Londiste),
physical replication (cf. DRBD), partitioning (cf. pg_partman), RLS
(cf. veil), and anything that could be written as application logic
(eg. psql's \if ... \endif, every procedural language we have,
user-defined functions themselves, database-enforced constraints,
FDWs).  Yet, in every one of those cases, we find it worthwhile to
have the feature because it works better and is easier to use when
it's built in.  I don't think that a patch for this feature is likely
to be bigger than (or even as large as) the patches for logical
replication or parallel query, and it will probably be less work to
maintain going forward than either.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company