Transparent Column Encryption does not work (permission/ownership issue)

[jstdk]

Bug report

Describe the bug

I followed the Supabase tutorial: https://supabase.com/blog/transparent-column-encryption-with-postgres to encrypt the password field in the email table. I tried this:

CREATE TABLE emails (
id bigserial primary key,
email text,
password text,
key_id uuid not null DEFAULT 'e348034b-3f07-4878-aad6-000511d12826'::uuid,
nonce bytea default pgsodium.crypto_aead_det_noncegen()
);

This succeeds, then I move on to:

SECURITY LABEL FOR pgsodium
ON COLUMN emails.password
IS 'ENCRYPT WITH KEY COLUMN key_id NONCE nonce';

Error: Failed to run sql query: must be owner of event trigger pgsodium_trg_mask_update

Then I tried to Google and change the owner as suggested by other users and tutorials. For example https://www.postgresql.org/docs/current/sql-altereventtrigger.html

Problem is that I cannot find this trigger in Triggers. I tried:

ALTER EVENT TRIGGER pgsodium_trg_mask_update OWNER to postgres

Error: Failed to run sql query: must be owner of event trigger pgsodium_trg_mask_update

I am out of options, How did the writer of the tutorial make this work?

To Reproduce

Follow https://supabase.com/blog/transparent-column-encryption-with-postgres

Expected behavior

Password column encrypted

Screenshots

See code examples above

System information

Linux (Fedora 37) + Supabase Dashboard in Chromium

[james2doyle]

Having the same issue. Also tried to use an RPC call and that also failed

[james2doyle]

You need to grant the user the permissions to use the function:

GRANT EXECUTE ON FUNCTION pgsodium.crypto_aead_det_decrypt (bytea, bytea, uuid, bytea) TO authenticated;

[jstdk]

Thanks. Unfortunately, I keep getting this error: Failed to run sql query: must be owner of event trigger pgsodium_trg_mask_update

[james2doyle]

You're running it on the supabase SQL editor? Are you using self hosted or platform? I ran it on a client connected as the postgres user on platform and it worked fine for me. My function and my view that had that error both worked after

…

On Tue, Mar 14, 2023, 9:46 p.m. Joost ***@***.***> wrote: Thanks. Unfortunately, I keep getting this error: Failed to run sql query: must be owner of event trigger pgsodium_trg_mask_update — Reply to this email directly, view it on GitHub <#12952 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAK37GAKCMH6RQGKVHUBDLTW4FCTFANCNFSM6AAAAAAVXGFZZE> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[jstdk]

I ran it in the SQL editor indeed. Will try as client. Thanks for the extra info

[radulescuandrew]

I have the same issue while trying to alter a table on a recently restored project.

must be owner of event trigger pgsodium_trg_mask_update

I tried the above but it is not working. Any other guidance?

[gregnr]

cc @michelp in case you have any insight.

[michelp]

Hi @radulescuandrew , the issue is that in an older version of our software stack, there was a dump/restore bug that introduced this ownership issue. If you start through the same tutorial with a fresh project, it will work properly.

Fixing it for an existing database depends on if you have encrypted data you want to preserve. If you do, you'll have to temporarily copy or dump that data to the side so you can reinser it. then disabling and then renabling the extensions will initialize them with the correct privledges:

drop view vault.decrypted_secrets;
drop extension supabase_vault;
drop extension pgsodium;
create extension pgsodium;
create extension supabase_vault;

[jstdk]

That explains why it did not even work with the new toggle in the edit column page in my existing project

[SamMakesThings]

Note to future people: Double check the blocked function name in your logs. It turned out that I had to allow access to crypto_aead_det_encrypt, not just crypto_aead_det_decrypt.

Full command:
GRANT EXECUTE ON FUNCTION pgsodium.crypto_aead_det_encrypt (bytea, bytea, uuid, bytea) TO authenticated;

[jacktho]

I was able to fix the lack of permissions for crypto_aead_det_encrypt and crypto_aead_det_decrypt with GRANT pgsodium_keyiduser TO authenticated;

As mentioned here:
https://supabase.com/docs/guides/database/column-encryption#granting-api-access-to-encrypted-columns

[jstdk]

Finally implemented it today for all databases and nuked out various crypto npm packages. What a relieve. It works great without any SQL code needed (only the one-line by @SamMakesThings ), except for querying a view instead of the tables directly.

Thanks everyone, this solution is what I was hoping for. Will close the ticket.