How to set security_invoker automatically? about pgsodium HOT 4 OPEN

@ianschmitz By the way, it is no longer a documentation issue for this repo. I've just seen that about 20 days after you created this issue, the Readme file was updated to include information about how to set the security invoker:

Any TCE security label can be appended with the string SECURITY INVOKER which will cause the automatically generated view to be marked security_invoker=true.

from pgsodium.

hey @ianschmitz,

I know this issue is old, but it has been on my mind for a while because it was really surprising to me that this was the default behavior and there was no way to specify the security invoker.

As a workaround, I would typically patch the generated view like this:

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

Of course, that was far from ideal.

Today, I found this PR, and I was wondering whether setting SECURITY INVOKER at the time of applying the security label would generate a decrypted_my_original_table view with the security invoker on.

And according to my tests, it does indeed.

Note: I tested it using DBeaver's DDL generation over the view, as other tools such as Supabase Admin Studio never show the security invoker part in a view DDL, regardless of whether it has it or not.

from pgsodium.

@fabricior interesting. Are you thinking this is more of a documentation issue? For context i'm coming from pgsodium usage within Supabase.

from pgsodium.

@fabricior interesting. Are you thinking this is more of a documentation issue? For context i'm coming from pgsodium usage within Supabase.

@ianschmitz yes, same here. I read about all this in a supabase blog post IIRC. I was thinking of creating an issue/pr if it is not fixed already

from pgsodium.