Re: Transparent column encryption - Mailing list pgsql-hackers

Hi,

On 2023-03-29 19:08:25 +0200, Peter Eisentraut wrote:
> On 29.03.23 18:24, Andres Freund wrote:
> > On 2023-03-29 18:08:29 +0200, Peter Eisentraut wrote:
> > > On 24.03.23 19:12, Andres Freund wrote:
> > > > > I thought about this some more.  I think we could get rid of attusertypmod
> > > > > and just hardcode it as -1.  The idea would be that if you ask for an
> > > > > encrypted column of type, say, varchar(500), the server isn't able to
> > > > > enforce that anyway, so we could just prohibit specifying a nondefault
> > > > > typmod for encrypted columns.
> > > > 
> > > > Why not just use typmod for the underlying typmod? It doesn't seem like
> > > > encrypted datums will need that? Or are you using it for something important there?
> > > 
> > > Yes, the typmod of encrypted types stores the encryption algorithm.
> > 
> > Why isn't that an attribute of pg_colenckey, given that attcek has been added
> > to pg_attribute?
> 
> One might think that, but the precedent in other equivalent systems is that
> you reference the key and the algorithm separately.  There is some
> (admittedly not very conclusive) discussion about this near [0].
> 
> [0]:
https://www.postgresql.org/message-id/flat/00b0c4f3-0d9f-dcfd-2ba0-eee5109b4963%40enterprisedb.com#147ad6faafe8cdd2c0d2fd56ec972a40

I'm very much not convinced by that. Either way, there at least there should
be a comment mentioning that we intentionally try to allow that.

Even if this feature is something we want (why?), ISTM that this should not be
implemented by having multiple fields in pg_attribute, but instead by a table
referenced by by pg_attribute.attcek.


> > > (Also, mixing a type with the typmod of another type is weird in a variety
> > > of ways, so this is a quite clean solution.)
> > 
> > It's not an unrelated type though. It's the actual typmod of the column we're
> > talking about.
> 
> What I mean is that various parts of the system think that typid+typmod make
> sense together.  If the typmod actually refers to usertypid, well, the code
> doesn't know that, so who knows what happens.

You control what the typmod for encrypted columns does. So I don't see what
problems that could be.

I seems quite likely that having a separate typmod for the encrypted type will
cause problems down the line, because you'll end up having to copy around
typid+typmod for the encrypted datum and then also separately for the
unencrypted one.


> Also, with the current proposal, the system is internally consistent:
> pg_encrypted_* can actually look at their own typmod and verify their own
> input values that way, which is what a typmod is for.  This just works out
> of the box.
> 
> > I find it a lot less clean to make all non-CEK uses of
> > pg_attribute pay the price of storing three new fields.
> 
> With the proposed removal of usertypmod, it's only two fields: the link to
> the key and the user-facing type.

That feels far less clean. I think loosing the ability to set the precision of
a numeric, or the SRID for postgis datums won't be received very well?

Greetings,

Andres Freund