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

On Thu, Jun 15, 2017 at 4:29 PM, Bruce Momjian <bruce@momjian.us> wrote:
> I think the big win for having OS features in the database is
> selectivity --- the ability to selectively apply a feature to part of
> the database.  This is what you are doing by putting a password on your
> SSH key, and my idea about row encryption.

I agree. I think we will eventually want to be able to apply
encryption selectively, although I don't think we need to have that
feature in a first patch.  One problem is that if you don't encrypt
the WAL, there's not much point in encrypting the table data, so it
becomes tricky to encrypt some things and not others.  However, I am
sure we can eventually solve those problems, given enough time and
development effort.

> It is also a question of convenience.  If SSH told users they have to
> create an encrypted volume to store their SSH keys with a password, it
> would be silly, since the files are so small compared to a file system.
> I think the assumption is that any security-concerned deployment of
> Postgres will already have Postgres on its own partition and have the
> root administrator involved.  I think it is this assumption that drives
> the idea that requiring root to run Postgres doesn't make sense, but it
> does to do encryption.

I don't think that's a particularly good assumption, though,
especially with the proliferation of virtual and containerized
environments where access to root privileges tends to be more
circumscribed.  Also, there are a lot of small databases out there
that you might want to be able to encrypt without encrypting
everything on the filesystem.  For example, there are products that
embed PostgreSQL.  If a particular PostgreSQL configuration requires
root access, then using that configuration means that the installing
the product which contains it also requires root access.  Installing
the product means changing /etc/fstab, and uninstalling it means
reversing those changes.  That's very awkward.  I agree that if you've
got a terabyte of sensitive data, you probably want to encrypt the
filesystem and involve the DBA, but there are still people who have a
gigabyte of sensitive data.  For those people, a separate filesystem
likely doesn't make sense, but they may still want encryption.

> Also, there is the sense that security requires
> trust of the root user, while using Postgres doesn't require the root
> user to also use Postgres.

I don't understand this.  It is certainly true that you're running
binaries owned by root, the root user could Trojan the binaries and
break any security you think you have.  But that problem is no better
or worse for PostgreSQL than anything else.

> One serious difference between in-database-encryption and SSH keys is
> that the use of passwords for SSH is well understood and reasonable to
> use, while I think we all admit that use of passwords for database
> objects like SSL keys is murky.  Use of keys for OS-level encryption is
> a little better handled, but not as clean as SSH keys.

Peter pointed out upthread that our handling of SSL passphrases leaves
a lot to be desired, and that maybe we should fix that problem first;
I agree.  But I don't think this is any kind of intrinsic limitation
of PostgreSQL vs. encrypted filesystems vs. SSH; it's just a
quality-of-implementation issue.

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