Open pafiti opened 1 year ago
I have the same issue when dealing with decrypting empty strings. I have modified the trigger to treat empty strings:
CREATE OR REPLACE FUNCTION public.emails_encrypt_secret_summary()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
BEGIN
-- Encrypt the token if it's not null and key_id is not null
NEW.summary := CASE
WHEN NEW.summary IS NULL THEN
NULL
-- When empty string, save empty string
WHEN NEW.summary = '' THEN
''
ELSE
CASE
WHEN NEW.key_id IS NULL THEN
NULL
ELSE
pg_catalog.encode(
pgsodium.crypto_aead_det_encrypt(
pg_catalog.convert_to(NEW.summary, 'utf8'),
pg_catalog.convert_to(('')::text, 'utf8'),
NEW.key_id::uuid,
NULL
),
'base64'
)
END
END;
RETURN NEW;
END;
$function$;
Note that you'd also need to handle the decrypt view:
DROP VIEW IF EXISTS public.decrypted_emails;
CREATE OR REPLACE VIEW public.decrypted_emails AS
SELECT emails.id,
emails.user_id,
emails.body,
emails.summary,
CASE
WHEN emails.summary IS NULL THEN NULL::text
WHEN emails.summary = '' THEN ''
ELSE
CASE
WHEN emails.key_id IS NULL THEN NULL::text
ELSE convert_from(pgsodium.crypto_aead_det_decrypt(decode(emails.summary, 'base64'::text), convert_to(''::text, 'utf8'::name), emails.key_id, NULL::bytea), 'utf8'::name)
END
END AS decrypted_summary,
Or you could just treat empty as null:
CREATE OR REPLACE FUNCTION public.emails_encrypt_secret_summary()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
BEGIN
-- Check if key_id is not already set, else set it to user's key_id
IF NEW.key_id IS NULL THEN
NEW.key_id := (
SELECT key_id
FROM user_preferences
WHERE user_preferences.user_id = NEW.user_id
);
END IF;
-- Encrypt the token if it's not null and key_id is not null
NEW.summary := CASE
WHEN NEW.summary IS NULL OR NEW.summary = '' THEN
NULL
ELSE
CASE
WHEN NEW.key_id IS NULL THEN
NULL
ELSE
pg_catalog.encode(
pgsodium.crypto_aead_det_encrypt(
pg_catalog.convert_to(NEW.summary, 'utf8'),
pg_catalog.convert_to(('')::text, 'utf8'),
NEW.key_id::uuid,
NULL
),
'base64'
)
END
END;
RETURN NEW;
END;
$function$;
I really wonder why in the first place decrypting an empty string is failing. Any idea @michelp?
Hello,
I created a simple table for TCE testing. I got everything created automatically (function, trigger, decrypted view). However, when I insert empty string in the column to be encrypted, the decryption fails.
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' ); select from decrypted_users; insert into users( secret ) values ( '' ); select from decrypted_users;
_SQL Error [22000]: ERROR: pgsodium_crypto_aead_det_decrypt_by_id: invalid message Where: PL/pgSQL function pgsodium.crypto_aead_detdecrypt(bytea,bytea,uuid,bytea) line 12 at RETURN
Regards,