Re: [HACKERS] WIP: Data at rest encryption

2017-06-19 Thread Robert Haas
On Mon, Jun 19, 2017 at 12:30 PM, Robert Haas  wrote:
> On Thu, Jun 15, 2017 at 7:51 PM, Alvaro Herrera
>  wrote:
>> I thought we called it "incremental development".  From the opposite
>> point of view, would you say we should ban use of passphrase-protected
>> SSL key files because the current user interface for them is bad?
>
> I think that we've got a number of features which exist in the tree
> today only because either (a) our standards were lower at the time
> that those features were committed than they are today or (b) we
> didn't realize how much trouble those features were going to create.
> Just because we don't want to hose the people already using those
> features does not mean that we want more features engineered to that
> same quality level.  Obviously, there's room for debate in any
> particular case about how reasonable it is to expect someone who wants
> to implement A to also improve B, and, well, maybe handling thing as
> we do SSL certificates is good enough for this feature, too.  I find
> myself a bit skeptical about that, though.  It preclude as lot of
> things we might want to do.  You're not going to be able to interface
> with some external key management server that way, nor do encryption
> of only part of the database, nor have multiple keys for different
> parts of the database using that kind of setup.
>
> One could argue that can all be added later, but I think there's a
> question about how much that's going to affect the code structure.
> Surely we don't want to install encryption v1 and then find that, by
> not considering key management, we've made it really hard to get to
> v2, and that it basically can't be done without ripping the whole
> implementation out and replacing it.  Maybe the database needs, at
> some rather low level, a concept of whether the encryption key (or an
> encryption key) is available yet, and maybe you get out of considering
> that by deciding you're just going to prompt very early in startup,
> but now when someone comes along and wants to improve things later,
> and they've got to try to go find all of the code that depends on the
> assumption that the key is always available and fix it.  That could be
> pretty annoying to validate.  I think it's better to give at least
> some consideration to these key management questions from the
> beginning, lest we back ourselves into a corner.  Whether or not the
> SSL-passphrase style implementation is above or below the level we'd
> consider a minimally viable feature, it's surely not where we want to
> end up, and we shouldn't do anything that makes it likely that we'll
> get stuck at precisely that point.
>
> Also, practically, I think that type of solution is going to be
> extremely difficult to use for most people.  It means that the
> database can't be started by the system startup scripts; you'll have
> to log into the PG OS user account and launch the database from there.
> IIUC, that means it won't be able to be controlled by things like
> systemd, that just know about start and stop, but not about ask for a
> password in the middle.  Maybe I'm wrong about that, though.  And
> certainly, there will be some users for whom starting the database
> manually and prompting for a password will be good enough, if not
> ideal.  But for people who want to fetch the encryption key from a key
> management server, which I bet is a lot of people, that's not really
> going to be good enough.  I'm not really sure that rushing a first
> patch that "works" for sufficiently small values of "works" is
> actually going

...to move us forward very much.

>> I have no use for data-at-rest encryption myself, but I wouldn't stop
>> development just because the initial design proposal doesn't include
>> top-notch key management.

I agree with that, but there's a difference between "not top-notch"
and "pretty bad".

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-19 Thread Robert Haas
On Thu, Jun 15, 2017 at 7:51 PM, Alvaro Herrera
 wrote:
> I thought we called it "incremental development".  From the opposite
> point of view, would you say we should ban use of passphrase-protected
> SSL key files because the current user interface for them is bad?

I think that we've got a number of features which exist in the tree
today only because either (a) our standards were lower at the time
that those features were committed than they are today or (b) we
didn't realize how much trouble those features were going to create.
Just because we don't want to hose the people already using those
features does not mean that we want more features engineered to that
same quality level.  Obviously, there's room for debate in any
particular case about how reasonable it is to expect someone who wants
to implement A to also improve B, and, well, maybe handling thing as
we do SSL certificates is good enough for this feature, too.  I find
myself a bit skeptical about that, though.  It preclude as lot of
things we might want to do.  You're not going to be able to interface
with some external key management server that way, nor do encryption
of only part of the database, nor have multiple keys for different
parts of the database using that kind of setup.

One could argue that can all be added later, but I think there's a
question about how much that's going to affect the code structure.
Surely we don't want to install encryption v1 and then find that, by
not considering key management, we've made it really hard to get to
v2, and that it basically can't be done without ripping the whole
implementation out and replacing it.  Maybe the database needs, at
some rather low level, a concept of whether the encryption key (or an
encryption key) is available yet, and maybe you get out of considering
that by deciding you're just going to prompt very early in startup,
but now when someone comes along and wants to improve things later,
and they've got to try to go find all of the code that depends on the
assumption that the key is always available and fix it.  That could be
pretty annoying to validate.  I think it's better to give at least
some consideration to these key management questions from the
beginning, lest we back ourselves into a corner.  Whether or not the
SSL-passphrase style implementation is above or below the level we'd
consider a minimally viable feature, it's surely not where we want to
end up, and we shouldn't do anything that makes it likely that we'll
get stuck at precisely that point.

Also, practically, I think that type of solution is going to be
extremely difficult to use for most people.  It means that the
database can't be started by the system startup scripts; you'll have
to log into the PG OS user account and launch the database from there.
IIUC, that means it won't be able to be controlled by things like
systemd, that just know about start and stop, but not about ask for a
password in the middle.  Maybe I'm wrong about that, though.  And
certainly, there will be some users for whom starting the database
manually and prompting for a password will be good enough, if not
ideal.  But for people who want to fetch the encryption key from a key
management server, which I bet is a lot of people, that's not really
going to be good enough.  I'm not really sure that rushing a first
patch that "works" for sufficiently small values of "works" is
actually going

> I have no use for data-at-rest encryption myself, but I wouldn't stop
> development just because the initial design proposal doesn't include
> top-notch key management.


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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-18 Thread Tomas Vondra

Hi all,

I've noticed this thread got resurrected a few days ago, but I haven't 
managed to read all the messages until today. I do have a bunch of 
comments, but let me share them as a single consistent message instead 
of sending a thousand responses to individual messages.



1) Threat model
---

Firstly, I think the thread would seriously benefit from an explanation 
and discussion of the threat model - what types of attacks it's meant to 
address, and what attacks it can't defend against.


My understanding is that data-at-rest encryption generally addresses 
only the "someone stole the disk" case and pretty much nothing else.


Moreover, I claim that encryption implemented at the database-level is 
strictly weaker compared to LUKS or encrypted disks, because it simply 
reveals a lot more information even without decryption (file sizes, 
timestamps, etc.)


That is a serious issue in practice, and researches have been proving 
that for a long time now. I do recommend this paper from Cornell Tech as 
a great starting point (it cites many papers relevant to this thread):


Why Your Encrypted Database Is Not Secure
Paul Grubbs, Thomas Ristenpart, Vitaly Schmatikov
http://eprint.iacr.org/2017/468.pdf

The paper explains how encryption schemes on general-purpose databases 
fail, due to exactly such side-channels. MVCC, logging and other side 
channels turn all attackers into "persistent passive attackers".


Now, this does not mean the feature is useless - nothing is perfect, and 
security is not a binary feature. It certainly makes attacks mode 
difficult compared to plaintext database. But it's untrue that it's 
basically LUKS, just implemented at the database level.


I'm not suggesting that we should not pursue this idea, but the threat 
model is a crucial piece of information missing in this thread.



2) How do other databases do it?


It was repeatedly mentioned that other databases support this type of 
encryption. So how do they deal with the hard parts? For example how do 
they get and protect the encryption key?


I agree with Stephen that we should not require a full key management 
from v1 of the patch, that's an incredibly difficult thing. And it 
largely depends on the hardware (e.g. it should be possible to move the 
key to TrustZone on ARM / SGX on Intel).



3) Why do users prefer this to FDE?
---

I suppose we're discussing this feature because we've been asked about 
it by users/customers who can't use FDE. Can we reach to them and ask 
them about the reasons? Why can't they use FDE?


It was mentioned in the thread that the FDE solutions are not portable 
between different systems, but honestly - is that an issue? You probably 
can't copy the datadir anyway due locale differences anyway. If you're 
running multiple operating systems, FDE is just one of many differences.



4) Other solutions?
---

Clearly, FDE (at the block device level) and DB-level encryption are not 
the only solutions. There are also filesystems-level solutions now, for 
example.


ext4 (since kernel 4.1) and f2fs (since kernel 4.2) allow encryption at 
directory level, are transparent to the user space, and include things 
like key management (well, that's provided by kernel). NTFS can do 
something quite similar using EFS.


https://lwn.net/Articles/639427/

https://blog.quarkslab.com/a-glimpse-of-ext4-filesystem-level-encryption.html

Of course, if you happen to use another filesystem (e.g. XFS), this 
won't work for you. But then there's eCryptfs, for example:


https://en.wikipedia.org/wiki/ECryptfs


regards

--
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-16 Thread Bruce Momjian
On Fri, Jun 16, 2017 at 11:06:39AM +0300, Konstantin Knizhnik wrote:
> Encryption is much easier to implement than compression, because it is not
> changing page size. So I do not see any "complexity and flexibility
> challenges" here.
> Just for reference I attached to this mail our own encryption patch. I do

I didn't see you using CPU AES instructions, which can improve
performance by 3-10x.  Is there a reason?

> Postgres buffer manager interface significantly simplifies integration of
> encryption and compression. There is actually single path through which data
> is fetched/stored to the disk.
> It is most obvious and natural solution to decompress/decrypt data when it
> is read from the disk to page pool and compress/encrypt it when it is
> written back. Taken in account that memory is cheap now and many databases
> can completely fit in memory, storing pages in the buffer cache in plain
> (decompressed/decrypted) format allows to minimize overhead of
> compression/encryption and its influence on performance. For read only
> queries working with cached data performance will be exactly the same as
> without encryption/compression.
> Write speed for encrypted pages will be certainly slightly worse, but still
> encryption speed is much higher than disk IO speed.

Good point.

> I do not think that pluggable storage API is right approach to integrate
> compression and especially encryption. It is better to plugin encryption
> between buffer manager and storage device,
> allowing to use  it with any storage implementation. Also it is not clear to
> me whether encryption of WAL can be provided using pluggable storage API.

Yes, you are completely correct.  I withdraw my suggestion of doing it
as plugin storage.

> The last discussed question is whether it is necessary to encrypt temporary
> data (BufFile). In our solution we encrypt only main fork of non-system
> relations and do no encrypt temporary relations. It may cause that some
> secrete data will be stored at this disk in non-encrypted format. But
> accessing this data is not trivial. You can not just copy/stole disk, open
> database and do "select * from SecreteTable": you will have to extract data
> from raw file yourself. So looks like it is better to allow user to make
> choice whether to encrypt temporary data or not.

If we go forward with in-db encryption, I think we are going to have to
have a discussion about what parts of PGDATA need to be encrypted,
i.e., I don't think pg_clog needs encryption.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-16 Thread Bruce Momjian
On Thu, Jun 15, 2017 at 08:08:05PM -0400, Bruce Momjian wrote:
> On Thu, Jun 15, 2017 at 04:56:36PM -0700, Andres Freund wrote:
> > how few concerns about this feature's complexity / maintainability
> > impact have been raised.
> 
> Yeah, I guess we will just have to wait to see it since other people are
> excited about it.  My concern is code complexity and usability
> challenges, vs punting the problem to the operating system, though
> admittedly there are some cases where that is not possible.

I know some OS's can create file systems inside files.  Can you encrypt
such file storage as non-root?  I assume that is just too odd.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-16 Thread Konstantin Knizhnik



On 16.06.2017 03:08, Bruce Momjian wrote:


Yeah, I guess we will just have to wait to see it since other people are
excited about it.  My concern is code complexity and usability
challenges, vs punting the problem to the operating system, though
admittedly there are some cases where that is not possible.



Let me also share my opinion about encryption and compression support at 
database level.
PostgresPro Enterprise does support both. I have made presentation about 
it at PgConn 2016 in Tallinn.
I was a little bit surprised that there were more questions about 
encryption than about compression.
But right now we have several customers which are using compression and 
none of them use encryption (just because them do not need
to protect their databases). But I absolutely sure that there are many 
Postgres users which first of all need to protect their data.


Encryption is much easier to implement than compression, because it is 
not changing page size. So I do not see any "complexity and flexibility 
challenges" here.
Just for reference I attached to this mail our own encryption patch. I 
do not want to propose it as alternative to Aasmas patch: it is less 
flexible and doesn't support encryption of WAL, just encryption of 
relation data. Also it doesn't allow custom encryption libraries: AES 
implementation is embedded. Encryption cipher is taken from environment 
variable. At Tallin's conferences I was informed about possible security 
issue with passing key through environment variable: it is possible to 
inspect server's environment variables using plpython/plperl stored 
procedure.
This is why we unset this environment variable after reading. I am not 
expect in security, but I do not know other issues with such solution.


Concerning the question whether to implement compression/encryption on 
database level or rely on OS, my opinion is that there are many 
scenarios where it is not possible or is not desirable to use OS level 
encryption/protection. It first of all includes cloud installations and 
embedded applications.  I do not want to repeat arguments already 
mentioned in this thread.
But the fact is that there are many people which really need 
compression/encryption support and them can not or do not want to 
redirect this aspects to OS. Almost all DBMSes are supporting 
compression encryption, so lack of this features in Postgres definitely 
can not be considered as Postgres advantage.


Postgres buffer manager interface significantly simplifies integration 
of encryption and compression. There is actually single path through 
which data is fetched/stored to the disk.
It is most obvious and natural solution to decompress/decrypt data when 
it is read from the disk to page pool and compress/encrypt it when it is 
written back. Taken in account that memory is cheap now and many 
databases can completely fit in memory, storing pages in the buffer 
cache in plain (decompressed/decrypted) format allows to minimize 
overhead of compression/encryption and its influence on performance. For 
read only queries working with cached data performance will be exactly 
the same as without encryption/compression.
Write speed for encrypted pages will be certainly slightly worse, but 
still encryption speed is much higher than disk IO speed.


So I do not think that it is really necessary to support encryption of 
some particular tables, storing "non-secrete" data in plain format 
without encryption. It should not cause noticeable  improve of 
performance, but may complicate implementation and increase possibility 
of leaking secure data.


I do not think that pluggable storage API is right approach to integrate 
compression and especially encryption. It is better to plugin encryption 
between buffer manager and storage device,
allowing to use  it with any storage implementation. Also it is not 
clear to me whether encryption of WAL can be provided using pluggable 
storage API.


The last discussed question is whether it is necessary to encrypt 
temporary data (BufFile). In our solution we encrypt only main fork of 
non-system relations and do no encrypt temporary relations. It may cause 
that some secrete data will be stored at this disk in non-encrypted 
format. But accessing this data is not trivial. You can not just 
copy/stole disk, open database and do "select * from SecreteTable": you 
will have to extract data from raw file yourself. So looks like it is 
better to allow user to make choice whether to encrypt temporary data or 
not.


--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

diff --git a/src/backend/storage/file/Makefile b/src/backend/storage/file/Makefile
index d2198f2..9492662 100644
--- a/src/backend/storage/file/Makefile
+++ b/src/backend/storage/file/Makefile
@@ -12,6 +12,6 @@ subdir = src/backend/storage/file
 top_builddir = ../../../..
 include $(top_builddir)/src/Makefile.global
 
-OBJS = fd.o buffile.o copydir.o reinit.o
+OBJS 

Re: [HACKERS] WIP: Data at rest encryption

2017-06-15 Thread Tatsuo Ishii
> Yeah, I guess we will just have to wait to see it since other people are
> excited about it.  My concern is code complexity and usability
> challenges, vs punting the problem to the operating system, though
> admittedly there are some cases where that is not possible.

Oracle sells this feature only with the expensive enterprise
edition. And people actually buy it. I guess the feature is pretty
important for some users.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-15 Thread Bruce Momjian
On Thu, Jun 15, 2017 at 04:56:36PM -0700, Andres Freund wrote:
> On 2017-06-15 19:44:43 -0400, Bruce Momjian wrote:
> > Understood, but now you are promoting a feature with an admittedly-poor
> > API, duplication of an OS feature, and perhaps an invasive change to the
> > code.
> 
> *Perhaps* an invasive change to the code?  To me it's pretty evident
> that this'll be a pretty costly feature from that angle. We've quite a
> few places that manipulate on-disk files, and they'll all have to be
> manipulated. Several of those are essentially critical sections, adding
> memory allocations to them wouldn't be good, so we'll need
> pre-allocation APIs.
> 
> I've only skimmed the discussion, but based on that I'm very surprised
> how few concerns about this feature's complexity / maintainability
> impact have been raised.

Yeah, I guess we will just have to wait to see it since other people are
excited about it.  My concern is code complexity and usability
challenges, vs punting the problem to the operating system, though
admittedly there are some cases where that is not possible.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-15 Thread Andres Freund
On 2017-06-15 19:44:43 -0400, Bruce Momjian wrote:
> Understood, but now you are promoting a feature with an admittedly-poor
> API, duplication of an OS feature, and perhaps an invasive change to the
> code.

*Perhaps* an invasive change to the code?  To me it's pretty evident
that this'll be a pretty costly feature from that angle. We've quite a
few places that manipulate on-disk files, and they'll all have to be
manipulated. Several of those are essentially critical sections, adding
memory allocations to them wouldn't be good, so we'll need
pre-allocation APIs.

I've only skimmed the discussion, but based on that I'm very surprised
how few concerns about this feature's complexity / maintainability
impact have been raised.

- Andres


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-15 Thread Bruce Momjian
On Thu, Jun 15, 2017 at 07:51:36PM -0400, Alvaro Herrera wrote:
> Bruce Momjian wrote:
> > On Thu, Jun 15, 2017 at 07:27:55PM -0400, Stephen Frost wrote:
> > > I expect the same would happen with the shell-command approach suggested
> > > up-thread and the prompt-on-stdin approach too, they aren't great but I
> > > expect users would still use the feature.  As Robert and I have
> > > mentioned, there is a good bit of value to having this feature simply
> > > because it avoids the need to get someone with root privileges to set up
> > > an encrypted volume and I don't think having to use a shell command or
> > > providing the password on stdin at startup really changes that very
> > > much.
> > 
> > Understood, but now you are promoting a feature with an admittedly-poor
> > API, duplication of an OS feature, and perhaps an invasive change to the
> > code.  Those are high hurdles.
> 
> I thought we called it "incremental development".  From the opposite
> point of view, would you say we should ban use of passphrase-protected
> SSL key files because the current user interface for them is bad?
> 
> I have no use for data-at-rest encryption myself, but I wouldn't stop
> development just because the initial design proposal doesn't include
> top-notch key management.

Yes, but we have to have a plan on how to improve it.  Why add a feature
that is hard to maintain, and hard to use.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-15 Thread Stephen Frost
Bruce,

* Bruce Momjian (br...@momjian.us) wrote:
> On Thu, Jun 15, 2017 at 07:27:55PM -0400, Stephen Frost wrote:
> > I expect the same would happen with the shell-command approach suggested
> > up-thread and the prompt-on-stdin approach too, they aren't great but I
> > expect users would still use the feature.  As Robert and I have
> > mentioned, there is a good bit of value to having this feature simply
> > because it avoids the need to get someone with root privileges to set up
> > an encrypted volume and I don't think having to use a shell command or
> > providing the password on stdin at startup really changes that very
> > much.
> 
> Understood, but now you are promoting a feature with an admittedly-poor
> API, duplication of an OS feature, and perhaps an invasive change to the
> code.  Those are high hurdles.

Of those, the only one that worries me, at least, is that it might be an
invasive and difficult to maintain code change.  As Robert said, and I
agree with, "duplication of an OS feature" is something we pretty
routinly, and justifiably, do.  The poor interface is unfortunate, but
if it's consistent with what we have today for a similar feature then
I'm really not too upset with it.  If we can do better, great, I'm all
for that, but if not, then I'd rather have the feature with the poor
interface than not have it at all.

If it's an invasive code change or one which ends up being difficult to
maintain, then that's a problem.  Getting some focus on that aspect
would be great and I certainly appreciate Robert's initial review and
commentary on it.

Thanks!

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] WIP: Data at rest encryption

