Updates to pgsodium encrypted values don't use specified key_id

14 views Asked by At

When I use pgsodium with a unique key_id for each row, encryption works well when I add a new record, and I can see the decrypted value in the associated view:

CREATE EXTENSION IF NOT EXISTS pgsodium;
create table if not exists "schema"."secret" (
    "id" uuid DEFAULT uuid_generate_v4() NOT NULL PRIMARY KEY,
    "createdAt" timestamp with time zone DEFAULT "now"() NOT NULL,
    "updatedAt" timestamp with time zone DEFAULT "now"() NOT NULL,
    "lastUsed" timestamp with time zone,
    "name" text DEFAULT ''::text not null,
    "value" text DEFAULT ''::text not null,
    "key_id" uuid NOT NULL references pgsodium.key(id) default (pgsodium.create_key()).id,
    "nonce" bytea DEFAULT pgsodium.crypto_aead_det_noncegen(),
    "userId" uuid DEFAULT "auth"."uid"() references "auth"."users"("id")
);
SECURITY LABEL FOR pgsodium 
ON COLUMN "schema"."secret"."value"
IS 'ENCRYPT WITH KEY COLUMN key_id ASSOCIATED (userId) NONCE nonce';

However, once I update a row in the 'secret' table, a NEW key is created in the pgsodium schema to encrypt the value, which is not subsequently updated in the key_id column. So after updating a record, I can no longer access its decrypted value from the associated view.

I tried all the different ways to assign keys (per column, per row, with and without nonce) but it always assigns a fresh key to encrypt the updated row.

Why this is happening? I thought pgsodium is supposed to use the specified key_id.

I think it is a bug with some predefined triggers on ON UPDATE, but I'm at a loss on where to find / overwrite them.

0

There are 0 answers