Transparent Column Decryption

[erikshestopal]

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!

[masoodshakir]

@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?

[kadengriffith]

@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.

[masoodshakir]

@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?

[kadengriffith]

@masoodshakir Np. Try something like:

GRANT pgsodium_keyiduser TO "postgres";
GRANT USAGE ON SCHEMA pgsodium TO "postgres";

[scalemaildev]

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?

[scalemaildev]

As per my supabase issue above I completely deleted my project and rebuilt it from scratch and the views were created.

[pompep]

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...