Message-ID: <0ec3a98a-6347-4a40-c135-3b9cf3847d25@dalibo.com>
Date: Fri, 28 Oct 2022 16:07:20 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.3.0
To: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>,
 Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
References: <89157929-c2b6-817b-6025-8e4b2d89d88f@enterprisedb.com>
 <48a9f2c2-4a57-27d8-7c53-16a23a01014e@enterprisedb.com>
 <79f08a39-a7da-5157-cef4-378fb60c18f8@enterprisedb.com>
 <258c5064-437e-f41e-7537-5e8c343c33cc@enterprisedb.com>
 <ddb316e8-a978-ee69-1edd-0ee82ecc6d8b@enterprisedb.com>
 <edb5eb1f-5818-5521-537d-20cf7f470742@enterprisedb.com>
 <6bd99fea-3298-854d-d37f-554151342f36@enterprisedb.com>
 <963aa100-7e78-3463-0645-700eaaa325f2@enterprisedb.com>
 <06830254-6d87-86b2-0280-bf2eee7736a5@enterprisedb.com>
 <a86c8d36-e9c5-cc5b-2c50-cc563d818419@enterprisedb.com>
 <20221028121629.6467d8f9@karst>
Content-Language: fr, en-US
From: =?UTF-8?Q?Fr=c3=a9d=c3=a9ric_Yhuel?= <frederic.yhuel@dalibo.com>
Subject: Re: Transparent column encryption
In-Reply-To: <20221028121629.6467d8f9@karst>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/0ec3a98a-6347-4a40-c135-3b9cf3847d25%40dalibo.com>
Precedence: bulk

Hi,

Here are a few more things I noticed :

If a CEK is encrypted with cmk1 and cmk2, but cmk1 isn't found on the 
client,the following error is printed twice for the very first SELECT 
statement:

   could not open file "/path/to/cmk1.pem": No such file or directory

...and nothing is returned. The next queries in the same session would 
work  correctly (cmk2 is used for the decryption of the CEK). An INSERT 
statement si handled properly, though : one (and only one) error 
message, and line actually inserted in all cases).

For example :

   postgres=# SELECT * FROM customers ;
   could not open file "/path/to/cmk1.pem": No such file or directory

   could not open file "/path/to/cmk1.pem": No such file or directory

   postgres=# SELECT * FROM customers ;
    id | name  | creditcard_num
   ----+-------+-----------------
     1 | toto  | 546843351354245
     2 | babar | 546843351354245

<close and open new psql session>

   postgres=# INSERT INTO customers (id, name, creditcard_num) VALUES 
  ($1, $2, $3) \gencr '3' 'toto' '546888351354245';
   could not open file "/path/to/cmk1.pem": No such file or directory

   INSERT 0 1
   postgres=# SELECT * FROM customers ;
    id | name  | creditcard_num
   ----+-------+-----------------
     1 | toto  | 546843351354245
     2 | babar | 546843351354245
     3 | toto  | 546888351354245


 From the documentation of CREATE COLUMN MASTER KEY, it looks like the 
REALM is optional, but both
   CREATE COLUMN MASTER KEY cmk1;
and
   CREATE COLUMN MASTER KEY cmk1 WITH ();
returns a syntax error.


About AEAD, the documentation says :
 > The “associated data” in these algorithms consists of 4 bytes: The 
ASCII letters P and G (byte values 80 and 71), followed by the algorithm 
ID as a 16-bit unsigned integer in network byte order.

My guess is that it serves no real purpose, did I misunderstand ?