Bruce, * Bruce Momjian (br...@momjian.us) wrote: > On Tue, Jun 13, 2017 at 11:04:21AM -0400, Stephen Frost wrote: > > > Also, in the use case you describe, if you use pg_basebackup to make a > > > direct encrypted copy of a data directory, I think that would mean you'd > > > have to keep using the same key for all copies. > > > > That's true, but that might be acceptable and possibly even desirable in > > certain cases. On the other hand, it would certainly be a useful > > feature to have a way to migrate from one key to another. Perhaps that > > would start out as an off-line tool, but maybe we'd be able to work out > > a way to support having it done on-line in the future (certainly > > non-trivial, but if we supported multiple keys concurrently with a > > preference for which key is used to write data back out, and required > > that checksums be in place to allow us to test if decrypting with a > > specific key worked ... lots more hand-waving here... ). > > As I understand it, having encryption in the database means the key is > stored in the database, while having encryption in the file system means > the key is stored in the operating system somewhere.
Key management is an entirely independent discussion from this and the proposal from Ants, as I understand it, is that the key would *not* be in the database but could be anywhere that a shell command could get it from, including possibly a HSM (hardware device). Having the data encrypted by PostgreSQL does not mean the key is stored in the database. > Of course, if the > key stored in the database is visible to someone using the operating > system, we really haven't added much/any security --- I guess my point > is that the OS easily can hide the key from the database, but the > database can't easily hide the key from the operating system. This is correct- the key must be available to the PostgreSQL process and therefore someone with privileged access to the OS would be able to retrieve the key, but that's also true of filesystem encryption. Basically, if the server is doing the encryption and you have the ability to read all memory on the server then you can get the key. Of course, if you can read all memory then you can just look at shared buffers and you don't really need to bother yourself with the key or the encryption, and it doesn't make any difference if you're encrypting in the database or in the filesystem. That attack vector is not one which this is intending to address. > Of course, if the storage is split from the database server then having > the key on the database server seems like a win. However, I think a db > server could easily encrypt blocks before sending them to the SAN > server. This would not work for NAS, of course, since it is file-based. The key doesn't necessairly have to be stored anywhere on the server- it just needs to be kept in memory while the database process is running and made available to the database at startup, unless an external system is used to perform the encryption, which might be possible with an extension, as discussed. In some environments, it might be acceptable to have the key stored on the database server, of course, but there's no requirement for the key to be stored on the database server or in the database at all. > I have to admit we tend to avoid heavy-API solutions that are designed > just to work around deployment challenges. Commercial databases are > fine in doing that, but it leads to very complex products. I'm not following what you mean here. > I think the larger issue is where to store the key. I would love for us > to come up with a unified solution to that and then build encryption on > that, including all-cluster encryption. Honestly, key management is something that I'd rather we *not* worry about in an initial implementation, which is one reason that I liked the approach discussed here of having a command which runs to provide the key. We could certainly look into improving that in the future, but key management really is a largely independent issue from encryption and it's much more difficult and complicated and whatever we come up with would still almost certainly be usable with the approach proposed here. > One cool idea I have is using public encryption to store the encryption > key by users who don't know the decryption key, e.g. RSA. It would be a > write-only encryption option. Not sure how useful that is, but it > easily possible, and doesn't require us to keep the _encryption_ key > secret, just the decryption one. The downside here is that asymmetric encryption is much more expensive than symmetric encryption and that probably makes it a non-starter. I do think we'll want to support multiple encryption methods and perhaps we can have an option where asymmetric encryption is used, but that's not what I expect will be typically used. Thanks! Stephen
Description: Digital signature