2017-06-15 Thread Alvaro Herrera
Bruce Momjian wrote:
> On Thu, Jun 15, 2017 at 07:27:55PM -0400, Stephen Frost wrote:
> > I expect the same would happen with the shell-command approach suggested
> > up-thread and the prompt-on-stdin approach too, they aren't great but I
> > expect users would still use the feature.  As Robert and I have
> > mentioned, there is a good bit of value to having this feature simply
> > because it avoids the need to get someone with root privileges to set up
> > an encrypted volume and I don't think having to use a shell command or
> > providing the password on stdin at startup really changes that very
> > much.
> 
> Understood, but now you are promoting a feature with an admittedly-poor
> API, duplication of an OS feature, and perhaps an invasive change to the
> code.  Those are high hurdles.

I thought we called it "incremental development".  From the opposite
point of view, would you say we should ban use of passphrase-protected
SSL key files because the current user interface for them is bad?

I have no use for data-at-rest encryption myself, but I wouldn't stop
development just because the initial design proposal doesn't include
top-notch key management.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-15 Thread Bruce Momjian
On Thu, Jun 15, 2017 at 07:27:55PM -0400, Stephen Frost wrote:
> I expect the same would happen with the shell-command approach suggested
> up-thread and the prompt-on-stdin approach too, they aren't great but I
> expect users would still use the feature.  As Robert and I have
> mentioned, there is a good bit of value to having this feature simply
> because it avoids the need to get someone with root privileges to set up
> an encrypted volume and I don't think having to use a shell command or
> providing the password on stdin at startup really changes that very
> much.

Understood, but now you are promoting a feature with an admittedly-poor
API, duplication of an OS feature, and perhaps an invasive change to the
code.  Those are high hurdles.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-15 Thread Stephen Frost
Bruce,

* Bruce Momjian (br...@momjian.us) wrote:
> On Thu, Jun 15, 2017 at 06:41:08PM -0400, Stephen Frost wrote:
> > > > > 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.
> > 
> > I'm not thrilled with asking Ants to implement a solution to SSL
> > passphrases, and generalizing it to work for this, to get this feature
> > accepted.  I assume that the reason for asking for that work to be done
> > now is because we decided that the current approach for SSL sucks but we
> > couldn't actually drop support for it, but we don't want to add other
> > features which work in a similar way because, well, it sucks.
> 
> My point is that if our support for db-level encryption is as bad as SSL
> key passwords, then it will be nearly useless, so we might as well not
> have it.  Isn't that obvious?

Well, no, because the reason we even have an approach at all for SSL key
passwords is because multiple people (myself and Magnus, at least, as I
recall) complained as we are aware of installations which are actively
using that approach.

That doesn't mean it's a great solution or that it doesn't suck- really,
I tend to agree that it does, but it's necessary because we need a
solution, it works, and users are using it.  Having a better solution
would be great and something agent-based might be the right answer (or
perhaps something where we have support for using hardware accellerators
through an existing library...).

I expect the same would happen with the shell-command approach suggested
up-thread and the prompt-on-stdin approach too, they aren't great but I
expect users would still use the feature.  As Robert and I have
mentioned, there is a good bit of value to having this feature simply
because it avoids the need to get someone with root privileges to set up
an encrypted volume and I don't think having to use a shell command or
providing the password on stdin at startup really changes that very
much.

Thanks!

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] WIP: Data at rest encryption

2017-06-15 Thread Bruce Momjian
On Thu, Jun 15, 2017 at 06:41:08PM -0400, Stephen Frost wrote:
> > > > 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.
> 
> I'm not thrilled with asking Ants to implement a solution to SSL
> passphrases, and generalizing it to work for this, to get this feature
> accepted.  I assume that the reason for asking for that work to be done
> now is because we decided that the current approach for SSL sucks but we
> couldn't actually drop support for it, but we don't want to add other
> features which work in a similar way because, well, it sucks.

My point is that if our support for db-level encryption is as bad as SSL
key passwords, then it will be nearly useless, so we might as well not
have it.  Isn't that obvious?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-15 Thread Stephen Frost
Bruce,

* Bruce Momjian (br...@momjian.us) wrote:
> On Thu, Jun 15, 2017 at 05:04:17PM -0400, Robert Haas wrote:
> > > 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.
> 
> I couldn't find a cleaner way to see it --- it is that database use
> doesn't involve the root user using it, while database security requires
> the root user to also be security-conscious.

I tend to agree with Robert here (in general).  Further, there are
certainly environments where the administrator with root access is
absolutely security conscious, but that doesn't mean that the DBA has
easy access to the root account or to folks who have that level of
access and it's much easier for the DBA if they're able to address all
of their requirements by building PG themselves, installing it, and,
ideally, encrypting the DB.

> > > 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.

I'm not thrilled with asking Ants to implement a solution to SSL
passphrases, and generalizing it to work for this, to get this feature
accepted.  I assume that the reason for asking for that work to be done
now is because we decided that the current approach for SSL sucks but we
couldn't actually drop support for it, but we don't want to add other
features which work in a similar way because, well, it sucks.

I get that.  I'm not thrilled with it, but I get it.  I'm hopeful it
ends up not being too bad, but if it ends up meaning we don't get this
feature, then I'll reconsider my position about agreeing that it's an
acceptable requirement.

> I think there are environmental issues that make password use on SSH
> easier than the other cases --- it isn't just code quality.  However, it
> would be good to research how SSH handles it to see if we can get any
> ideas.

Actually, the approach SSH uses is a really good one, imv, and one which
we might be able to leverage..  I'm not sure though.  I will say that,
in general, I like the idea of leveraging the external libraries which
handle keys and deal with encryption to make this happen as those allow
things like hardware devices to hold the key and possibly perform the
encryption/decryption, etc.

Thanks!

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] WIP: Data at rest encryption

2017-06-15 Thread Bruce Momjian
On Thu, Jun 15, 2017 at 05:04:17PM -0400, Robert Haas wrote:
> > 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.

I couldn't find a cleaner way to see it --- it is that database use
doesn't involve the root user using it, while database security requires
the root user to also be security-conscious.

> > 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.

I think there are environmental issues that make password use on SSH
easier than the other cases --- it isn't just code quality.  However, it
would be good to research how SSH handles it to see if we can get any
ideas.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-15 Thread Robert Haas
On Thu, Jun 15, 2017 at 4:29 PM, Bruce Momjian  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-15 Thread Bruce Momjian
On Thu, Jun 15, 2017 at 03:09:32PM -0400, Robert Haas wrote:
> 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.

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.

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.  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.

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.

I admit there is no hard line here, so I guess we will have to see what
the final patch looks like.  I am basing my statements on what I guess
the complexity will be.  Complexity has a cost so we will have to weigh
it when we see it.  When SSH added password access, it was probably an
easy decision because the use-case was high and the complexity was low.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-15 Thread Robert Haas
On Thu, Jun 15, 2017 at 12:06 PM, Peter Eisentraut
 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

Re: [HACKERS] WIP: Data at rest encryption

2017-06-15 Thread Peter Eisentraut
On 6/14/17 17:41, Stephen Frost wrote:
>> Relying on environment variables is clearly pretty crappy.  So if that's
>> the proposal, then I think it needs to be better.
> I don't believe that was ever intended to be the final solution, I was
> just pointing out that it's what the WIP patch did.
> 
> The discussion had moved into having a command called which provided the
> key on stdout, as I recall, allowing it to be whatever the user wished,
> including binary of any kind.
> 
> If you have other suggestions, I'm sure they would be well received.  As
> to the question of complexity, it certainly looks like it'll probably be
> quite straight-forward for users to use.

I think the passphrase entry part of the problem is actually a bit
harder than it appears.

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.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-15 Thread Robert Haas
On Wed, Jun 14, 2017 at 5:41 PM, Stephen Frost  wrote:
> I don't believe that was ever intended to be the final solution, I was
> just pointing out that it's what the WIP patch did.
>
> The discussion had moved into having a command called which provided the
> key on stdout, as I recall, allowing it to be whatever the user wished,
> including binary of any kind.
>
> If you have other suggestions, I'm sure they would be well received.  As
> to the question of complexity, it certainly looks like it'll probably be
> quite straight-forward for users to use.

To me, this reads a bit like you're still trying to shut down the
discussion here.  Perhaps I am misreading it.

Upthread, you basically said that we shouldn't talk about key
management (specifically, you said, "Key management is an entirely
independent discussion from this") which I think is a ridiculous
statement.  We have to have some kind of simple key management in
order to have the feature at all.  It does not have to be crazy
complicated, but it has to exist.

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-14 Thread Stephen Frost
Peter,

* Peter Eisentraut (peter.eisentr...@2ndquadrant.com) wrote:
> On 6/13/17 18:11, Stephen Frost wrote:
> >> Let's see a proposal in those terms then.  How easy can you make it,
> >> compared to existing OS-level solutions, and will that justify the
> >> maintenance overhead?
> > From the original post on this thread, which included a WIP patch:
> > 
> > --
> > Usage
> > =
> > 
> > Set up database like so:
> > 
> > (read -sp "Postgres passphrase: " PGENCRYPTIONKEY; echo;
> >  export PGENCRYPTIONKEY
> >  initdb -k -K pgcrypto $PGDATA )
> > 
> > Start PostgreSQL:
> > 
> > (read -sp "Postgres passphrase: " PGENCRYPTIONKEY; echo;
> >  export PGENCRYPTIONKEY
> >  postgres $PGDATA )
> > --
> 
> Relying on environment variables is clearly pretty crappy.  So if that's
> the proposal, then I think it needs to be better.

I don't believe that was ever intended to be the final solution, I was
just pointing out that it's what the WIP patch did.

The discussion had moved into having a command called which provided the
key on stdout, as I recall, allowing it to be whatever the user wished,
including binary of any kind.

If you have other suggestions, I'm sure they would be well received.  As
to the question of complexity, it certainly looks like it'll probably be
quite straight-forward for users to use.

Thanks!

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] WIP: Data at rest encryption

2017-06-14 Thread Peter Eisentraut
On 6/13/17 18:11, Stephen Frost wrote:
>> Let's see a proposal in those terms then.  How easy can you make it,
>> compared to existing OS-level solutions, and will that justify the
>> maintenance overhead?
> From the original post on this thread, which included a WIP patch:
> 
> --
> Usage
> =
> 
> Set up database like so:
> 
> (read -sp "Postgres passphrase: " PGENCRYPTIONKEY; echo;
>  export PGENCRYPTIONKEY
>  initdb -k -K pgcrypto $PGDATA )
> 
> Start PostgreSQL:
> 
> (read -sp "Postgres passphrase: " PGENCRYPTIONKEY; echo;
>  export PGENCRYPTIONKEY
>  postgres $PGDATA )
> --

Relying on environment variables is clearly pretty crappy.  So if that's
the proposal, then I think it needs to be better.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-14 Thread Peter Eisentraut
On 6/14/17 05:04, Aleksander Alekseev wrote:
> A few companies that hired system administrators that are too
> lazy to read two or three man pages is not a reason to re-implement file
> system encryption (or compression, or mirroring if that matters) in any
> open source RDBMS.

To be fair, we did implement our own compression (TOAST) and mirroring
(compared to, say, DRBD) because there were clear advantages in
simplicity and performance.  Checksumming is another area where we moved
forward in spite of arguments that the file system should do it.  So we
will need to see the whole picture.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-14 Thread Bruce Momjian
On Wed, Jun 14, 2017 at 06:41:43PM +0300, Ants Aasma wrote:
> On Wed, Jun 14, 2017 at 6:26 PM, Bruce Momjian  wrote:
> > Are you checking the CPU type or if AES instructions are enabled on the
> > CPU? I ask this because I just realized in researching my new TLS talk
> > that my BIOS defaults to AES instructions disabled, and I had to
> > manually enable it.
> 
> There is zero code for that now, but the plan was to check the CPUID
> instruction. My understanding is that it should report what is
> currently enabled on the CPU. Will double check when actually writing
> the code for the check.

