Row level security and views, how to allow different rows to different users?

[SvanteRichter]

Environment

• PostgreSQL version: 12.5
• PostgREST version: 7.0.1
• Operating system: Ubuntu 20.04

Description of issue

Documentation seems to be missing for how to properly use row level security with views. Since PostgREST recommends RLS to handle authorization to different rows within the DB and views to handle schema isolation this seems important.

My problem is that I have tables that need RLS to allow different users access to different rows, and views that expose those within my PostgREST schema, but the RLS rules always gets the view owners current_user. The docs say

"Views are invoked with the privileges of the view owner, much like stored procedures with the SECURITY DEFINER option. When created by a SUPERUSER role, all row-level security will be bypassed unless a different, non-SUPERUSER owner is specified. For changing this, we can create a non-SUPERUSER role and make this role the view’s owner."

But that simply means that the RLS rule is not bypassed, not that it actually gets calculated based on the role that PostgREST has assumed based on the bearer token.

Am I missing something here or is there no way to use RLS with views to expose different rows to different users via PostgREST using current_user (which I use because that seems like what all examples use)? Is the idea to use current_setting('request.jwt.claim.role') instead of current_user? Or is it simply recommended to use tables directly if one uses RLS?

[ruslantalpa]

have a separate role like this
https://github.com/subzerocloud/postgrest-starter-kit/blob/master/db/src/api/schema.sql#L5-L9

Use that role as a view owner like this https://github.com/subzerocloud/postgrest-starter-kit/blob/master/db/src/api/todos.sql#L10

Attache policies to that role (not the one from jwt) and inside the policy check the "role" like this
https://github.com/subzerocloud/postgrest-starter-kit/blob/master/db/src/authorization/privileges.sql#L20-L35

[SvanteRichter]

Ah, thanks! I'm guessing request is a variable set by postgrest at the start of the session? Is there any way to make this compatible both for users logging in to postgreSQL directly and via postgrest?

[SvanteRichter]

Ah, I see now, request is defined here: https://github.com/subzerocloud/postgrest-starter-kit/blob/master/db/src/libs/request.sql so request.user_role() is essentially equivalent to current_setting('request.jwt.claim.role', true). Thanks!

[ruslantalpa]

No, request.xxx is a function that reads CUGs set by postgrest, look for the definition in that repo. Compatibility... maybe, haven’t thought about it.

…

On 3 Feb 2021, at 14:26, SahAssar ***@***.***> wrote:  Ah, I see now, request is defined here: https://github.com/subzerocloud/postgrest-starter-kit/blob/master/db/src/libs/request.sql so request.user_role() is essentially equivalent to current_setting('request.jwt.claim.role'). Thanks! — You are receiving this because you commented. Reply to this email directly, view it on GitHub, or unsubscribe.

[wolfgangwalther]

Am I missing something here or is there no way to use RLS with views to expose different rows to different users via PostgREST using current_user (which I use because that seems like what all examples use)?

No, you're not missing anything - that's true. If PostgreSQL were to support either RLS directly on views or views with SECURITY INVOKER (or ideally both) that would improve the situation a lot.

Is the idea to use current_setting('request.jwt.claim.role') instead of current_user?

Yes, that's the only real solution right now. Either directly or via helper function as mentioned by Ruslan - doesn't matter much. Still feels a bit like a hack, tbh.

Or is it simply recommended to use tables directly if one uses RLS?

No, the benefits of schema isolation still outweigh. Also, if PostgreSQL were to support any of the two mentioned features in the future, you could upgrade your view-based schema easily.