Re: Transparent column encryption

On 30.03.23 20:35, Stephen Frost wrote:
> I do feel that column encryption is a useful capability and there's
> large parts of this approach that I agree with, but I dislike the idea
> of having our clients be able to depend on what gets returned for
> non-encrypted columns while not being able to trust what encrypted
> column results are and then trying to say it's 'transparent'.  To that
> end, it seems like just saying they get back a bytea and making it clear
> that they have to provide the validation would be clear, while keeping
> much of the rest.

[Note that the word "transparent" has been removed from the feature 
name.  I just didn't change the email thread name.]

These thoughts are reasonable, but I think there is a tradeoff to be 
made between having featureful data validation and enhanced security. 
If you want your database system to validate your data, you have to send 
it in plain text.  If you want to have your database system not see the 
plain text, then it cannot validate it.  But there is still utility in it.

You can't really depend on what gets returned even in the non-encrypted 
case, unless you cryptographically sign the schema against modification 
or something like that.  So realistically, a client that cares strongly 
about the data it receives has to do some kind of client-side validation 
anyway.

Note also that higher-level client libraries like JDBC effectively do 
client-side data validation, for example when you call 
ResultSet.getInt() etc.

This is also one of the reasons why the user facing type declaration 
exists.  You could just make all encrypted columns of type "opaque" or 
something and not make any promises about what's inside.  But client 
APIs sort or rely on the application being able to ask the result set 
for what's inside a column value.  If we just say, we don't know, then 
applications (or driver APIs) will have to be changed to accommodate 
that, but the intention was to not require that.  So instead we say, 
it's supposed to be int, and then if it's sometimes actually not int, 
then your application throws an exception you can deal with.  This is 
arguably a better developer experience, even if it concerns the data 
type purist.

But do you have a different idea about how it should work?

> Expanding out from that I'd imagine, pie-in-the-sky
> and in some far off land, having our data type in/out validation
> functions moved to the common library and then adding client-side
> validation of the data going in/out of the encrypted columns would allow
> application developers to be able to trust what we're returning (as long
> as they're using libpq- and we'd have to document that independent
> implementations of the protocol have to provide this or just continue to
> return bytea's).

As mentioned, some client libraries effectively already do that.  But 
even if we could make this much more comprehensive, I don't see how this 
can ever actually satisfy your point.  It would require that all 
participating clients apply validation all the time, and all other 
clients can rely on that happening, which is impossible.