Just for specifics, I have two Intel Xeon CPU E5620, but the AES
instructions were disabled for this CPU since 2012 when I bought it. 
:-(  The good news is that only recently have I forced https in some
pages so this is the first time I heavily need it.  I now have a boot
test, which returns 16:

grep -c '\' /proc/cpuinfo

> >> > I anticipate that one of the trickier problems here will be handling
> >> > encryption of the write-ahead log.  Suppose you encrypt WAL a block at
> >> > a time.  In the current system, once you've written and flushed a
> >> > block, you can consider it durably committed, but if that block is
> >> > encrypted, this is no longer true.  A crash might tear the block,
> >> > making it impossible to decrypt.  Replay will therefore stop at the
> >> > end of the previous block, not at the last record actually flushed as
> >> > would happen today.
> >>
> >> My patch is currently doing a block at a time for WAL. The XTS mode
> >
> > Uh, how are you writing partial writes to the WAL.  I assume you are
> > doing a streaming cipher so you can write in increments, right?
> 
> We were doing 8kB page aligned writes to WAL anyway. I just encrypt
> the block before it gets written out.

Oh, we do.  The beauty of streaming ciphers built on block ciphers is
that you can pre-compute the cipher to be XOR'ed with the data because
the block cipher output doesn't depend on the user data.  This is used
for SSH, for example.

> >> I think we need to require wal_log_hints=on when encryption is
> >> enabled. Currently I have not considered tearing on CLOG bits. Other
> >> SLRUs probably have similar issues. I need to think a bit about how to
> >> solve that.
> >
> > I am not sure if clog even needs to be encrypted.
> 
> Me neither, but it currently is, and it looks like that's broken in a
> "silently corrupts your data" way in face of torn writes. Using OFB
> mode (xor plaintext with pseudorandom stream for cipher) looks like it
> might help here, if other approaches fail.

I would just document the limitation and move on.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-14 Thread Ants Aasma
On Wed, Jun 14, 2017 at 6:26 PM, Bruce Momjian  wrote:
> Are you checking the CPU type or if AES instructions are enabled on the
> CPU? I ask this because I just realized in researching my new TLS talk
> that my BIOS defaults to AES instructions disabled, and I had to
> manually enable it.

There is zero code for that now, but the plan was to check the CPUID
instruction. My understanding is that it should report what is
currently enabled on the CPU. Will double check when actually writing
the code for the check.

>> > I anticipate that one of the trickier problems here will be handling
>> > encryption of the write-ahead log.  Suppose you encrypt WAL a block at
>> > a time.  In the current system, once you've written and flushed a
>> > block, you can consider it durably committed, but if that block is
>> > encrypted, this is no longer true.  A crash might tear the block,
>> > making it impossible to decrypt.  Replay will therefore stop at the
>> > end of the previous block, not at the last record actually flushed as
>> > would happen today.
>>
>> My patch is currenly doing a block at a time for WAL. The XTS mode
>
> Uh, how are you writing partial writes to the WAL.  I assume you are
> doing a streaming cipher so you can write in increments, right?

We were doing 8kB page aligned writes to WAL anyway. I just encrypt
the block before it gets written out.

>> I think we need to require wal_log_hints=on when encryption is
>> enabled. Currently I have not considered tearing on CLOG bits. Other
>> SLRUs probably have similar issues. I need to think a bit about how to
>> solve that.
>
> I am not sure if clog even needs to be encrypted.

Me neither, but it currently is, and it looks like that's broken in a
"silently corrupts your data" way in face of torn writes. Using OFB
mode (xor plaintext with pseudorandom stream for cipher) looks like it
might help here, if other approaches fail.

Regards,
Ants Aasma


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-14 Thread Bruce Momjian
On Wed, Jun 14, 2017 at 06:10:32PM +0300, Ants Aasma wrote:
> On Tue, Jun 13, 2017 at 6:35 PM, Robert Haas  wrote:
> > Performance is likely to be poor on large databases,
> > because every time a page transits between shared_buffers and the
> > buffer cache we've got to en/decrypt, but as long as it's only poor
> > for the people who opt into the feature I don't see a big problem with
> > that.
> 
> It would make sense to tune the database with large shared buffers if
> encryption is enabled. That should make sure that most shared buffers
> traffic is going to disk anyway. As for performance, I have a
> prototype assembly implementation of AES that does 3GB/s/core on my
> laptop. If we add that behind a CPUID check the overhead should be
> quite reasonable.

Are you checking the CPU type or if AES instructions are enabled on the
CPU? I ask this because I just realized in researching my new TLS talk
that my BIOS defaults to AES instructions disabled, and I had to
manually enable it.

> > I anticipate that one of the trickier problems here will be handling
> > encryption of the write-ahead log.  Suppose you encrypt WAL a block at
> > a time.  In the current system, once you've written and flushed a
> > block, you can consider it durably committed, but if that block is
> > encrypted, this is no longer true.  A crash might tear the block,
> > making it impossible to decrypt.  Replay will therefore stop at the
> > end of the previous block, not at the last record actually flushed as
> > would happen today.
> 
> My patch is currenly doing a block at a time for WAL. The XTS mode

Uh, how are you writing partial writes to the WAL.  I assume you are
doing a streaming cipher so you can write in increments, right?

> used to encrypt has the useful property that blocks that share
> identical prefix unencrypted also have identical prefix when
> encrypted. It requires that the tearing is 16B aligned, but I think
> that is true for pretty much all storage systems. That property of
> course has security downsides, but for table/index storage we have a
> nonce in the form of LSN in the page header eliminating the issue.
> 
> > So, your synchronous_commit suddenly isn't.  A
> > similar problem will occur any other page where we choose not to
> > protect against torn pages using full page writes.  For instance,
> > unless checksums are enabled or wal_log_hints=on, we'll write a data
> > page where a single bit has been flipped and assume that the bit will
> > either make it to disk or not; the page can't really be torn in any
> > way that hurts us.  But with encryption that's no longer true, because
> > the hint bit will turn into much more than a single bit flip, and
> > rereading that page with half old and half new contents will be the
> > end of the world (TM).  I don't know off-hand whether we're
> > protecting, say, CLOG page writes with FPWs.: because setting a couple
> > of bits is idempotent and doesn't depend on the existing page
> > contents, we might not need it currently, but with encryption, every
> > bit in the page depends on every other bit in the page, so we
> > certainly would.  I don't know how many places we've got assumptions
> > like this baked into the system, but I'm guessing there are a bunch.
> 
> I think we need to require wal_log_hints=on when encryption is
> enabled. Currently I have not considered tearing on CLOG bits. Other
> SLRUs probably have similar issues. I need to think a bit about how to
> solve that.

I am not sure if clog even needs to be encrypted.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-14 Thread Ants Aasma
On Tue, Jun 13, 2017 at 6:35 PM, Robert Haas  wrote:
> Of course, what would be even more useful is fine-grained encryption -
> encrypt these tables (and the corresponding indexes, toast tables, and
> WAL records related to any of that) with this key, encrypt these other
> tables (and the same list of associated stuff) with this other key,
> and leave the rest unencrypted.  The problem with that is that you
> probably can't run recovery without all of the keys, and even on a
> clean startup there would be a good deal of engineering work involved
> in refusing access to tables whose key hadn't been provided yet.

That's pretty much the reason why I decided to skip anything more
complicated for now. Anything that would be able to run recovery
without knowing the encryption key looks like an order of magnitude
more complicated to implement.

> Performance is likely to be poor on large databases,
> because every time a page transits between shared_buffers and the
> buffer cache we've got to en/decrypt, but as long as it's only poor
> for the people who opt into the feature I don't see a big problem with
> that.

It would make sense to tune the database with large shared buffers if
encryption is enabled. That should make sure that most shared buffers
traffic is going to disk anyway. As for performance, I have a
prototype assembly implementation of AES that does 3GB/s/core on my
laptop. If we add that behind a CPUID check the overhead should be
quite reasonable.

> I anticipate that one of the trickier problems here will be handling
> encryption of the write-ahead log.  Suppose you encrypt WAL a block at
> a time.  In the current system, once you've written and flushed a
> block, you can consider it durably committed, but if that block is
> encrypted, this is no longer true.  A crash might tear the block,
> making it impossible to decrypt.  Replay will therefore stop at the
> end of the previous block, not at the last record actually flushed as
> would happen today.

My patch is currenly doing a block at a time for WAL. The XTS mode
used to encrypt has the useful property that blocks that share
identical prefix unencrypted also have identical prefix when
encrypted. It requires that the tearing is 16B aligned, but I think
that is true for pretty much all storage systems. That property of
course has security downsides, but for table/index storage we have a
nonce in the form of LSN in the page header eliminating the issue.

> So, your synchronous_commit suddenly isn't.  A
> similar problem will occur any other page where we choose not to
> protect against torn pages using full page writes.  For instance,
> unless checksums are enabled or wal_log_hints=on, we'll write a data
> page where a single bit has been flipped and assume that the bit will
> either make it to disk or not; the page can't really be torn in any
> way that hurts us.  But with encryption that's no longer true, because
> the hint bit will turn into much more than a single bit flip, and
> rereading that page with half old and half new contents will be the
> end of the world (TM).  I don't know off-hand whether we're
> protecting, say, CLOG page writes with FPWs.: because setting a couple
> of bits is idempotent and doesn't depend on the existing page
> contents, we might not need it currently, but with encryption, every
> bit in the page depends on every other bit in the page, so we
> certainly would.  I don't know how many places we've got assumptions
> like this baked into the system, but I'm guessing there are a bunch.

I think we need to require wal_log_hints=on when encryption is
enabled. Currently I have not considered tearing on CLOG bits. Other
SLRUs probably have similar issues. I need to think a bit about how to
solve that.

Regards,
Ants Aasma


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-14 Thread Bruce Momjian
On Tue, Jun 13, 2017 at 06:29:20PM -0400, Stephen Frost wrote:
> > Isn't the leakage controlled by OS permissions, so is it really leakage,
> > i.e., if you can see the leakage, you probably have bypassed the OS
> > permissions and see the key and data anyway.
> 
> The case I'm mainly considering is if you somehow lose control over the
> medium in which the encrypted database resides- that is, someone steals
> the hard drive, or perhaps the hard drive is sold without properly being
> wiped first, things like that.
> 
> In such a case, there's no OS permissions to bypass because the OS is
> now controlled by the attacker.  In that case, if the key wasn't stored
> on the hard drive, then the attacker would be able to see the contents
> of the filesystem and the associated metadata, but not the contents of
> the cluster.
> 
> In that case, the distinction between filesystem-level encryption and
> PG-level encryption is that with filesystem-level encryption the
> attacker wouldn't be able to even see what files exist or any metadata
> about them, whereas with PG-level encryption that information would be
> available to the attacker.

Yes, Peter Eisentraut pointed that out in an earlier email in this
thread.  Thanks.

> > > > The benefit is allowing configuration
> > > > in the database rather than the OS?
> > > 
> > > No, the benefit is that the database administrator can configure it and
> > > set it up and not have to get an OS-level administrator involved.  There
> > > may also be other reasons why filesystem-level encryption is difficult
> > > to set up or use in a certain environment, but this wouldn't depend on
> > > anything OS-related and therefore could be done.
> > 
> > OK, my only point here is that we are going down a slippery slope of
> > implementing OS things in the database.  There is nothing wrong with
> > that but it has often been something we have avoided, because of the
> > added complexity required in the db server.
> 
> I'm not sure that I actually agree that encryption is really solely an
> OS-level function, or even that encryption at rest is solely the OS's
> job.  As a counter-example, I encrypt my SSH keys and GPG keys
> routinely, even when I'm using OS-level filesystem encryption.  Perhaps
> that's excessive of me, but, well, I don't find it so. :)

Well, I think SSH and GPG keys are a case of selective encryption, which
is where I said database encryption could really be a win, because you
can't do that outside the database.  Just to go crazy, here is something
I think would be cool for a fully or partially encrypted data row:

row data encrypted with symmetric key k
k encrypted with public key of user 1
k encrypted with public key of user 2
hash of previous fields signed by insert user

The would allow the insert user complete control over who sees the row. 
The database administrator could corrupt the row or add/remove users,
but that would be detected by the hash signature being invalid.  This is
kind of like TLS bundled in the database.  I think this is actually all
possible now too.  :-)

> > As a counter-example, we only added an external collation library to
> > Postgres when we clearly saw a benefit, e.g. detecting collation
> > changes.
> 
> Right, and there's also the potential for adding more flexibility down
> the road, which I'm certainly all for, but I see value in having even
> this initial version of the feature too.

Understood.

> > > Also, tools like pg_basebackup could be used, with nearly zero changes,
> > > I think, to get an encrypted copy of the database for backup purposes,
> > > removing the need to work out a way to handle encrypting backups.
> > 
> > I do think we need much more documentation on how to encrypt things,
> > though that is a separate issue.  It might help to document how you
> > _should_ do things now to see the limitations we have.
> 
> Improving our documentation would certainly be good, but I'm not sure
> that we can really recommend specific ways of doing things like
> filesystem-level encryption, as that's really OS-dependent and there
> could be trade-offs in different ways a given OS might provide that
> capability.  I'm not sure that having our documentation generically say
> "you should use filesystem-level encryption" would really be very
> helpful.
> 
> Perhaps I misunderstood your suggestion here though..?

I was just throwing out the idea that sometimes writing things down
shows the gaps in our feature-set --- it might not apply here.

> > > > Is the problem that you have to encrypt before sending and decrypt on
> > > > arrival, if you don't trust the transmission link?  Is that used a lot? 
> > > > Is having the db encrypt every write a reasonable solution to that?
> > > 
> > > There's multiple use-cases here.  Making it easier to copy the database
> > > is just one of them and it isn't the biggest one.  The biggest benefit
> > > is that there's cases where filesystem-level encryption isn't re

Re: [HACKERS] WIP: Data at rest encryption

2017-06-14 Thread Bruce Momjian
On Wed, Jun 14, 2017 at 04:13:57PM +0300, Aleksander Alekseev wrote:
> > > While I agree that configuring full disk encryption is not technically
> > > difficult, it requires much more privileged access to the system and
> > > basically requires the support of a system administrator. In addition,
> > > if a volume is not available for encryption, PostgreSQL support for
> > > encryption would still allow for its data to be encrypted and as others
> > > have mentioned can be enabled by the DBA alone.
> > 
> > Frankly I'm having difficulties imagining when it could be a real
> > problem. It doesn't seem to be such a burden to ask a colleague for
> > assistance in case you don't have sufficient permissions to do
> > something. And I got a strong feeling that solving bureaucracy issues of
> > specific organizations by changing PostgreSQL core in very invasive way
> > (keeping in mind testing, maintaining, etc) is misguided.
> 
> In the same time implementing a plugable storage API and then implementing
> encrypted / compressed / whatever storage in a standalone extension using
> this API seems to be a reasonable thing to do. 

Agreed, good point.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-14 Thread Aleksander Alekseev
> > While I agree that configuring full disk encryption is not technically
> > difficult, it requires much more privileged access to the system and
> > basically requires the support of a system administrator. In addition,
> > if a volume is not available for encryption, PostgreSQL support for
> > encryption would still allow for its data to be encrypted and as others
> > have mentioned can be enabled by the DBA alone.
> 
> Frankly I'm having difficulties imagining when it could be a real
> problem. It doesn't seem to be such a burden to ask a colleague for
> assistance in case you don't have sufficient permissions to do
> something. And I got a strong feeling that solving bureaucracy issues of
> specific organizations by changing PostgreSQL core in very invasive way
> (keeping in mind testing, maintaining, etc) is misguided.

In the same time implementing a plugable storage API and then implementing
encrypted / compressed / whatever storage in a standalone extension using
this API seems to be a reasonable thing to do. 

