-
Notifications
You must be signed in to change notification settings - Fork 32
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Transparent Column Encryption with Empty string #60
Comments
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? |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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_det_decrypt(bytea,bytea,uuid,bytea) line 12 at RETURN
Regards,
The text was updated successfully, but these errors were encountered: