Use security_invoker views for temporary views created in the new schema

[ben-pr-p]

First, thanks for introducing this library - it's great to have, and I'm planning on contributing some extra tooling around it, specifically an integration with Kysely.

I think there's a bug (I haven't verified it, but just believe it's there from reading the code), that the views on the new schema that point to the tables in the old schema are created using the default security setting, which is similar to security definer.

This means that during the transition phase, row level security on the underlying tables will be bypassed by the views created by pgroll.

In order to make sure that accessing the old schema and new schema are the same, create view view_name with (security_invoker = true) as .... should be used.

See this article on the PG 15 release for more info: https://www.depesz.com/2022/03/22/waiting-for-postgresql-15-add-support-for-security-invoker-views/

I think that pgroll should also have some documented limitation on this potential "gotcha" in versions before Postgres 15, and potentially even error if there's no way around it (if there is an RLS policy on a table and the version is 14, which I see is supported).

Thanks again for all your work, super excited for what it means for the ecosystem!

[exekias]

Thank you for bringing this up! I agree we should document better this and other limitations, happy to take a first stab at that.

Adding security_invoker to pgroll views makes sense to me, and should not be too complicated. We will need to retrieve the Postgres version first, as I believe this would be the first conditional clause we add based on it.

Also, really looking forward to knowing more about your plans for Kysely integration, this is great to hear! It seems there is enough interest on integrating ORMs & query builders across the board, and it’s something we want to look into: #166

[andrew-farries]

@ben-pr-p FYI we have a PR up that addresses the issue you raised with security_invoker views: #189.