How to set security_invoker automatically?

[ianschmitz]

Currently every time we use:

SECURITY LABEL FOR pgsodium ON COLUMN ...

it generates/replaces a decrypted view. This view does not have security_invoker = on by default.

We can use a separate command to enable it:

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

However if we were to enable encryption on another column from the same table, this would remove security_invoker since the view is replaced.

I'm not really sure what the right solution is here, but i did find it a bit surprising and clearly since security_invoker is such a critical configuration property of a view to enable RLS, having it removed is a big security issue.

Should security_invoker be enabled by default on supported Postgres versions? Should it remain on the newly generated view if it existed on the previous version? Should we be able to specify it when calling SECURITY LABEL?

Thanks for your consideration!

[fabricior]

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.

[ianschmitz]

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

[fabricior]

@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

[fabricior]

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