-- 
Best regards,
Aleksander Alekseev


signature.asc
Description: PGP signature


Re: [HACKERS] WIP: Data at rest encryption

2017-06-14 Thread Aleksander Alekseev
Hi Kenneth,

> > > File system encryption already exists and is well-tested.  I don't see
> > > any big advantages in re-implementing all of this one level up.  You
> > > would have to touch every single place in PostgreSQL backend and tool
> > > code where a file is being read or written.  Yikes.
> > 
> > I appreciate your work, but unfortunately I must agree with Peter.
> > 
> > On Linux you can configure the full disc encryption using LUKS /
> > dm-crypt in like 5 minutes [1]. On FreeBSD you can do the same using
> > geli [2]. In my personal opinion PostgreSQL is already complicated
> > enough. A few companies that hired system administrators that are too
> > lazy to read two or three man pages is not a reason to re-implement file
> > system encryption (or compression, or mirroring if that matters) in any
> > open source RDBMS.
> 
> While I agree that configuring full disk encryption is not technically
> difficult, it requires much more privileged access to the system and
> basically requires the support of a system administrator. In addition,
> if a volume is not available for encryption, PostgreSQL support for
> encryption would still allow for its data to be encrypted and as others
> have mentioned can be enabled by the DBA alone.

Frankly I'm having difficulties imagining when it could be a real
problem. It doesn't seem to be such a burden to ask a colleague for
assistance in case you don't have sufficient permissions to do
something. And I got a strong feeling that solving bureaucracy issues of
specific organizations by changing PostgreSQL core in very invasive way
(keeping in mind testing, maintaining, etc) is misguided.

-- 
Best regards,
Aleksander Alekseev


signature.asc
Description: PGP signature


Re: [HACKERS] WIP: Data at rest encryption

2017-06-14 Thread Kenneth Marshall
On Wed, Jun 14, 2017 at 12:04:26PM +0300, Aleksander Alekseev wrote:
> Hi Ants,
> 
> On Tue, Jun 13, 2017 at 09:07:49AM -0400, Peter Eisentraut wrote:
> > On 6/12/17 17:11, Ants Aasma wrote:
> > > I'm curious if the community thinks this is a feature worth having?
> > > Even considering that security experts would classify this kind of
> > > encryption as a checkbox feature.
> > 
> > File system encryption already exists and is well-tested.  I don't see
> > any big advantages in re-implementing all of this one level up.  You
> > would have to touch every single place in PostgreSQL backend and tool
> > code where a file is being read or written.  Yikes.
> 
> I appreciate your work, but unfortunately I must agree with Peter.
> 
> On Linux you can configure the full disc encryption using LUKS /
> dm-crypt in like 5 minutes [1]. On FreeBSD you can do the same using
> geli [2]. In my personal opinion PostgreSQL is already complicated
> enough. A few companies that hired system administrators that are too
> lazy to read two or three man pages is not a reason to re-implement file
> system encryption (or compression, or mirroring if that matters) in any
> open source RDBMS.
> 

Hi Aleksander,

While I agree that configuring full disk encryption is not technically
difficult, it requires much more privileged access to the system and
basically requires the support of a system administrator. In addition,
if a volume is not available for encryption, PostgreSQL support for
encryption would still allow for its data to be encrypted and as others
have mentioned can be enabled by the DBA alone.

Regards,
Ken


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-14 Thread Aleksander Alekseev
Hi Ants,

On Tue, Jun 13, 2017 at 09:07:49AM -0400, Peter Eisentraut wrote:
> On 6/12/17 17:11, Ants Aasma wrote:
> > I'm curious if the community thinks this is a feature worth having?
> > Even considering that security experts would classify this kind of
> > encryption as a checkbox feature.
> 
> File system encryption already exists and is well-tested.  I don't see
> any big advantages in re-implementing all of this one level up.  You
> would have to touch every single place in PostgreSQL backend and tool
> code where a file is being read or written.  Yikes.

I appreciate your work, but unfortunately I must agree with Peter.

On Linux you can configure the full disc encryption using LUKS /
dm-crypt in like 5 minutes [1]. On FreeBSD you can do the same using
geli [2]. In my personal opinion PostgreSQL is already complicated
enough. A few companies that hired system administrators that are too
lazy to read two or three man pages is not a reason to re-implement file
system encryption (or compression, or mirroring if that matters) in any
open source RDBMS.

[1] http://eax.me/dm-crypt/
[2] http://eax.me/freebsd-geli/

-- 
Best regards,
Aleksander Alekseev


signature.asc
Description: PGP signature


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Stephen Frost
Bruce,

* Bruce Momjian (br...@momjian.us) wrote:
> On Tue, Jun 13, 2017 at 03:20:12PM -0400, Stephen Frost wrote:
> > > OK, so let's go back.  You are saying there are no security benefits to
> > > this vs. file system encryption.
> > 
> > I'm not sure that I can see any, myself..  Perhaps I'm wrong there, but
> > it seems unlikely that this would be an improvement over filesystem
> > level encryption in the general sense.  I'm not sure that I see it as
> > really worse than filesystem-level encryption either, to be clear.
> > There's a bit of increased information leakage, as Peter mentioned and I
> > agreed with, but it's not a lot and I expect in most cases that
> > information leak would be acceptable.  That seems like something which
> > would need to be considered on a case-by-case basis.
> 
> Isn't the leakage controlled by OS permissions, so is it really leakage,
> i.e., if you can see the leakage, you probably have bypassed the OS
> permissions and see the key and data anyway.

The case I'm mainly considering is if you somehow lose control over the
medium in which the encrypted database resides- that is, someone steals
the hard drive, or perhaps the hard drive is sold without properly being
wiped first, things like that.

In such a case, there's no OS permissions to bypass because the OS is
now controlled by the attacker.  In that case, if the key wasn't stored
on the hard drive, then the attacker would be able to see the contents
of the filesystem and the associated metadata, but not the contents of
the cluster.

In that case, the distinction between filesystem-level encryption and
PG-level encryption is that with filesystem-level encryption the
attacker wouldn't be able to even see what files exist or any metadata
about them, whereas with PG-level encryption that information would be
available to the attacker.

In terms of an online attack where an attacker has gained access to the
system then, in general, you're right that if the attacker is able to
see into the PG data directory at all then they've figured out a way to
bypass the OS-level permissions and would then be able to see the data
directly anyway.  That's a different scenario which would most likely be
helped by something like SELinux being used, which could prevent the
attacker from being able to look at the PG data directory because the
attacker has connected to the system from a network which isn't allowed
to directly access those files.

> > > The benefit is allowing configuration
> > > in the database rather than the OS?
> > 
> > No, the benefit is that the database administrator can configure it and
> > set it up and not have to get an OS-level administrator involved.  There
> > may also be other reasons why filesystem-level encryption is difficult
> > to set up or use in a certain environment, but this wouldn't depend on
> > anything OS-related and therefore could be done.
> 
> OK, my only point here is that we are going down a slippery slope of
> implementing OS things in the database.  There is nothing wrong with
> that but it has often been something we have avoided, because of the
> added complexity required in the db server.

I'm not sure that I actually agree that encryption is really solely an
OS-level function, or even that encryption at rest is solely the OS's
job.  As a counter-example, I encrypt my SSH keys and GPG keys
routinely, even when I'm using OS-level filesystem encryption.  Perhaps
that's excessive of me, but, well, I don't find it so. :)

> As a counter-example, we only added an external collation library to
> Postgres when we clearly saw a benefit, e.g. detecting collation
> changes.

Right, and there's also the potential for adding more flexibility down
the road, which I'm certainly all for, but I see value in having even
this initial version of the feature too.

> > Of course, either way you'd have to provide for a way to get the key
> > from one system to the other.
> 
> Uh, doesn't scp do this?  I have trouble seeing how avoiding calling
> openssl justifies changes to our database server.

scp may not be an option as it requires network connectivity between the
systems.  This is also just one of the use-cases, and not the main
reason, at least in my view, to add this feature.

> > Also, tools like pg_basebackup could be used, with nearly zero changes,
> > I think, to get an encrypted copy of the database for backup purposes,
> > removing the need to work out a way to handle encrypting backups.
> 
> I do think we need much more documentation on how to encrypt things,
> though that is a separate issue.  It might help to document how you
> _should_ do things now to see the limitations we have.

Improving our documentation would certainly be good, but I'm not sure
that we can really recommend specific ways of doing things like
filesystem-level encryption, as that's really OS-dependent and there
could be trade-offs in different ways a given OS might provide that
capability.  I'm not sure that having our doc

Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Stephen Frost
Peter,

* Peter Eisentraut (peter.eisentr...@2ndquadrant.com) wrote:
> On 6/13/17 15:20, Stephen Frost wrote:
> > And then you would need openssl on the other system to decrypt it.
> 
> Or make the USB file system encrypted as well?  If you're in that kind
> of environment, that would surely be feasible, if not required.

Right, but requiring file system encryption to work on a USB stick
across different types of systems strikes me as actually a higher bar
than requiring openssl to exist on both the source and destination
sides.

Naturally, if the environment you're in has already solved that problem
across the enterprise then it's a good approach, although you might want
to use a different encryption key, perhaps, though hopefully that's
something you'd be able to do pretty easily too.

Thanks!

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Stephen Frost
Peter,

* Peter Eisentraut (peter.eisentr...@2ndquadrant.com) wrote:
> On 6/13/17 15:20, Stephen Frost wrote:
> > No, the benefit is that the database administrator can configure it and
> > set it up and not have to get an OS-level administrator involved.  There
> > may also be other reasons why filesystem-level encryption is difficult
> > to set up or use in a certain environment, but this wouldn't depend on
> > anything OS-related and therefore could be done.
> 
> Let's see a proposal in those terms then.  How easy can you make it,
> compared to existing OS-level solutions, and will that justify the
> maintenance overhead?

From the original post on this thread, which included a WIP patch:

--
Usage
=

Set up database like so:

(read -sp "Postgres passphrase: " PGENCRYPTIONKEY; echo;
 export PGENCRYPTIONKEY
 initdb -k -K pgcrypto $PGDATA )

Start PostgreSQL:

(read -sp "Postgres passphrase: " PGENCRYPTIONKEY; echo;
 export PGENCRYPTIONKEY
 postgres $PGDATA )
--

That certainly seems very straight-forward to me, though I expect that
packagers would probably improve upon it further.

> Considering how ubiquitous file-system encryption is, I have my doubts
> that the trade-offs will come out right, but let's see.

There's definitely environments out there where DBAs aren't able to have
root privileges and that limits what they're able to do.  I'm not really
sure how to objectively weigh "you don't need to be root to encrypt the
database" vs. maintenance overhead of this feature.  Subjectively, for
my 2c anyway, it seems well worth it, but that's naturally subjective.
:)

Thanks!

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread David Fetter
On Tue, Jun 13, 2017 at 10:28:14AM -0400, Peter Eisentraut wrote:
> On 6/13/17 09:24, Stephen Frost wrote:
> > but there are use-cases where it'd be really nice to be able to
> > have PG doing the encryption instead of the filesystem because
> > then you can do things like backup the database, copy it somewhere
> > else directly, and then restore it using the regular PG
> > mechanisms, as long as you have access to the key.  That's not
> > something you can directly do with filesystem-level encryption
> 
> Interesting point.
> 
> I wonder what the proper extent of "encryption at rest" should be.
> If you encrypt just on a file or block level, then someone looking
> at the data directory or a backup can still learn a number of things
> about the number of tables, transaction rates, various configuration
> settings, and so on.

In the end, information leaks at a strictly positive baud rate because
physics (cf. Claude Shannon, et al).

Encryption at rest is one technique whereby people can slow this rate,
but there's no such thing as getting it to zero.  Let's not creep this
feature in the ultimately futile attempt to do so.

> In the scenario of a sensitive application hosted on a shared
> SAN, I don't think that is good enough.
> 
> 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.

Right.

Best,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Bruce Momjian
On Tue, Jun 13, 2017 at 04:08:29PM -0400, Peter Eisentraut wrote:
> On 6/13/17 15:51, Bruce Momjian wrote:
> > Isn't the leakage controlled by OS permissions, so is it really leakage,
> > i.e., if you can see the leakage, you probably have bypassed the OS
> > permissions and see the key and data anyway.
> 
> One scenario (among many) is when you're done with the disk.  If the
> content was fully encrypted, then you can just throw it into the trash
> or have your provider dispose of it or reuse it.  If not, then,
> depending on policy, you will have to physically obtain it and burn it.

Oh, I see your point --- db-level encryption stores the file system as
mountable on the device, while it is not with storage-level encryption
--- got it.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Peter Eisentraut
On 6/13/17 15:51, Bruce Momjian wrote:
> Isn't the leakage controlled by OS permissions, so is it really leakage,
> i.e., if you can see the leakage, you probably have bypassed the OS
> permissions and see the key and data anyway.

One scenario (among many) is when you're done with the disk.  If the
content was fully encrypted, then you can just throw it into the trash
or have your provider dispose of it or reuse it.  If not, then,
depending on policy, you will have to physically obtain it and burn it.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Bruce Momjian
On Tue, Jun 13, 2017 at 03:20:12PM -0400, Stephen Frost wrote:
> Bruce,
> 
> * Bruce Momjian (br...@momjian.us) wrote:
> > On Tue, Jun 13, 2017 at 02:38:58PM -0400, Stephen Frost wrote:
> > > It's good to discuss what the feature would bring and what cases it
> > > doesn't cover, as well as discussing how it can be designed to make sure
> > > that later improvements are able to be done without having to change it
> > > around.  I do think it's a good idea for us to consider taking an
> > > incremental approach where we're adding pieces and building things up as
> > > we go.  I'm concerned that if we try to do too much in the initial
> > > implementation that we'll end up not having anything.
> > > 
> > > As it relates to the different attack vectors that this would address,
> > > it's primairly the same ones which filesystem-level encryption also
> > > addresses, but it's an improvement when it comes to ease of use.
> > > Unfortunately, it won't address cases where the OS is compromised.
> > 
> > OK, so let's go back.  You are saying there are no security benefits to
> > this vs. file system encryption.
> 
> I'm not sure that I can see any, myself..  Perhaps I'm wrong there, but
> it seems unlikely that this would be an improvement over filesystem
> level encryption in the general sense.  I'm not sure that I see it as
> really worse than filesystem-level encryption either, to be clear.
> There's a bit of increased information leakage, as Peter mentioned and I
> agreed with, but it's not a lot and I expect in most cases that
> information leak would be acceptable.  That seems like something which
> would need to be considered on a case-by-case basis.

Isn't the leakage controlled by OS permissions, so is it really leakage,
i.e., if you can see the leakage, you probably have bypassed the OS
permissions and see the key and data anyway.

> > The benefit is allowing configuration
> > in the database rather than the OS?
> 
> No, the benefit is that the database administrator can configure it and
> set it up and not have to get an OS-level administrator involved.  There
> may also be other reasons why filesystem-level encryption is difficult
> to set up or use in a certain environment, but this wouldn't depend on
> anything OS-related and therefore could be done.

OK, my only point here is that we are going down a slippery slope of
implementing OS things in the database.  There is nothing wrong with
that but it has often been something we have avoided, because of the
added complexity required in the db server.

As a counter-example, we only added an external collation library to
Postgres when we clearly saw a benefit, e.g. detecting collation
changes.

> > You stated you can transfer
> > db-level encrypted files between servers, but can't you do that anyway? 
> 
> If the filesystem is encrypted and you wanted to transfer the entire
> cluster from one system to another, keeping it encrypted with the same
> key, you'd have to transfer the entire filesystem at a block level.
> That's not typically very easy to do (ZFS, specifically, has this
> capability where you can export a filesystem and send it from one
> machine to another, but I don't know of any other filesystems which do
> and ZFS isn't always an option..).
>
> You could go through a process of re-encrypting the files prior to
> transferring them, or deciding that simply having the transport
> mechanism encrypted is sufficient (eg: SSH), but if what you really want
> to do is keep the existing encryption of the database and transfer it to
> another system, this allows that pretty easily.
> 
> For example, you could simply do: 
> 
> cp -a /path/to/PG /mnt/usb
> 
> and you're done.  If you're using filesystem level encryption then you'd
> have to re-encrypt the data, using something like:
> 
> tar -cf - /path/to/PG | openssl -key private.key > 
> /mnt/usb/encrypted_cluster.tar
> 
> And then you would need openssl on the other system to decrypt it.
> 
> Of course, either way you'd have to provide for a way to get the key
> from one system to the other.

