pgsodium with Row Level Security and One Key per User

[henningko]

Hey,
Love the library's ease of use, but struggling to adapt it to work with RLS. I'm currently using pgsodium for transparent column encryption in a PostgreSQL database and I want to combine it with row-level security 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))

Would love some guidance on how to best achieve this.

[sigma-andex]

As described here I got this running enabling the security_invoker. @michelp shouldn't this be enabled by default on the generated decrypted view?