Clarify issues of view usage with row level security

[colearendt]

Environment

• PostgreSQL version: 12
• PostgREST version: Any
• Operating system:

Description of issue

Postgrest docs articulate very clearly (thank you!) the:

• benefits of view usage for API versioning
• benefits and implementation of row level security

However, where it is not clear is the current PostgreSQL limitation on using views with "security invoker." Rather, views are "security definers" and run as the creator of the view. This means that you cannot use views with row level security (in a natural sense) today.

(Expected behavior vs actual behavior)

It would be helpful as a user if this was explained more clearly in the docs, ideally with links to the development of this feature within Postgres itself for tracking. Similarly, it would be good to highlight the importance of making stored procedures "security invokers" when using row level security.

Alternatively, it would be possible to document an alternative row level security policy (like this one) that circumvents the restriction that views do not support today.

In any case, I think the ability to make views into "security invokers" would be a huge boon for postgrest, as having to choose between RLS and very nicely versioned apis based on views is an unfortunate dichotomy. Any ability to push for this feature upstream within postgres would be great!

(Steps to reproduce: Include a minimal SQL definition plus how you make the request to PostgREST and the response body)

N/A

[ruslantalpa]

There is no such dichotomy, you can use both, the only "inconvenience" when using views with RLS is that you have to attach the policy to the view owner (which should not be a superuser) but a distinct/normal role like so https://github.com/subzerocloud/postgrest-starter-kit/blob/master/db/src/authorization/privileges.sql#L21-L35

[wolfgangwalther]

the only "inconvenience" when using views with RLS

There's certainly more inconveniences, mostly in a "roles for each web user" setup:

• RLS can't use CURRENT_USER, but would have to fall back to the postgrest jwt GUCs. Those policies will then only work with PostgREST and not through SQL directly (or worse: can easily be avoided by direct SQL access).
• RLS can be applied to certain roles only (CREATE POLICY ... TO user ...) - but this won't work with views.
• RLS can't be created on views. This would sometimes be useful. And no: security_barrier with WITH CHECK OPTION views will not do it - RLS are a lot more fine-grained.

If PostgreSQL supported SECURITY INVOKER on views and adding RLS directly on views, that would be super helpful.

It would be helpful as a user if this was explained more clearly in the docs...

I agree that we should probably point out the fact that Roles for Each Web User and Schema Isolation don't work together nicely.

This issue is mostly a duplicate of https://github.com/PostgREST/postgrest/discussions/1750 - but since it specifically asks for a docs improvement, I will move it to the postgrest-docs repo to track there.