Uh, doesn't scp do this?  I have trouble seeing how avoiding calling
openssl justifies changes to our database server.

> Also, tools like pg_basebackup could be used, with nearly zero changes,
> I think, to get an encrypted copy of the database for backup purposes,
> removing the need to work out a way to handle encrypting backups.

I do think we need much more documentation on how to encrypt things,
though that is a separate issue.  It might help to document how you
_should_ do things now to see the limitations we have.

> > Is the problem that you have to encrypt before sending and decrypt on
> > arrival, if you don't trust the transmission link?  Is that used a lot? 
> > Is having the db encrypt every write a reasonable solution to that?
> 
> There's multiple use-cases here.  Making it easier to copy the database
> is just one of them and it isn't the biggest one.  The biggest benefit
> is that there's case

Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Peter Eisentraut
On 6/13/17 15:20, Stephen Frost wrote:
> For example, you could simply do: 
> 
> cp -a /path/to/PG /mnt/usb
> 
> and you're done.  If you're using filesystem level encryption then you'd
> have to re-encrypt the data, using something like:
> 
> tar -cf - /path/to/PG | openssl -key private.key > 
> /mnt/usb/encrypted_cluster.tar
> 
> And then you would need openssl on the other system to decrypt it.

Or make the USB file system encrypted as well?  If you're in that kind
of environment, that would surely be feasible, if not required.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Peter Eisentraut
On 6/13/17 15:20, Stephen Frost wrote:
> No, the benefit is that the database administrator can configure it and
> set it up and not have to get an OS-level administrator involved.  There
> may also be other reasons why filesystem-level encryption is difficult
> to set up or use in a certain environment, but this wouldn't depend on
> anything OS-related and therefore could be done.

Let's see a proposal in those terms then.  How easy can you make it,
compared to existing OS-level solutions, and will that justify the
maintenance overhead?

Considering how ubiquitous file-system encryption is, I have my doubts
that the trade-offs will come out right, but let's see.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Stephen Frost
Bruce,

* Bruce Momjian (br...@momjian.us) wrote:
> On Tue, Jun 13, 2017 at 02:38:58PM -0400, Stephen Frost wrote:
> > It's good to discuss what the feature would bring and what cases it
> > doesn't cover, as well as discussing how it can be designed to make sure
> > that later improvements are able to be done without having to change it
> > around.  I do think it's a good idea for us to consider taking an
> > incremental approach where we're adding pieces and building things up as
> > we go.  I'm concerned that if we try to do too much in the initial
> > implementation that we'll end up not having anything.
> > 
> > As it relates to the different attack vectors that this would address,
> > it's primairly the same ones which filesystem-level encryption also
> > addresses, but it's an improvement when it comes to ease of use.
> > Unfortunately, it won't address cases where the OS is compromised.
> 
> OK, so let's go back.  You are saying there are no security benefits to
> this vs. file system encryption.

I'm not sure that I can see any, myself..  Perhaps I'm wrong there, but
it seems unlikely that this would be an improvement over filesystem
level encryption in the general sense.  I'm not sure that I see it as
really worse than filesystem-level encryption either, to be clear.
There's a bit of increased information leakage, as Peter mentioned and I
agreed with, but it's not a lot and I expect in most cases that
information leak would be acceptable.  That seems like something which
would need to be considered on a case-by-case basis.

> The benefit is allowing configuration
> in the database rather than the OS?

No, the benefit is that the database administrator can configure it and
set it up and not have to get an OS-level administrator involved.  There
may also be other reasons why filesystem-level encryption is difficult
to set up or use in a certain environment, but this wouldn't depend on
anything OS-related and therefore could be done.

> You stated you can transfer
> db-level encrypted files between servers, but can't you do that anyway? 

If the filesystem is encrypted and you wanted to transfer the entire
cluster from one system to another, keeping it encrypted with the same
key, you'd have to transfer the entire filesystem at a block level.
That's not typically very easy to do (ZFS, specifically, has this
capability where you can export a filesystem and send it from one
machine to another, but I don't know of any other filesystems which do
and ZFS isn't always an option..).

You could go through a process of re-encrypting the files prior to
transferring them, or deciding that simply having the transport
mechanism encrypted is sufficient (eg: SSH), but if what you really want
to do is keep the existing encryption of the database and transfer it to
another system, this allows that pretty easily.

For example, you could simply do: 

cp -a /path/to/PG /mnt/usb

and you're done.  If you're using filesystem level encryption then you'd
have to re-encrypt the data, using something like:

tar -cf - /path/to/PG | openssl -key private.key > 
/mnt/usb/encrypted_cluster.tar

And then you would need openssl on the other system to decrypt it.

Of course, either way you'd have to provide for a way to get the key
from one system to the other.

Also, tools like pg_basebackup could be used, with nearly zero changes,
I think, to get an encrypted copy of the database for backup purposes,
removing the need to work out a way to handle encrypting backups.

> Is the problem that you have to encrypt before sending and decrypt on
> arrival, if you don't trust the transmission link?  Is that used a lot? 
> Is having the db encrypt every write a reasonable solution to that?

There's multiple use-cases here.  Making it easier to copy the database
is just one of them and it isn't the biggest one.  The biggest benefit
is that there's cases where filesystem-level encryption isn't really an
option or, even if it is, it's not desirable for other reasons.

> As far as future features, we don't have to add the all features at this
> time, but if someone has a good idea for an API and we can make it work
> easily while adding this feature, why wouldn't we do that?

Sure, I'm all for specific suggestions about how to improve the API, or
just in general recommendations of how to improve the patch.  The
suggestions which have been made about key management don't really come
across to me as specific API-level recommendations but rather "this
would also be nice to have" kind of comments, which isn't really the
same.

Thanks!

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Bruce Momjian
On Tue, Jun 13, 2017 at 02:38:58PM -0400, Stephen Frost wrote:
> It's good to discuss what the feature would bring and what cases it
> doesn't cover, as well as discussing how it can be designed to make sure
> that later improvements are able to be done without having to change it
> around.  I do think it's a good idea for us to consider taking an
> incremental approach where we're adding pieces and building things up as
> we go.  I'm concerned that if we try to do too much in the initial
> implementation that we'll end up not having anything.
> 
> As it relates to the different attack vectors that this would address,
> it's primairly the same ones which filesystem-level encryption also
> addresses, but it's an improvement when it comes to ease of use.
> Unfortunately, it won't address cases where the OS is compromised.

OK, so let's go back.  You are saying there are no security benefits to
this vs. file system encryption.  The benefit is allowing configuration
in the database rather than the OS?  You stated you can transfer
db-level encrypted files between servers, but can't you do that anyway? 
Is the problem that you have to encrypt before sending and decrypt on
arrival, if you don't trust the transmission link?  Is that used a lot? 
Is having the db encrypt every write a reasonable solution to that?

As far as future features, we don't have to add the all features at this
time, but if someone has a good idea for an API and we can make it work
easily while adding this feature, why wouldn't we do that?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Stephen Frost
Bruce,

* Bruce Momjian (br...@momjian.us) wrote:
> On Tue, Jun 13, 2017 at 02:23:39PM -0400, Stephen Frost wrote:
> > I'm not trying to shut down discussion, I'm simply pointing out where
> > this feature will be helpful and where it won't be.  If there's a way to
> > make it better and able to address an attack where the OS permission
> > system is bypassed, that'd be great, but I certainly don't know of any
> > way to do that and we don't want to claim that this feature will protect
> > against an attack vector that it won't.
> > 
> > If the lack of that means you don't support the feature, that's
> > unfortunate as it seems to imply, to me at least, that we'll never have
> > any kind of encryption because there's no way for it to prevent attacks
> > where the OS permission system is able to be bypassed.
> 
> It means if we can't discuss the actual benefits that this feature
> brings, and doesn't bring, and how it will deal with future feature
> additions, then you are right we will never have it.

I apologize for having come across as trying to shut down discussion,
that was not my intent.

It's good to discuss what the feature would bring and what cases it
doesn't cover, as well as discussing how it can be designed to make sure
that later improvements are able to be done without having to change it
around.  I do think it's a good idea for us to consider taking an
incremental approach where we're adding pieces and building things up as
we go.  I'm concerned that if we try to do too much in the initial
implementation that we'll end up not having anything.

As it relates to the different attack vectors that this would address,
it's primairly the same ones which filesystem-level encryption also
addresses, but it's an improvement when it comes to ease of use.
Unfortunately, it won't address cases where the OS is compromised.

Thanks!

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Bruce Momjian
On Tue, Jun 13, 2017 at 02:23:39PM -0400, Stephen Frost wrote:
> I'm not trying to shut down discussion, I'm simply pointing out where
> this feature will be helpful and where it won't be.  If there's a way to
> make it better and able to address an attack where the OS permission
> system is bypassed, that'd be great, but I certainly don't know of any
> way to do that and we don't want to claim that this feature will protect
> against an attack vector that it won't.
> 
> If the lack of that means you don't support the feature, that's
> unfortunate as it seems to imply, to me at least, that we'll never have
> any kind of encryption because there's no way for it to prevent attacks
> where the OS permission system is able to be bypassed.

It means if we can't discuss the actual benefits that this feature
brings, and doesn't bring, and how it will deal with future feature
additions, then you are right we will never have it.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Stephen Frost
Bruce,

* Bruce Momjian (br...@momjian.us) wrote:
> On Tue, Jun 13, 2017 at 01:25:00PM -0400, Stephen Frost wrote:
> > > I think the big win of Postgres doing the encryption is that the
> > > user-visible file system is no longer a target (assuming OS permissions
> > > are bypassed), while for file system encryption it is the storage device
> > > that is encrypted.
> > 
> > If OS permissions are bypassed then the encryption isn't going to help
> > because the attacker can just access shared memory.
> > 
> > The big wins for doing the encryption in PostgreSQL are, as Robert and I
> > have both mentioned on this thread already, that it provides
> > data-at-rest encryption in an easier to deploy fashion which will work
> > the same across different systems and allows the encrypted cluster to be
> > transferred more easily between systems.  There are almsot certainly
> > other wins from having PG do the encryption, but the above strikes me as
> > the big ones, and those are certainly valuable enough on their own for
> > us to seriously consider adding this capability.
> 
> Since you seem to be trying to shut down discussion, I will simply say I
> am unimpressed that this use-case is sufficient justification to add the
> feature.

I'm not trying to shut down discussion, I'm simply pointing out where
this feature will be helpful and where it won't be.  If there's a way to
make it better and able to address an attack where the OS permission
system is bypassed, that'd be great, but I certainly don't know of any
way to do that and we don't want to claim that this feature will protect
against an attack vector that it won't.

If the lack of that means you don't support the feature, that's
unfortunate as it seems to imply, to me at least, that we'll never have
any kind of encryption because there's no way for it to prevent attacks
where the OS permission system is able to be bypassed.

Thanks!

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Stephen Frost
Bruce,

* Bruce Momjian (br...@momjian.us) wrote:
> On Tue, Jun 13, 2017 at 01:44:51PM -0400, Stephen Frost wrote:
> > Just to be clear, I don't have any issue with discussing the idea that
> > we want to get to a point where we can work with multiple keys and
> > encrypt different tables with different keys (or not encrypt certain
> > tables, et al) with the goal of implementing the single-key approach in
> > a way that allows us to expand on it down the road easily, I just don't
> > think we need to have it all done in the very first patch which adds the
> > ability to encrypt the data files.  Maybe you're not saying that it has
> > to be included in the first implementation, in which case we seem to
> > just be talking past each other, but that isn't the impression I got..
> 
> We don't want to implement all-cluster encryption with a simple user API
> and then realize we need another API for later encryption features, do
> we?

I actually suspect that's exactly where we'll end up- but due to
necessity rather than because there's a way to avoid it.  We are going
to want to encrypt cluster-wide components of the system (shared
catalogs, WAL, etc) and that means that we have to have a key provided
very early on.  That's a very different thing from being able to do
something like encrypt specific tables, tablespaces, etc, where the key
can be provided much later and we'll want to allow users to use SQL or
the libpq protocol to be able to specify what to encrypt and possibly
even provide the encryption keys.

That said, the approach outlined here could be used for both by
expanding on the command string, perhaps passing it a keyid which is
what we store in the catalog to indicate what key a table is encrypted
with and then the keyid is "%k" in the command string and the command
has to return the specified key for us to decrypt the table.  That would
involve adding a new catalog table to identify keys and their keyids,
I'd think, and an additional column in pg_class which specifies the key
(or perhaps we'd just have a new catalog table that says what tables are
encrypted in what way).

Thanks!

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Bruce Momjian
On Tue, Jun 13, 2017 at 01:44:51PM -0400, Stephen Frost wrote:
> Just to be clear, I don't have any issue with discussing the idea that
> we want to get to a point where we can work with multiple keys and
> encrypt different tables with different keys (or not encrypt certain
> tables, et al) with the goal of implementing the single-key approach in
> a way that allows us to expand on it down the road easily, I just don't
> think we need to have it all done in the very first patch which adds the
> ability to encrypt the data files.  Maybe you're not saying that it has
> to be included in the first implementation, in which case we seem to
> just be talking past each other, but that isn't the impression I got..

We don't want to implement all-cluster encryption with a simple user API
and then realize we need another API for later encryption features, do
we?  And we are not going to know that if we don't talk about it, but
hey, this is just an email thread and I can marshal opposition to the
feature later when it appears, and point this all out again.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Stephen Frost
Joe,

* Joe Conway (m...@joeconway.com) wrote:
> On 06/13/2017 10:20 AM, Stephen Frost wrote:
> > * Joe Conway (m...@joeconway.com) wrote:
> >> Except shell escaping issues, etc, etc
> > 
> > That's not an issue- we're talking about reading the stdout of some
> > other process, there's no shell escaping that has to be done there.
> 
> It could be an issue depending on how the user stores their master key.

... eh?  The user gives us a command to run, we run it, it spits out
some binary blob to stdout which we read in and use as the key.  I don't
see where in that there's any need to be concerned about shell escaping
issues.

> > I disagree that proper key management is "simple".  If we really get to
> > a point where we think we have a simple answer to it then perhaps that
> > can be implemented in addition to the encryption piece in the same
> > release cycle- but they certainly don't need to be in the same patch,
> > nor do we need to make good key management a requirement for adding
> > encryption support.
> 
> I never said key management was simple. Indeed it is the most complex
> and hazardous part of all this as you said earlier. What is simple is
> implementing a master key encrypting actual keys scheme. Keeping the
> user's master key management out of this design is unchanged by what I
> proposed, and what I proposed is a superior yet simple method. Yes, it
> can be done separately but what is the point? We should at least discuss
> it as part of the design.

The point is that we haven't got any encryption of any kind and you're
suggesting we introduce key management which you agree isn't simple.
That you're trying to argue that it actually is simple because it's just
<> is a bit bizarre to me.

> > No, but it seriously changes the level of complexity.  I feel like we're
> > trying to go from zero to light speed here because there's an idea that
> > it's "simple" to add X, Y or Z additional requirement beyond the basic
> > feature, but we don't have anything yet.
> 
> I think that is hyperbole. It does not significantly add to the
> complexity of what is being discussed.

