TCE : Update with using Old Value

[pafiti]

Hello,

Looks like table gets corrupted when I'm trying to update an encrypted column using this column. CREATE TABLE users ( id bigserial primary key, secret text, key_id uuid not null default 'e3496f2a-787f-45a0-9717-f648496179d1', nonce bytea default pgsodium.crypto_aead_det_noncegen() );

SECURITY LABEL FOR pgsodium ON COLUMN users.secret IS 'ENCRYPT WITH KEY COLUMN key_id NONCE nonce';

insert into users( secret ) values ( '12345' );

For now, everything is right Then update users set secret = secret || 'test' where id = 1;

At this stage, my secret has been corrupted and is not accessible anymore... Do I miss anything ?

Thanks

[ioguix]

Hi,

update users set secret = secret || 'test' where id = 1;

This is normal, you concatenate the encrypted value secret with the string test. so the trigger is actually encrypting the old encrypted value concatenated with test. See:

test=# select secret from users ;
 hk39ZUVWwot9fS74go8uICXaRjwePlg8rugd5qPmn5cde/S77Q==

test=# select decrypted_secret from decrypted_users ;
 12345

test=# update users set secret = secret || 'test'  where id = 1;
UPDATE 1
test=# select decrypted_secret from decrypted_users ;
 hk39ZUVWwot9fS74go8uICXaRjwePlg8rugd5qPmn5cde/S77Q==test  -- <== old encrypted value + "test"

You might want to do:

test=# insert into users( id, secret ) values ( 2, '12345' );
INSERT 0 1

test=# update decrypted_users set secret = decrypted_secret||'test' where id = 2;
UPDATE 1

test=# select secret, decrypted_secret from decrypted_users where id=2;
 qGHgsza6lmQ6Z5VFqHuC4FhojqNaONdU3cRwVdkJAc7VL8a3d8G15SE= | 12345test