Re: [PATCH] Add reloption for views to enable RLS

On Fri, 2021-12-17 at 18:31 +0100, Christoph Heiss wrote:
> As part of a customer project we are looking to implement an reloption
> for views which when set, runs the subquery as invoked by the user
> rather than the view owner, as is currently the case.
> The rewrite rule's table references are then checked as if the user were
> referencing the table(s) directly.
>
> This feature is similar to so-called 'SECURITY INVOKER' views in other DBMS.
> Although such permission checking could be implemented using views which
> SELECT from a table function and further using triggers, that approach
> has obvious performance downsides.

This has been requested before, see for example
https://stackoverflow.com/q/33858030/6464308

Row Level Security is only one use case; there may be other situations
when it is useful to check permissions on the underlying objects with
the current user rather than with the view owner.

> Our initial thought on implementing this was to simply add another
> reloption for views, just like the already existing `security_barrier`.
> With this in place, we then can conditionally evaluate in
> RelationBuildRuleLock() if we need to call setRuleCheckAsUser() or not.
> The new reloption has been named `security`, which is an enum currently
> only supporting a single value: `relation_permissions`.

You made that an enum with only a single value.
What other values could you imagine in the future?

I think that this should be a boolean reloption, for example "security_definer".
If unset or set to "off", you would get the current behavior.

> Finally, patch 0003 updates the documentation for this new reloption.

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 64d9030652..760ea2f794 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -2292,6 +2292,10 @@ GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;
are not subject to row security.
</para>

+ <para>
+ For views, the policies are applied as being referenced through the view owner by default, rather than the user referencing the view. To apply row security policies as defined for the invoking
user, the <firstterm>security</firstterm> option can be set on views (see <link linkend="sql-createview">CREATE VIEW</link>) to get the same behavior.
+ </para>
+
<para>
Row security policies can be specific to commands, or to roles, or to
both. A policy can be specified to apply to <literal>ALL</literal>

Please avoid long lines like that. Also, I don't think that the documentation on
RLS policies is the correct place for this. It should be on a page dedicated to views
or permissions.

The CREATE VIEW page already has a paragraph about this, starting with
"Access to tables referenced in the view is determined by permissions of the view owner."
This looks like the best place to me (and it would need to be adapted anyway).

Yours,
Laurenz Albe