If I stipulate that it's, indeed, simple to implement a system where we
have a master key and other keys- where are those other keys going to
kept (even if they're encrypted)?  How many extra keys are we talking
about?  When are those keys going to be used and how do we know what key
to use when?  If we're going to do per-tablespace or per-table
encryption, how are we going to handle the WAL for that?  Will we have
an independent key for WAL (in which case, what's the point of using
different keys for tables, et al, when all the data is in the WAL?)?

Having a single key which is used cluster-wide is extremely simple and
lets us get some form of encryption first before we try to tackle the
more complicated multi-key/partial-encryption system.

Just to be clear, I don't have any issue with discussing the idea that
we want to get to a point where we can work with multiple keys and
encrypt different tables with different keys (or not encrypt certain
tables, et al) with the goal of implementing the single-key approach in
a way that allows us to expand on it down the road easily, I just don't
think we need to have it all done in the very first patch which adds the
ability to encrypt the data files.  Maybe you're not saying that it has
to be included in the first implementation, in which case we seem to
just be talking past each other, but that isn't the impression I got..

Thanks!

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Bruce Momjian
On Tue, Jun 13, 2017 at 01:25:00PM -0400, Stephen Frost wrote:
> > I think the big win of Postgres doing the encryption is that the
> > user-visible file system is no longer a target (assuming OS permissions
> > are bypassed), while for file system encryption it is the storage device
> > that is encrypted.
> 
> If OS permissions are bypassed then the encryption isn't going to help
> because the attacker can just access shared memory.
> 
> The big wins for doing the encryption in PostgreSQL are, as Robert and I
> have both mentioned on this thread already, that it provides
> data-at-rest encryption in an easier to deploy fashion which will work
> the same across different systems and allows the encrypted cluster to be
> transferred more easily between systems.  There are almsot certainly
> other wins from having PG do the encryption, but the above strikes me as
> the big ones, and those are certainly valuable enough on their own for
> us to seriously consider adding this capability.

Since you seem to be trying to shut down discussion, I will simply say I
am unimpressed that this use-case is sufficient justification to add the
feature.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Joe Conway
On 06/13/2017 10:20 AM, Stephen Frost wrote:
> * Joe Conway (m...@joeconway.com) wrote:
>> Except shell escaping issues, etc, etc
> 
> That's not an issue- we're talking about reading the stdout of some
> other process, there's no shell escaping that has to be done there.

It could be an issue depending on how the user stores their master key.

> I disagree that proper key management is "simple".  If we really get to
> a point where we think we have a simple answer to it then perhaps that
> can be implemented in addition to the encryption piece in the same
> release cycle- but they certainly don't need to be in the same patch,
> nor do we need to make good key management a requirement for adding
> encryption support.

I never said key management was simple. Indeed it is the most complex
and hazardous part of all this as you said earlier. What is simple is
implementing a master key encrypting actual keys scheme. Keeping the
user's master key management out of this design is unchanged by what I
proposed, and what I proposed is a superior yet simple method. Yes, it
can be done separately but what is the point? We should at least discuss
it as part of the design.

> No, but it seriously changes the level of complexity.  I feel like we're
> trying to go from zero to light speed here because there's an idea that
> it's "simple" to add X, Y or Z additional requirement beyond the basic
> feature, but we don't have anything yet.

I think that is hyperbole. It does not significantly add to the
complexity of what is being discussed.

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Stephen Frost
Bruce,

* Bruce Momjian (br...@momjian.us) wrote:
> On Tue, Jun 13, 2017 at 01:01:32PM -0400, Stephen Frost wrote:
> > > Well, usually the symetric key is stored using RSA and a symetric
> > > cipher is used to encrypt/decrypt the data.  I was thinking of a case
> > > where you encrypt a row using a symetric key, then store RSA-encrypted
> > > versions of the symetric key encrypted that only specific users could
> > > decrypt and get the key to decrypt the data.
> > 
> > This goes back to key management and I agree that it often makes sense
> > to use RSA or similar to encrypt the symmetric key, and this approach
> > would allow the user to do so.  That doesn't actually give you a
> > "write-only" encryption option though, since any user who can decrypt
> > the symmetric key is able to use the symmetric key for both encryption
> > and decryption, and someone who only has access to the RSA encryption
> > key can't actually encrypt the data since they can't access the
> > symmetric key.
>  
> I think the big win of Postgres doing the encryption is that the
> user-visible file system is no longer a target (assuming OS permissions
> are bypassed), while for file system encryption it is the storage device
> that is encrypted.

If OS permissions are bypassed then the encryption isn't going to help
because the attacker can just access shared memory.

The big wins for doing the encryption in PostgreSQL are, as Robert and I
have both mentioned on this thread already, that it provides
data-at-rest encryption in an easier to deploy fashion which will work
the same across different systems and allows the encrypted cluster to be
transferred more easily between systems.  There are almsot certainly
other wins from having PG do the encryption, but the above strikes me as
the big ones, and those are certainly valuable enough on their own for
us to seriously consider adding this capability.

> My big question is how many times are the OS permissions bypassed in a
> way that would also not expose the db clusters key or db data?

This is not the attack vector that this solution is attempting to
address, so there really isn't much point in discussing it on this
thread.

Thanks!

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Stephen Frost
Joe,

* Joe Conway (m...@joeconway.com) wrote:
> Except shell escaping issues, etc, etc

That's not an issue- we're talking about reading the stdout of some
other process, there's no shell escaping that has to be done there.

> > Let us, please, stop stressing over the right way to do key management
> > as part of this discussion about providing encryption.  The two are
> > different things and we do not need to solve both at once.
> 
> Not stressing, but this is an important part of the design and should be
> done correctly. It is also very simple, so should not be hard to add.

I disagree that proper key management is "simple".  If we really get to
a point where we think we have a simple answer to it then perhaps that
can be implemented in addition to the encryption piece in the same
release cycle- but they certainly don't need to be in the same patch,
nor do we need to make good key management a requirement for adding
encryption support.

> > Further, yes, we will definitely want to get to a point where we can
> > encrypt subsets of the system in different ways, but that doesn't have
> > to be done in the first implementation either.
> 
> No, it doesn't, but that doesn't change the utility of doing it this way
> from the start.

No, but it seriously changes the level of complexity.  I feel like we're
trying to go from zero to light speed here because there's an idea that
it's "simple" to add X, Y or Z additional requirement beyond the basic
feature, but we don't have anything yet.  I continue to be of the
feeling that we should start simple and keep it to the basic feature
first and make sure that we can actually get that right before we start
looking into adding on additional bits.

Thanks!

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Bruce Momjian
On Tue, Jun 13, 2017 at 01:01:32PM -0400, Stephen Frost wrote:
> > Well, usually the symetric key is stored using RSA and a symetric
> > cipher is used to encrypt/decrypt the data.  I was thinking of a case
> > where you encrypt a row using a symetric key, then store RSA-encrypted
> > versions of the symetric key encrypted that only specific users could
> > decrypt and get the key to decrypt the data.
> 
> This goes back to key management and I agree that it often makes sense
> to use RSA or similar to encrypt the symmetric key, and this approach
> would allow the user to do so.  That doesn't actually give you a
> "write-only" encryption option though, since any user who can decrypt
> the symmetric key is able to use the symmetric key for both encryption
> and decryption, and someone who only has access to the RSA encryption
> key can't actually encrypt the data since they can't access the
> symmetric key.
 
I think the big win of Postgres doing the encryption is that the
user-visible file system is no longer a target (assuming OS permissions
are bypassed), while for file system encryption it is the storage device
that is encrypted.

My big question is how many times are the OS permissions bypassed in a
way that would also not expose the db clusters key or db data?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Joe Conway
On 06/13/2017 10:05 AM, Stephen Frost wrote:
> Bruce, Joe,
> 
> * Bruce Momjian (br...@momjian.us) wrote:
>> On Tue, Jun 13, 2017 at 09:55:10AM -0700, Joe Conway wrote:
>> > > That way, if the user wants to store the key in an unencrypted text
>> > > file, they can set the encryption_key_command = 'cat /not/very/secure'
>> > > and call it a day.  If they want to prompt the user on the console or
>> > > request the key from an HSM or get it in any other way, they just have
>> > > to write the appropriate shell script.  We just provide mechanism, not
>> > > policy, and the user can adopt any policy they like, from an extremely
>> > > insecure policy to one suitable for Fort Knox.
>> > 
>> > Agreed, but as Bruce alluded to, we want this to be a master key, which
>> > is in turn used to encrypt the actual key, or keys, that are used to
>> > encrypt the data. The actual data encryption keys could be very long
>> > randomly generated binary, and there could be more than one of them
>> > (e.g. one per tablespace) in a file which is encrypted with the master
>> > key. This is more secure and allows, for example the master key to be
>> > changed without having to decrypt/re-encrypt the entire database.
>> 
>> Yes, thank you.  Also, you can make multiple RSA-encrypted copies of the
>> symetric key, one for each role you want to view the data.  And good
>> point on the ability to change the RSA key/password without having to
>> reencrypt the data.
> 
> There's nothing in this proposal that prevents the user from using a
> very long randomly generated binary key.  We aren't talking about
> prompting the user for a password unless that's what they decide the
> shell script should do, unless the user decides to do that and if they
> do then that's their choice.

Except shell escaping issues, etc, etc

> Let us, please, stop stressing over the right way to do key management
> as part of this discussion about providing encryption.  The two are
> different things and we do not need to solve both at once.

Not stressing, but this is an important part of the design and should be
done correctly. It is also very simple, so should not be hard to add.

> Further, yes, we will definitely want to get to a point where we can
> encrypt subsets of the system in different ways, but that doesn't have
> to be done in the first implementation either.

No, it doesn't, but that doesn't change the utility of doing it this way
from the start.

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Stephen Frost
Bruce, Joe,

* Bruce Momjian (br...@momjian.us) wrote:
> On Tue, Jun 13, 2017 at 09:55:10AM -0700, Joe Conway wrote:
> > > That way, if the user wants to store the key in an unencrypted text
> > > file, they can set the encryption_key_command = 'cat /not/very/secure'
> > > and call it a day.  If they want to prompt the user on the console or
> > > request the key from an HSM or get it in any other way, they just have
> > > to write the appropriate shell script.  We just provide mechanism, not
> > > policy, and the user can adopt any policy they like, from an extremely
> > > insecure policy to one suitable for Fort Knox.
> > 
> > Agreed, but as Bruce alluded to, we want this to be a master key, which
> > is in turn used to encrypt the actual key, or keys, that are used to
> > encrypt the data. The actual data encryption keys could be very long
> > randomly generated binary, and there could be more than one of them
> > (e.g. one per tablespace) in a file which is encrypted with the master
> > key. This is more secure and allows, for example the master key to be
> > changed without having to decrypt/re-encrypt the entire database.
> 
> Yes, thank you.  Also, you can make multiple RSA-encrypted copies of the
> symetric key, one for each role you want to view the data.  And good
> point on the ability to change the RSA key/password without having to
> reencrypt the data.

There's nothing in this proposal that prevents the user from using a
very long randomly generated binary key.  We aren't talking about
prompting the user for a password unless that's what they decide the
shell script should do, unless the user decides to do that and if they
do then that's their choice.

Let us, please, stop stressing over the right way to do key management
as part of this discussion about providing encryption.  The two are
different things and we do not need to solve both at once.

Further, yes, we will definitely want to get to a point where we can
encrypt subsets of the system in different ways, but that doesn't have
to be done in the first implementation either.

Thanks!

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Stephen Frost
Bruce,

* Bruce Momjian (br...@momjian.us) wrote:
> On Tue, Jun 13, 2017 at 12:23:01PM -0400, Stephen Frost wrote:
> > > 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.
> 
> My point is that if you have the key accessible to the database server,
> both the database server and OS have access to it.  If you store it in
> the OS, only the OS has access to it.

I understand that, but that's not a particularly interesting distinction
as the database server must, necessairly, have access to the data in the
database.

> > > 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.
> 
> By adding all-cluster encryption, we are re-implementing something the
> OS does just fine, in most cases.  We are going to have API overhead to
> do it in the database, and historically we have avoided that.

We do try to avoid the overhead of additional function calls, in places
where it matters.  As this is all about reading and writing data, the
overhead for the additional check to see if we're doing encryption or
not is unlikely to be interesting.

> > > 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.
> 
> Well, usually the symetric key is stored using RSA and a symetric
> cipher is used to encrypt/decrypt the data.  I was thinking of a case
> where you encrypt a row using a symetric key, then store RSA-encrypted
> versions of the symetric key encrypted that only specific users could
> decrypt and get the key to decrypt the data.

This goes back to key management and I agree that it often makes sense
to use RSA or similar to encrypt the symmetric key, and this approach
would allow the user to do so.  That doesn't actually give you a
"write-only" encryption option though, since any user who can decrypt
the symmetric key is able to use the symmetric key for both encryption
and decryption, and someone who only has access to the RSA encryption
key can't actually encrypt the data since they can't access the
symmetric key.

Thanks!

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Bruce Momjian
On Tue, Jun 13, 2017 at 09:55:10AM -0700, Joe Conway wrote:
> > That way, if the user wants to store the key in an unencrypted text
> > file, they can set the encryption_key_command = 'cat /not/very/secure'
> > and call it a day.  If they want to prompt the user on the console or
> > request the key from an HSM or get it in any other way, they just have
> > to write the appropriate shell script.  We just provide mechanism, not
> > policy, and the user can adopt any policy they like, from an extremely
> > insecure policy to one suitable for Fort Knox.
> 
> Agreed, but as Bruce alluded to, we want this to be a master key, which
> is in turn used to encrypt the actual key, or keys, that are used to
> encrypt the data. The actual data encryption keys could be very long
> randomly generated binary, and there could be more than one of them
> (e.g. one per tablespace) in a file which is encrypted with the master
> key. This is more secure and allows, for example the master key to be
> changed without having to decrypt/re-encrypt the entire database.

Yes, thank you.  Also, you can make multiple RSA-encrypted copies of the
symetric key, one for each role you want to view the data.  And good
point on the ability to change the RSA key/password without having to
reencrypt the data.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Joe Conway
On 06/13/2017 09:28 AM, Robert Haas wrote:
> On Tue, Jun 13, 2017 at 12:23 PM, Stephen Frost  wrote:
>> 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).
> 
> Yes.  I think the right way to implement this is something like:
> 
> 1. Have a GUC that runs a shell command to get the key.
> 
> 2. If the command successfully gets the key, it prints it to stdout
> and returns 0.
> 
> 3. If it doesn't get successfully get the key, it returns 1.  The
> database can retry or give up, whatever we decide to do.
> 
> That way, if the user wants to store the key in an unencrypted text
> file, they can set the encryption_key_command = 'cat /not/very/secure'
> and call it a day.  If they want to prompt the user on the console or
> request the key from an HSM or get it in any other way, they just have
> to write the appropriate shell script.  We just provide mechanism, not
> policy, and the user can adopt any policy they like, from an extremely
> insecure policy to one suitable for Fort Knox.

Agreed, but as Bruce alluded to, we want this to be a master key, which
is in turn used to encrypt the actual key, or keys, that are used to
encrypt the data. The actual data encryption keys could be very long
randomly generated binary, and there could be more than one of them
(e.g. one per tablespace) in a file which is encrypted with the master
key. This is more secure and allows, for example the master key to be
changed without having to decrypt/re-encrypt the entire database.

Joe


-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Bruce Momjian
On Tue, Jun 13, 2017 at 12:23:01PM -0400, Stephen Frost wrote:
> > 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.

Yes, I was just simplifying.

> > 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.

My point is that if you have the key accessible to the database server,
both the database server and OS have access to it.  If you store it in
the OS, only the OS has access to it.

> > 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.

By adding all-cluster encryption, we are re-implementing something the
OS does just fine, in most cases.  We are going to have API overhead to
do it in the database, and historically we have avoided that.

> > 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.

Well, usually the symetric key is stored using RSA and a symetric
cipher is used to encrypt/decrypt the data.  I was thinking of a case
where you encrypt a row using a symetric key, then store RSA-encrypted
versions of the symetric key encrypted that only specific users could
decrypt and get the key to decrypt the data.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Robert Haas
On Tue, Jun 13, 2017 at 12:23 PM, Stephen Frost  wrote:
> 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).

