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 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.
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:
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.
Is this a bug?