pgsodium
pgsodium copied to clipboard
Transparent Column Decryption
Hello,
It's not clear to me how to decrypt a column using a view that was encrypted with a security label. Is there any documentation around this? Thanks!
@erikshestopal I'm facing the same issue. Did you find a solution?
@michelp I am also trying to use TCE and cannot decrypt with decrypted_table_name
I tried specifying a security label like SECURITY LABEL FOR pgsodium ON TABLE public.table_name IS 'DECRYPT WITH VIEW public.decrypted_table_name';
and it says pgsodium provider does not support labels on this object
Any documentation I can refer to? Or any pointers to get past this error?
@erikshestopal @masoodshakir I'm running pgsodium 3.0.4 on Supabase. I was having the same issue. The decrypted views are not created automatically (at least for the version I'm using).
I was able to create a decrypted view manually similar to the following:
CREATE TABLE table_name (
id uuid PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),
key_id uuid NOT NULL REFERENCES pgsodium.key(id) DEFAULT (pgsodium.create_key()).id,
nonce bytea NOT NULL DEFAULT pgsodium.crypto_aead_det_noncegen(),
secret_column text NOT NULL DEFAULT 'undefined'::text
)
SECURITY LABEL FOR pgsodium
ON COLUMN table_name.secret_column
IS 'ENCRYPT WITH KEY COLUMN key_id NONCE nonce ASSOCIATED id';
CREATE VIEW decrypted_table_name AS
SELECT id,
convert_from(pgsodium.crypto_aead_det_decrypt(
pg_catalog.decode(secret_column, 'base64'),
pg_catalog.convert_to(id::text, 'utf8'),
key_id::uuid,
nonce
), 'utf8') AS secret_column
FROM table_name;
I'm not sure this is the right way to do it, but it seems to work.
@kadengriffith Thanks for sharing. Super Helpful!
Your solution seems to work. I can access the view from the supabase UI's SQL editor. But, I'm having trouble accessing the view from supabase client. It says: permission denied for view valid_key
Did you specify some permissions for the client?
@masoodshakir Np. Try something like:
GRANT pgsodium_keyiduser TO "postgres";
GRANT USAGE ON SCHEMA pgsodium TO "postgres";
Same problem with no dynamically generated decryption table. Tried using the above solution which successfully created a table but supabase complains about an "invalid cipher key."
Perhaps its a problem on Supabase's side instead of pgsodium?
As per my supabase issue above I completely deleted my project and rebuilt it from scratch and the views were created.
I was looking for view public.decrypted_table_name
, but found only view pgsodium_masks.table_name
with decrypted_column_name
. Not sure if this is right to use it, but it seems to work. Maybe this will help someone...