Yes.  I think the right way to implement this is something like:

1. Have a GUC that runs a shell command to get the key.

2. If the command successfully gets the key, it prints it to stdout
and returns 0.

3. If it doesn't get successfully get the key, it returns 1.  The
database can retry or give up, whatever we decide to do.

That way, if the user wants to store the key in an unencrypted text
file, they can set the encryption_key_command = 'cat /not/very/secure'
and call it a day.  If they want to prompt the user on the console or
request the key from an HSM or get it in any other way, they just have
to write the appropriate shell script.  We just provide mechanism, not
policy, and the user can adopt any policy they like, from an extremely
insecure policy to one suitable for Fort Knox.

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Stephen Frost
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


signature.asc
Description: Digital signature


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Bruce Momjian
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.  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.

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.

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 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.

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.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Bruce Momjian
On Tue, Jun 13, 2017 at 11:35:03AM -0400, Robert Haas wrote:
> I anticipate that one of the trickier problems here will be handling
> encryption of the write-ahead log.  Suppose you encrypt WAL a block at
> a time.  In the current system, once you've written and flushed a
> block, you can consider it durably committed, but if that block is
> encrypted, this is no longer true.  A crash might tear the block,
> making it impossible to decrypt.  Replay will therefore stop at the
> end of the previous block, not at the last record actually flushed as
> would happen today.  So, your synchronous_commit suddenly isn't.  A
> similar problem will occur any other page where we choose not to
> protect against torn pages using full page writes.  For instance,
> unless checksums are enabled or wal_log_hints=on, we'll write a data
> page where a single bit has been flipped and assume that the bit will
> either make it to disk or not; the page can't really be torn in any
> way that hurts us.  But with encryption that's no longer true, because
> the hint bit will turn into much more than a single bit flip, and
> rereading that page with half old and half new contents will be the
> end of the world (TM).  I don't know off-hand whether we're
> protecting, say, CLOG page writes with FPWs.: because setting a couple
> of bits is idempotent and doesn't depend on the existing page
> contents, we might not need it currently, but with encryption, every
> bit in the page depends on every other bit in the page, so we
> certainly would.  I don't know how many places we've got assumptions
> like this baked into the system, but I'm guessing there are a bunch.

That is not necessary true.  You are describing a cipher mode where the
user data goes through the cipher, e.g. AES in CBC mode.  However, if
you are using a stream cipher based on a block cipher, e.g. CTR, GCM,
you XOR the user data with a random bit stream, and in that case one bit
change in user data would be one bit change in the cipher output.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Robert Haas
On Mon, Jun 12, 2017 at 5:11 PM, Ants Aasma  wrote:
> Fundamentally there doesn't seem to be a big benefit of implementing
> the encryption at PostgreSQL level instead of the filesystem. The
> patch doesn't take any real advantage from the higher level knowledge
> of the system, nor do I see much possibility for it to do that. The
> main benefit for us is that it's much easier to get a PostgreSQL based
> solution deployed.

I agree with all of that, but ease of deployment has some value unto
itself.  I think pretty much every modern operating system has some
way of encrypting a filesystem, but it's different on Linux vs.
Windows vs. macOS vs. BSD, and you probably need to be the system
administrator on any of those systems in order to set it up.
Something built into PostgreSQL could run without administrator
privileges and work the same way on every platform we support.  That
would be useful.

Of course, what would be even more useful is fine-grained encryption -
encrypt these tables (and the corresponding indexes, toast tables, and
WAL records related to any of that) with this key, encrypt these other
tables (and the same list of associated stuff) with this other key,
and leave the rest unencrypted.  The problem with that is that you
probably can't run recovery without all of the keys, and even on a
clean startup there would be a good deal of engineering work involved
in refusing access to tables whose key hadn't been provided yet.  I
don't think we should wait to have this feature until all of those
problems are solved.  In my opinion, something coarse-grained that
just encrypts the whole cluster would be a pretty useful place to
start and would meet the needs of enough people to be worthwhile all
on its own.  Performance is likely to be poor on large databases,
because every time a page transits between shared_buffers and the
buffer cache we've got to en/decrypt, but as long as it's only poor
for the people who opt into the feature I don't see a big problem with
that.

I anticipate that one of the trickier problems here will be handling
encryption of the write-ahead log.  Suppose you encrypt WAL a block at
a time.  In the current system, once you've written and flushed a
block, you can consider it durably committed, but if that block is
encrypted, this is no longer true.  A crash might tear the block,
making it impossible to decrypt.  Replay will therefore stop at the
end of the previous block, not at the last record actually flushed as
would happen today.  So, your synchronous_commit suddenly isn't.  A
similar problem will occur any other page where we choose not to
protect against torn pages using full page writes.  For instance,
unless checksums are enabled or wal_log_hints=on, we'll write a data
page where a single bit has been flipped and assume that the bit will
either make it to disk or not; the page can't really be torn in any
way that hurts us.  But with encryption that's no longer true, because
the hint bit will turn into much more than a single bit flip, and
rereading that page with half old and half new contents will be the
end of the world (TM).  I don't know off-hand whether we're
protecting, say, CLOG page writes with FPWs.: because setting a couple
of bits is idempotent and doesn't depend on the existing page
contents, we might not need it currently, but with encryption, every
bit in the page depends on every other bit in the page, so we
certainly would.  I don't know how many places we've got assumptions
like this baked into the system, but I'm guessing there are a bunch.

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Stephen Frost
Peter,

* Peter Eisentraut (peter.eisentr...@2ndquadrant.com) wrote:
> I wonder what the proper extent of "encryption at rest" should be.  If
> you encrypt just on a file or block level, then someone looking at the
> data directory or a backup can still learn a number of things about the
> number of tables, transaction rates, various configuration settings, and
> so on.  In the scenario of a sensitive application hosted on a shared
> SAN, I don't think that is good enough.

If someone has access to the SAN, it'd be very difficult to avoid
revealing some information about transaction rates or I/O throughput.
Being able to have the configuration files encrypted would be good
(thinking particularly about pg_hba.conf/pg_ident.conf) but I don't
know that it's strictly necessary or that it would have to be done in an
initial version.

Certainly, there is a trade-off here when it comes to the information
which someone can learn about the system by looking at the number and
sizes of files from using PG-based encryption vs. what information
someone can learn from being able to look at only an encrypted
filesystem, but that's a trade-off which security experts are good at
making a determination on and will be case-by-case, based on how easy
setting up filesystem-encryption is in a particular environment and what
the use-cases are for the system.

> 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... ).

Thanks!

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Peter Eisentraut
On 6/13/17 09:24, Stephen Frost wrote:
> but there are
> use-cases where it'd be really nice to be able to have PG doing the
> encryption instead of the filesystem because then you can do things like
> backup the database, copy it somewhere else directly, and then restore
> it using the regular PG mechanisms, as long as you have access to the
> key.  That's not something you can directly do with filesystem-level
> encryption

Interesting point.

I wonder what the proper extent of "encryption at rest" should be.  If
you encrypt just on a file or block level, then someone looking at the
data directory or a backup can still learn a number of things about the
number of tables, transaction rates, various configuration settings, and
so on.  In the scenario of a sensitive application hosted on a shared
SAN, I don't think that is good enough.

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.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Stephen Frost
Ants, all,

* Ants Aasma (ants.aa...@eesti.ee) wrote:
> Yes, the plan is to pick it up again, Real Soon Now(tm). There are a
> couple of loose ends for stuff that should be encrypted, but in the
> current state of the patch aren't yet (from the top of my head,
> logical decoding and pg_stat_statements write some files). The code
> handling keys could really take better precautions as Peter pointed
> out in another e-mail. And I expect there to be a bunch of polishing
> work to make the APIs as good as they can be.

Very glad to hear that you're going to be continuing to work on this
effort.

> To answer Peter's question about HSMs, many enterprise deployments are
> on top of shared storage systems. For regulatory reasons or to limit
> security clearance of storage administrators, the data on shared
> storage should be encrypted. Now for there to be any point to this
> endeavor, the key needs to be stored somewhere else. This is where
> hardware security modules come in. They are basically hardware key
> storage appliances that can either output the key when requested, or
> for higher security hold onto the key and perform
> encryption/decryption on behalf of the user. The patch enables the
> user to use a custom shell command to go and fetch the key from the
> HSM, for example using the KMIP protocol. Or a motivated person could
> write an extension that implements the encryption hooks to delegate
> encryption/decryption of blocks to an HSM.

An extension, or perhaps even something built-in, would certainly be
good here but I don't think it's necessary in an initial implementation
as long as it's something we can do later.

> Fundamentally there doesn't seem to be a big benefit of implementing
> the encryption at PostgreSQL level instead of the filesystem. The
> patch doesn't take any real advantage from the higher level knowledge
> of the system, nor do I see much possibility for it to do that. The
> main benefit for us is that it's much easier to get a PostgreSQL based
> solution deployed.

Making it easier to get a PostgreSQL solution deployed is certainly a
very worthwhile goal.

> I'm curious if the community thinks this is a feature worth having?
> Even considering that security experts would classify this kind of
> encryption as a checkbox feature.

I would say that some security experts would consider it a 'checkbox'
feature, while others would say that it's actually a quite useful
capability for a database to have and isn't just for being able to check
a given box.  I tended to lean towards the 'checkbox' camp and
encouraged people to use filesystem encryption also, but there are
use-cases where it'd be really nice to be able to have PG doing the
encryption instead of the filesystem because then you can do things like
backup the database, copy it somewhere else directly, and then restore
it using the regular PG mechanisms, as long as you have access to the
key.  That's not something you can directly do with filesystem-level
encryption (unless you happen to be lucky enough to be able to use ZFS,
which can do exporting, or you can do a block-level exact copy to an
exactly identically sized partition on the remote side or similar..),
and while you could encrypt the PG files during the backup, that
requires that you make sure both sides agree on how that encryption is
done and have the same tools for performing the encryption/decryption.
Possible, certainly, but not nearly as convenient.

+1 for having this capability.

Thanks!

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] WIP: Data at rest encryption

2017-06-13 Thread Peter Eisentraut
On 6/12/17 17:11, Ants Aasma wrote:
> I'm curious if the community thinks this is a feature worth having?
> Even considering that security experts would classify this kind of
> encryption as a checkbox feature.

File system encryption already exists and is well-tested.  I don't see
any big advantages in re-implementing all of this one level up.  You
would have to touch every single place in PostgreSQL backend and tool
code where a file is being read or written.  Yikes.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-12 Thread Ants Aasma
On Mon, Jun 12, 2017 at 10:38 PM, Robert Haas  wrote:
> On Mon, Jun 13, 2016 at 11:07 AM, Peter Eisentraut
>  wrote:
>> On 6/7/16 9:56 AM, Ants Aasma wrote:
>>>
>>> Similar things can be achieved with filesystem level encryption.
>>> However this is not always optimal for various reasons. One of the
>>> better reasons is the desire for HSM based encryption in a storage
>>> area network based setup.
>>
>> Could you explain this in more detail?
>
> I don't think Ants ever responded to this point.
>
> I'm curious whether this is something that is likely to be pursued for
> PostgreSQL 11.

Yes, the plan is to pick it up again, Real Soon Now(tm). There are a
couple of loose ends for stuff that should be encrypted, but in the
current state of the patch aren't yet (from the top of my head,
logical decoding and pg_stat_statements write some files). The code
handling keys could really take better precautions as Peter pointed
out in another e-mail. And I expect there to be a bunch of polishing
work to make the APIs as good as they can be.

To answer Peter's question about HSMs, many enterprise deployments are
on top of shared storage systems. For regulatory reasons or to limit
security clearance of storage administrators, the data on shared
storage should be encrypted. Now for there to be any point to this
endeavor, the key needs to be stored somewhere else. This is where
hardware security modules come in. They are basically hardware key
storage appliances that can either output the key when requested, or
for higher security hold onto the key and perform
encryption/decryption on behalf of the user. The patch enables the
user to use a custom shell command to go and fetch the key from the
HSM, for example using the KMIP protocol. Or a motivated person could
write an extension that implements the encryption hooks to delegate
encryption/decryption of blocks to an HSM.

Fundamentally there doesn't seem to be a big benefit of implementing
the encryption at PostgreSQL level instead of the filesystem. The
patch doesn't take any real advantage from the higher level knowledge
of the system, nor do I see much possibility for it to do that. The
main benefit for us is that it's much easier to get a PostgreSQL based
solution deployed.

I'm curious if the community thinks this is a feature worth having?
Even considering that security experts would classify this kind of
encryption as a checkbox feature.

Regards,
Ants Aasma


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2017-06-12 Thread Robert Haas
On Mon, Jun 13, 2016 at 11:07 AM, Peter Eisentraut
 wrote:
> On 6/7/16 9:56 AM, Ants Aasma wrote:
>>
>> Similar things can be achieved with filesystem level encryption.
>> However this is not always optimal for various reasons. One of the
>> better reasons is the desire for HSM based encryption in a storage
>> area network based setup.
>
> Could you explain this in more detail?

I don't think Ants ever responded to this point.

I'm curious whether this is something that is likely to be pursued for
PostgreSQL 11.

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2016-06-15 Thread PostgreSQL - Hans-Jürgen Schönig



On 06/14/2016 09:59 PM, Jim Nasby wrote:

On 6/12/16 2:13 AM, Ants Aasma wrote:

On Fri, Jun 10, 2016 at 5:23 AM, Haribabu Kommi
 wrote:

> 1. Instead of doing the entire database files encryption, how about
> providing user an option to protect only some particular tables that
> wants the encryption at table/tablespace level. This not only 
provides

> an option to the user, it reduces the performance impact on tables
> that doesn't need any encryption. The problem with this approach
> is that every xlog record needs to validate to handle the encryption
> /decryption, instead of at page level.

Is there a real need for this? The customers I have talked to want to
encrypt the whole database and my goal is to make the feature fast
enough to make that feasible for pretty much everyone. I guess
switching encryption off per table would be feasible, but the key
setup would still need to be done at server startup. Per record
encryption would result in some additional information leakage though.
Overall I thought it would not be worth it, but I'm willing to have my
mind changed on this.


I actually design with this in mind. Tables that contain sensitive 
info go into designated schemas, partly so that you can blanket move 
all of those to an encrypted tablespace (or safer would be to move 
things not in those schemas to an unencrypted tablespace). Since that 
can be done with an encrypted filesystem maybe that's good enough. 
(It's not really clear to me what this buys us over an encrypted FS, 
other than a feature comparison checkmark...)


the reason why this is needed is actually very simple: security 
guidelines and legal requirements ...
we have dealt with a couple of companies recently, who explicitly 
demanded PostgreSQL level encryption in a transparent way to fulfill 
some internal or legal requirements. this is especially true for 
financial stuff. and yes, sure ... you can do a lot of stuff with 
filesystem encryption.
the core idea of this entire thing is however to have a counterpart on 
the database level. if you don't have the key you cannot start the 
instance and if you happen to get access to the filesystem you are still 
not able to fire up the DB.

as it said: requirements by ever bigger companies.

as far as benchmarking is concerned: i did a quick test yesterday (not 
with the final AES implementation yet) and i got pretty good results. 
with a reasonably well cached database in a typical application I expect 
to loose around 10-20%. if everything fits in memory there is 0 loss of 
course. the worst I got with the standard AES (no hardware support used 
yet) I lost around 45% or so. but this requires a value as low as 32 MB 
of shared buffers or so.


many thanks,

hans


--
Hans-Jürgen Schönig
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de, http://www.cybertec.at



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2016-06-14 Thread Jim Nasby

On 6/12/16 2:13 AM, Ants Aasma wrote:

On Fri, Jun 10, 2016 at 5:23 AM, Haribabu Kommi
 wrote:

