Re: Transparent column encryption

On Tue, Jul 19, 2022 at 10:52 PM Peter Eisentraut
<peter.eisentraut@enterprisedb.com> wrote:
>
> On 12.07.22 20:29, Peter Eisentraut wrote:
> > Updated patch, to resolve some merge conflicts.
>
> Rebased patch, no new functionality

Thank you for working on this and updating the patch!

I've mainly looked at the documentation and tests and done some tests.
Before looking at the code in depth, I'd like to share my
comments/questions.

---
Regarding the documentation, I'd like to have a page that describes
the generic information of the transparent column encryption for users
such as what this feature actually does, what can be achieved by this
feature, CMK rotation, and its known limitations. The patch has
"Transparent Column Encryption" section in protocol.sgml but it seems
to be more internal information.

---
In datatype.sgml, it says "Thus, clients that don't support
transparent column encryption or have disabled it will see the
encrypted values as byte arrays." but I got an error rather than
encrypted values when I tried to connect to the server using by
clients that don't support the encryption:

postgres(1:6040)=# select * from tbl;
no CMK lookup found for realm ""

no CMK lookup found for realm ""
postgres(1:6040)=#

---
In single-user mode, the user cannot decrypt the encrypted value but
probably it's fine in practice.

---
Regarding the column master key rotation, would it be useful if we
provide a tool for that? For example, it takes old and new CMK as
input, re-encrypt all CEKs realted to the CMK, and registers them to
the server.

---
Is there any convenient way to load a large amount of test data to the
encrypted columns? I tried to use generate_series() but it seems not
to work as it generates the data on the server side:

postgres(1:80556)=# create table a (i text encrypted with
(column_encryption_key = cek1));
CREATE TABLE
postgres(1:80556)=# insert into a select i::text from
generate_series(1, 1000) i;
2022-07-20 15:06:38.502 JST [80556] ERROR:  column "i" is of type
pg_encrypted_rnd but expression is of type text at character 22

I've also tried to load the data from a file on the client by using
\copy command, but it seems not to work:

postgres(1:80556)=# copy (select generate_series(1, 1000)::text) to
'/tmp/tmp.dat';
COPY 1000
postgres(1:80556)=# \copy a from '/tmp/tmp.dat'
COPY 1000
postgres(1:80556)=# select * from a;
out out memory

---
I got SEGV in the following two situations:

(1) SEGV by backend
postgres(1:59931)=# create table tbl (i int encrypted with
(column_encryption_key = cek1));
CREATE TABLE
postgres(1:59931)=# insert into tbl values ($1) \gencr 1
INSERT 0 1
postgres(1:59931)=# select * from tbl;
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

The backtrace is:

(lldb) bt
* thread #1, stop reason = signal SIGSTOP
  * frame #0: 0x0000000106830a30
postgres`pg_detoast_datum_packed(datum=0xffffffffab32c563) at
fmgr.c:1742:6
    frame #1: 0x00000001067d9dbf
postgres`byteaout(fcinfo=0x00007ffee9c311a8) at varlena.c:392:20
    frame #2: 0x000000010682ed0c
postgres`FunctionCall1Coll(flinfo=0x00007faeb28193a8, collation=0,
arg1=18446744072286815587) at fmgr.c:1124:11
    frame #3: 0x0000000106830611
postgres`OutputFunctionCall(flinfo=0x00007faeb28193a8,
val=18446744072286815587) at fmgr.c:1561:9
    frame #4: 0x0000000105fed702
postgres`printtup(slot=0x00007faeb2818960, self=0x00007faeb3809390) at
printtup.c:519:16
    frame #5: 0x0000000106319318
postgres`ExecutePlan(estate=0x00007faeb2818520,
planstate=0x00007faeb2818758, use_parallel_mode=false,
operation=CMD_SELECT, sendTuples=true, numberTuples=0,
direction=ForwardScanDirection, dest=0x00007faeb3809390,
execute_once=true) at execMain.c:1667:9
    frame #6: 0x0000000106319180
postgres`standard_ExecutorRun(queryDesc=0x00007faeb280d920,
direction=ForwardScanDirection, count=0, execute_once=true) at
execMain.c:363:3
    frame #7: 0x0000000106318f11
postgres`ExecutorRun(queryDesc=0x00007faeb280d920,
direction=ForwardScanDirection, count=0, execute_once=true) at
execMain.c:307:3
    frame #8: 0x000000010661139c
postgres`PortalRunSelect(portal=0x00007faeb5028920, forward=true,
count=0, dest=0x00007faeb3809390) at pquery.c:924:4
    frame #9: 0x0000000106610d3f
postgres`PortalRun(portal=0x00007faeb5028920,
count=9223372036854775807, isTopLevel=true, run_once=true,
dest=0x00007faeb3809390, altdest=0x00007faeb3809390,
qc=0x00007ffee9c31620) at pquery.c:768:18
    frame #10: 0x000000010660bb93
