pgsodium/auth with Row Level Security and One Key per User

[henningko]

Hi Supabase,

I'm currently using pgsodium for transparent column encryption and I want to combine it with row-level security based based on the user_id. I have a table called secrets with the following schema:

CREATE TABLE "public"."secrets" (
    "id" uuid NOT NULL DEFAULT gen_random_uuid(),
    "user_id" uuid NOT NULL,
    "secret" text,
    CONSTRAINT "emails_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id"),
    PRIMARY KEY ("id")
);

I've enabled row-level security on this table like this:

alter table secrets enable row level security;
create policy "Individuals can create secrets." on secrets for
    insert with check (auth.uid() = user_id);
create policy "Individuals can view their own secrets. " on secrets for
    select using (auth.uid() = user_id);
create policy "Individuals can update their own secrets." on secrets for
    update using (auth.uid() = user_id);
create policy "Individuals can delete their own secrets." on secrets for
    delete using (auth.uid() = user_id);

Now, I want to enable transparent column encryption on the secret column using pgsodium, but I would like the encryption to be done with a user's unique key. The corresponding key_id should be specific to each user, and I want to store it in another table (e.g., user_preferences).

Is the best approach to have a separate function such as create_key_for_user(user_id), that is then referenced like this?

key_id uuid REFERENCES pgsodium.key(id) DEFAULT (create_key_for_user(user_id))

(I don't have access to Vault at the moment)

Appreciate some guidance—thanks!

P.S. This is a duplicate of michelp/pgsodium#79, not sure the discussions there have a lot of visibility.

[sigma-andex]

I got that running, but I had to manually enable the security_invoker on the generated view.

ALTER VIEW public.decrypted_mydata SET (security_invoker = on);

I guess this should actually be enabled by default, because whats the sense to encrypt data if everyone can access the view?

[henningko]

How do you set the key_id when ingesting a new secret? Do you retrieve it first and then pass it alongside the other ingest values?

[sigma-andex]

Yes exactly. So in my case I create a new key for the user using a trigger when the user registers. Then when creating a new secret I first get the key id and then I insert it along the other values. In plain SQL this could probably be done via a insert ... (select ... from ...), though I haven't figured out how to do it with supabase.js. In any case, this works for me now.

[henningko]

@sigma-andex I am now using a modified trigger to achieve this, so I don't have to first select the key, see here:
supabase/cli#1024 (comment)

[henningko]

Finally achieved this (note that I'm also updating an existing table with column token to now be encrypted, and that I have to recreate the triggers/views due to supabase/cli#1024:

-- Alter table user_preferences to hold a key_id (uuid) that references pgsodium.key.id
ALTER TABLE public.user_preferences ADD COLUMN key_id uuid REFERENCES pgsodium.key(id) DEFAULT (pgsodium.create_key()).id UNIQUE;

UPDATE "public"."user_preferences"
SET "key_id" = (pgsodium.create_key()).id;

-- -- Add columns key_id (uuid) to table user_tokens
ALTER TABLE public.user_tokens ADD COLUMN key_id uuid REFERENCES user_preferences(key_id);

-- -- Add security label that encrypts 
SECURITY LABEL FOR pgsodium ON COLUMN user_tokens.token IS 'ENCRYPT WITH KEY COLUMN key_id';

CREATE OR REPLACE FUNCTION public.user_tokens_encrypt_secret_token()
 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.token := CASE 
        WHEN NEW.token IS NULL 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.token, 'utf8'),
                            pg_catalog.convert_to(('')::text, 'utf8'),
                            NEW.key_id::uuid,
                            NULL
                        ),
                        'base64'
                    ) 
            END 
    END;
  
    RETURN NEW;
END;
$function$;

CREATE OR REPLACE TRIGGER user_tokens_encrypt_secret_trigger_token 
BEFORE INSERT OR UPDATE OF token ON "public"."user_tokens"
FOR EACH ROW EXECUTE FUNCTION user_tokens_encrypt_secret_token();

-- -- Set token to trigger encryption
UPDATE user_tokens SET token = token;

-- -- -- Create View of decrypted data
CREATE OR REPLACE VIEW public.decrypted_user_tokens AS
 SELECT user_tokens.id,
    user_tokens.created_at,
    user_tokens.user_id,
    user_tokens.token,
        CASE
            WHEN user_tokens.token IS NULL THEN NULL::text
            ELSE
            CASE
                WHEN user_tokens.key_id IS NULL THEN NULL::text
                ELSE convert_from(pgsodium.crypto_aead_det_decrypt(decode(user_tokens.token, 'base64'::text), convert_to(''::text, 'utf8'::name), user_tokens.key_id, NULL::bytea), 'utf8'::name)
            END
        END AS decrypted_token,
    user_tokens.revoked_at,
    user_tokens.scope,
    user_tokens.email,
    user_tokens.key_id
   FROM user_tokens;