> 1. Instead of doing the entire database files encryption, how about
> providing user an option to protect only some particular tables that
> wants the encryption at table/tablespace level. This not only provides
> an option to the user, it reduces the performance impact on tables
> that doesn't need any encryption. The problem with this approach
> is that every xlog record needs to validate to handle the encryption
> /decryption, instead of at page level.

Is there a real need for this? The customers I have talked to want to
encrypt the whole database and my goal is to make the feature fast
enough to make that feasible for pretty much everyone. I guess
switching encryption off per table would be feasible, but the key
setup would still need to be done at server startup. Per record
encryption would result in some additional information leakage though.
Overall I thought it would not be worth it, but I'm willing to have my
mind changed on this.


I actually design with this in mind. Tables that contain sensitive info 
go into designated schemas, partly so that you can blanket move all of 
those to an encrypted tablespace (or safer would be to move things not 
in those schemas to an unencrypted tablespace). Since that can be done 
with an encrypted filesystem maybe that's good enough. (It's not really 
clear to me what this buys us over an encrypted FS, other than a feature 
comparison checkmark...)

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2016-06-13 Thread Haribabu Kommi
On Sun, Jun 12, 2016 at 5:13 PM, Ants Aasma  wrote:
> On Fri, Jun 10, 2016 at 5:23 AM, Haribabu Kommi
>  wrote:
>
>> 2. Instead of depending on a contrib module for the encryption, how
>> about integrating pgcrypto contrib in to the core and add that as a
>> default encryption method. And also provide an option to the user
>> to use a different encryption methods if needs.
>
> Technically that would be simple enough, this is more of a policy
> decision. I think having builtin encryption provided by pgcrypto is
> completely fine. If a consensus emerges that it needs to be
> integrated, it would need to be a separate patch anyway.

In our proprietary database, we are using the encryption methods
provided by openSSL [1]. May be we can have a look at those
methods provided by openSSL for the use of encryption for builds
under USE_SSL. Ignore it if you have already validated.


>> 5. Instead of providing passphrase through environmental variable,
>> better to provide some options to pg_ctl etc.
>
> That looks like it would be worse from a security perspective.
> Integrating a passphrase prompt would be an option, but a way for
> scripts to provide passphrases would still be needed.

What I felt was, if we store the passphrase in an environmental variable,
a person who is having an access to the system can get the details
and using that it may be possible to decrypt the data files.


[1] - https://www.openssl.org/docs/manmaster/crypto/EVP_EncryptInit.html


Regards,
Hari Babu
Fujitsu Australia


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2016-06-13 Thread Peter Eisentraut

On 6/12/16 3:13 AM, Ants Aasma wrote:

5. Instead of providing passphrase through environmental variable,
> better to provide some options to pg_ctl etc.

That looks like it would be worse from a security perspective.
Integrating a passphrase prompt would be an option, but a way for
scripts to provide passphrases would still be needed.


Environment variables and command-line options are visible to other 
processes on the machine, so neither of these approaches is really going 
to work.  We would need some kind of integration with secure 
password-entry mechanisms, such as pinentry.


Also note that all tools that work directly on the data directory would 
need password-entry and encryption/decryption support, including 
pg_basebackup, pg_controldata, pg_ctl, pg_receivexlog, pg_resetxlog, 
pg_rewind, pg_upgrade, pg_xlogdump.


It seems that your implementation doesn't encrypt pg_control, thus 
avoiding some of that.  But that doesn't seem right.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2016-06-13 Thread Peter Eisentraut

On 6/7/16 9:56 AM, Ants Aasma wrote:

Similar things can be achieved with filesystem level encryption.
However this is not always optimal for various reasons. One of the
better reasons is the desire for HSM based encryption in a storage
area network based setup.


Could you explain this in more detail?

--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2016-06-13 Thread Ants Aasma
On Mon, Jun 13, 2016 at 5:17 AM, Michael Paquier
 wrote:
> On Sun, Jun 12, 2016 at 4:13 PM, Ants Aasma  wrote:
>>> I feel separate file is better to include the key data instead of pg_control
>>> file.
>>
>> I guess that would be more flexible. However I think at least the fact
>> that the database is encrypted should remain in the control file to
>> provide useful error messages for faulty backup procedures.
>
> Another possibility could be always to do some encryption at data-type
> level for text data. For example I recalled the following thing while
> going through this thread:
> https://github.com/nec-postgres/tdeforpg
> Though I don't quite understand the use for encrypt.enable in this
> code... This has the advantage to not patch upstream.

While certainly possible, this does not cover the requirements I want
to satisfy - user data never gets stored on disk unencrypted without
making changes to the application or schema. This seems to be mostly
about separating administrator roles, specifically that centralised
storage and backup administrators should not have access to database
contents. I see this as orthogonal to per column encryption, which in
my opinion is better done in the application.

Regards,
Ants Aasma


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2016-06-12 Thread Michael Paquier
On Sun, Jun 12, 2016 at 4:13 PM, Ants Aasma  wrote:
>> I feel separate file is better to include the key data instead of pg_control
>> file.
>
> I guess that would be more flexible. However I think at least the fact
> that the database is encrypted should remain in the control file to
> provide useful error messages for faulty backup procedures.

Another possibility could be always to do some encryption at data-type
level for text data. For example I recalled the following thing while
going through this thread:
https://github.com/nec-postgres/tdeforpg
Though I don't quite understand the use for encrypt.enable in this
code... This has the advantage to not patch upstream.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2016-06-12 Thread Ants Aasma
On Fri, Jun 10, 2016 at 5:23 AM, Haribabu Kommi
 wrote:
> 1. Instead of doing the entire database files encryption, how about
> providing user an option to protect only some particular tables that
> wants the encryption at table/tablespace level. This not only provides
> an option to the user, it reduces the performance impact on tables
> that doesn't need any encryption. The problem with this approach
> is that every xlog record needs to validate to handle the encryption
> /decryption, instead of at page level.

Is there a real need for this? The customers I have talked to want to
encrypt the whole database and my goal is to make the feature fast
enough to make that feasible for pretty much everyone. I guess
switching encryption off per table would be feasible, but the key
setup would still need to be done at server startup. Per record
encryption would result in some additional information leakage though.
Overall I thought it would not be worth it, but I'm willing to have my
mind changed on this.

> 2. Instead of depending on a contrib module for the encryption, how
> about integrating pgcrypto contrib in to the core and add that as a
> default encryption method. And also provide an option to the user
> to use a different encryption methods if needs.

Technically that would be simple enough, this is more of a policy
decision. I think having builtin encryption provided by pgcrypto is
completely fine. If a consensus emerges that it needs to be
integrated, it would need to be a separate patch anyway.

> 3. Currently entire xlog pages are encrypted and stored in the file.
> can pg_xlogdump works with those files?

Technically yes, with the patch as it stands, no. Added this to my todo list.

> 4. For logical decoding, how about the adding a decoding behavior
> based on the module to decide whether data to be encrypted/decrypted.

The data to be encrypted does not depend on the module used, so I
don't think it should be module controlled. The reorder buffer
contains pretty much the same stuff as the xlog, so not encrypting it
does not look like a valid choice. For logical heap rewrites it could
be argued that nothing useful is leaked in most cases, but encrypting
it is not hard. Just a small matter of programming.

> 5. Instead of providing passphrase through environmental variable,
> better to provide some options to pg_ctl etc.

That looks like it would be worse from a security perspective.
Integrating a passphrase prompt would be an option, but a way for
scripts to provide passphrases would still be needed.

> 6. I don't have any idea whether is it possible to integrate the checksum
> and encryption in a single shot to avoid performance penalty.

Currently no, the checksum gets stored in the page header and for any
decent cipher mode the encryption of the rest of the page will depend
on it. However, the performance difference should be negligible
because both algorithms are compute bound for cached data. The data is
very likely to be completely in L1 cache as the operations are done in
quick succession.

The non-cryptographic checksum algorithm could actually be an attack
vector for an adversary that can trigger repeated encryption by
tweaking a couple of bytes at the end of the page to see when the
checksum matches and try to infer the data from that. Similarly to the
CRIME attack. However the LSN stored at the beginning of the page
header basically provides a nonce that makes this impossible.

This also means that encryption needs to imply wal_log_hints. Will
include this in the next version of the patch.

>> I would also like to incorporate some database identifier as a salt in
>> key setup. However, system identifier stored in control file doesn't
>> fit this role well. It gets initialized somewhat too late in the
>> bootstrap process, and more importantly, gets changed on pg_upgrade.
>> This will make link mode upgrades impossible, which seems like a no
>> go. I'm torn whether to add a new value for this purpose (perhaps
>> stored outside the control file) or allow setting of system identifier
>> via initdb. The first seems like a better idea, the file could double
>> as a place to store additional encryption parameters, like key length
>> or different cipher primitive.
>
> I feel separate file is better to include the key data instead of pg_control
> file.

I guess that would be more flexible. However I think at least the fact
that the database is encrypted should remain in the control file to
provide useful error messages for faulty backup procedures.

Thanks for your input.

Regards,
Ants Aasma


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WIP: Data at rest encryption

2016-06-09 Thread Haribabu Kommi
On Tue, Jun 7, 2016 at 11:56 PM, Ants Aasma  wrote:
> Hi all,
>
> I have been working on data-at-rest encryption support for PostgreSQL.
> In my experience this is a common request that customers make. The
> short of the feature is that all PostgreSQL data files are encrypted
> with a single master key and are decrypted when read from the OS. It
> does not provide column level encryption which is an almost orthogonal
> feature, arguably better done client side.
>
> Similar things can be achieved with filesystem level encryption.
> However this is not always optimal for various reasons. One of the
> better reasons is the desire for HSM based encryption in a storage
> area network based setup.
>
> Attached to this mail is a work in progress patch that adds an
> extensible encryption mechanism. There are some loose ends left to tie
> up, but the general concept and architecture is at a point where it's
> ready for some feedback, fresh ideas and bikeshedding.

Yes, encryption is really a nice and wanted feature.
Following are my thoughts regarding the approach.

1. Instead of doing the entire database files encryption, how about
providing user an option to protect only some particular tables that
wants the encryption at table/tablespace level. This not only provides
an option to the user, it reduces the performance impact on tables
that doesn't need any encryption. The problem with this approach
is that every xlog record needs to validate to handle the encryption
/decryption, instead of at page level.

2. Instead of depending on a contrib module for the encryption, how
about integrating pgcrypto contrib in to the core and add that as a
default encryption method. And also provide an option to the user
to use a different encryption methods if needs.

3. Currently entire xlog pages are encrypted and stored in the file.
can pg_xlogdump works with those files?

4. For logical decoding, how about the adding a decoding behavior
based on the module to decide whether data to be encrypted/decrypted.

5. Instead of providing passphrase through environmental variable,
better to provide some options to pg_ctl etc.

6. I don't have any idea whether is it possible to integrate the checksum
and encryption in a single shot to avoid performance penalty.


> I would also like to incorporate some database identifier as a salt in
> key setup. However, system identifier stored in control file doesn't
> fit this role well. It gets initialized somewhat too late in the
> bootstrap process, and more importantly, gets changed on pg_upgrade.
> This will make link mode upgrades impossible, which seems like a no
> go. I'm torn whether to add a new value for this purpose (perhaps
> stored outside the control file) or allow setting of system identifier
> via initdb. The first seems like a better idea, the file could double
> as a place to store additional encryption parameters, like key length
> or different cipher primitive.

I feel separate file is better to include the key data instead of pg_control
file.


Regards,
Hari Babu
Fujitsu Australia


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] WIP: Data at rest encryption

2016-06-07 Thread Ants Aasma
Hi all,

I have been working on data-at-rest encryption support for PostgreSQL.
In my experience this is a common request that customers make. The
short of the feature is that all PostgreSQL data files are encrypted
with a single master key and are decrypted when read from the OS. It
does not provide column level encryption which is an almost orthogonal
feature, arguably better done client side.

Similar things can be achieved with filesystem level encryption.
However this is not always optimal for various reasons. One of the
better reasons is the desire for HSM based encryption in a storage
area network based setup.

Attached to this mail is a work in progress patch that adds an
extensible encryption mechanism. There are some loose ends left to tie
up, but the general concept and architecture is at a point where it's
ready for some feedback, fresh ideas and bikeshedding.

Usage
=

Set up database like so:

(read -sp "Postgres passphrase: " PGENCRYPTIONKEY; echo;
 export PGENCRYPTIONKEY
 initdb -k -K pgcrypto $PGDATA )

Start PostgreSQL:

(read -sp "Postgres passphrase: " PGENCRYPTIONKEY; echo;
 export PGENCRYPTIONKEY
 postgres $PGDATA )

Design
==

The patch adds a new GUC called encryption_library, when specified the
named library is loaded before shared_preload_libraries and is
expected to register its encryption routines. For now the API is
pretty narrow, one parameterless function that lets the extension do
key setup on its own terms, and two functions for
encrypting/decrypting an arbitrary sized block of data with tweak. The
tweak should alter the encryption function so that identical block
contents are encrypted differently based on their location. The GUC
needs to be set at bootstrap time, so it gets set by a new option for
initdb. During bootstrap an encryption sample gets stored in the
control file, enabling useful error messages.

The library name is not stored in controldata. I'm not quite sure
about this decision. On one hand it would be very useful to tell the
user what he needs to get at his data if the configuration somehow
goes missing and it would get rid of the extra GUC. On the other hand
I don't really want to bloat control data, and the same encryption
algorithm could be provided by different implementations.

For now the encryption is done for everything that goes through md,
xlog and slru. Based on a review of read/write/fread/fwrite calls this
list is missing:

* BufFile - needs refactoring
* Logical reorder buffer serialization - probably needs a stream mode
cipher API addition.
* logical_heap_rewrite - can be encrypted as one big block
* 2PC state data - ditto
* pg_stat_statements - query texts get appended so a stream mode
cipher might be needed here too.

copydir needed some changes too because tablespace and database oid
are included in the tweak and so copying also needs to decrypt and
encrypt with the new tweak value.

For demonstration purposes I imported Brian Gladman's AES-128-XTS mode
implementation into pgcrypto and used an environment variable for key
setup. This part is not really in any reviewable state, the XTS code
needs heavy cleanup to bring it up to PostgreSQL coding standards,
keysetup needs something secure, like PBKDF2 or scrypt.

Performance with current AES implementation is not great, but not
horrible either, I'm seeing around 2x slowdown for larger than
shared_buffers, smaller than free memory workloads. However the plan
is to fix this - I have a prototype AES-NI implementation that does
3GB/s per core on my Haswell based laptop (1.25 B/cycle).

Open questions
==

The main questions is what to do about BufFile? It currently provides
both unaligned random access and a block based interface. I wonder if
it would be a good idea to refactor it to be fully block based under
the covers.

I would also like to incorporate some database identifier as a salt in
key setup. However, system identifier stored in control file doesn't
fit this role well. It gets initialized somewhat too late in the
bootstrap process, and more importantly, gets changed on pg_upgrade.
This will make link mode upgrades impossible, which seems like a no
go. I'm torn whether to add a new value for this purpose (perhaps
stored outside the control file) or allow setting of system identifier
via initdb. The first seems like a better idea, the file could double
as a place to store additional encryption parameters, like key length
or different cipher primitive.

Regards,
Ants Aasma
diff --git a/contrib/pgcrypto/Makefile b/contrib/pgcrypto/Makefile
index 18bad1a..04ce887 100644
--- a/contrib/pgcrypto/Makefile
+++ b/contrib/pgcrypto/Makefile
@@ -20,7 +20,7 @@ SRCS		= pgcrypto.c px.c px-hmac.c px-crypt.c \
 		mbuf.c pgp.c pgp-armor.c pgp-cfb.c pgp-compress.c \
 		pgp-decrypt.c pgp-encrypt.c pgp-info.c pgp-mpi.c \
 		pgp-pubdec.c pgp-pubenc.c pgp-pubkey.c pgp-s2k.c \
-		pgp-pgsql.c
+		pgp-pgsql.c xts.c
 
 MODULE_big	= pgcrypto
 OBJS		= $(SRCS:.c=.o) $(WIN32R