postgres`exec_simple_query(query_string="select * from tbl;") at
postgres.c:1246:10
    frame #11: 0x000000010660ac2f
postgres`PostgresMain(dbname="postgres", username="masahiko") at
postgres.c:4534:7
    frame #12: 0x000000010650d9c6
postgres`BackendRun(port=0x00007faeb3004210) at postmaster.c:4490:2
    frame #13: 0x000000010650cf8a
postgres`BackendStartup(port=0x00007faeb3004210) at
postmaster.c:4218:3
    frame #14: 0x000000010650bd57 postgres`ServerLoop at postmaster.c:1808:7
    frame #15: 0x00000001065094cf postgres`PostmasterMain(argc=5,
argv=0x00007faeb2406320) at postmaster.c:1480:11
    frame #16: 0x00000001063b4dcf postgres`main(argc=5,
argv=0x00007faeb2406320) at main.c:197:3
    frame #17: 0x00007fff721abcc9 libdyld.dylib`start + 1

(2) SEGV by psql

postgres(1:47762)=# create table tbl (t text encrypted with
(column_encryption_key = cek1));
CREATE TABLE
postgres(1:47762)=# insert into tbl values ('test');
INSERT 0 1
postgres(1:47762)=# select * from tbl;
Segmentation fault: 11 (core dumped)

The backtrace is:

(lldb) bt
* thread #1, stop reason = signal SIGSTOP
  * frame #0: 0x00007fff723a1b36
libsystem_platform.dylib`_platform_memmove$VARIANT$Haswell + 566
    frame #1: 0x000000010c509a5f
libpq.5.dylib`get_message_auth_tag(md=0x000000010c7f28b8, mac_key="
\x1c,\x98g½ȩ[\x88\x16\x12Kiꔂ\v8g_\x80, mac_key_len=16, encr="test",
encrlen=-12, cekalg=130, md_value="", md_len_p=0x00007ffee380a720,
errmsgp=0x00007ffee380a8c0) at fe-encrypt-openssl.c:316:2
    frame #2: 0x000000010c509442
libpq.5.dylib`decrypt_value(res=0x00007fa098504770,
cek=0x00007fa0985045d0, cekalg=130, input="test", inputlen=4,
errmsgp=0x00007ffee380a8c0) at fe-encrypt-openssl.c:429:7
    frame #3: 0x000000010c4f3c0c
libpq.5.dylib`pqRowProcessor(conn=0x00007fa098808e00,
errmsgp=0x00007ffee380a8c0) at fe-exec.c:1670:21
    frame #4: 0x000000010c5013bb
libpq.5.dylib`getAnotherTuple(conn=0x00007fa098808e00, msgLength=16)
at fe-protocol3.c:882:6
    frame #5: 0x000000010c4ffbf2
libpq.5.dylib`pqParseInput3(conn=0x00007fa098808e00) at
fe-protocol3.c:410:11
    frame #6: 0x000000010c4f5b65
libpq.5.dylib`parseInput(conn=0x00007fa098808e00) at fe-exec.c:2598:2
    frame #7: 0x000000010c4f5c59
libpq.5.dylib`PQgetResult(conn=0x00007fa098808e00) at fe-exec.c:2684:3
    frame #8: 0x000000010c401a77
psql`ExecQueryAndProcessResults(query="select * from tbl;",
elapsed_msec=0x00007ffee380ab08, svpt_gone_p=0x00007ffee380aafe,
is_watch=false, opt=0x0000000000000000,
printQueryFout=0x0000000000000000) at common.c:1514:11
    frame #9: 0x000000010c402469 psql`SendQuery(query="select * from
tbl;") at common.c:1171:9
    frame #10: 0x000000010c41a4dd
psql`MainLoop(source=0x00007fff98ce8d90) at mainloop.c:439:16
    frame #11: 0x000000010c426b44 psql`main(argc=3,
argv=0x00007ffee380adc8) at startup.c:462:19
    frame #12: 0x00007fff721abcc9 libdyld.dylib`start + 1
    frame #13: 0x00007fff721abcc9 libdyld.dylib`start + 1
(lldb) f 1
frame #1: 0x000000010c509a5f
libpq.5.dylib`get_message_auth_tag(md=0x000000010c7f28b8, mac_key="
\x1c,\x98g½ȩ[\x88\x16\x12Kiꔂ\v8g_\x80, mac_key_len=16, encr="test",
encrlen=-12, cekalg=130, md_value="", md_len_p=0x00007ffee380a720,
errmsgp=0x00007ffee380a8c0) at fe-encrypt-openssl.c:316:2
   313  #else
   314          memcpy(buf, test_A, sizeof(test_A));
   315  #endif
-> 316          memcpy(buf + PG_AD_LEN, encr, encrlen);
   317          *(int64 *) (buf + PG_AD_LEN + encrlen) =
pg_hton64(PG_AD_LEN * 8);
   318
   319          if (!EVP_DigestSignInit(evp_md_ctx, NULL, md, NULL, pkey))
(lldb) p encrlen
(int) $0 = -12
(lldb)

Regards,

--
Masahiko Sawada
EDB:  https://www.enterprisedb.com/