Thanks for reviewing!
On 2/25/22 19:22, Dean Rasheed wrote:
Re-reading this thread, I think I preferred the name
"security_invoker". The main objection seemed to come from the
potential confusion with SECURITY INVOKER/DEFINER functions, but I
think that's really a different thing. As long as the documentation
for the default behaviour is clear (which I think it was), then it
should be easy to explain how a security invoker view behaves
differently. Also, there's value in using the same terminology as
other databases, because many users will already be familiar with the
feature from those databases.
That is also the main reason I preferred naming it "security_invoker" -
it is consistent with other databases and eases transition from such
systems.
I kept "check_permissions_owner" for now. Constantly changing it around
with each iteration doesn't really bring any value IMHO, I'd rather have
a final consensus on how to name the option and *then* change it for good.
Some other review comments:
1). This new comment:
+ <para>
+ Be aware that <literal>USAGE</literal> privileges on schemas containing
+ the underlying base relations are <emphasis>not</emphasis> checked.
+ </para>
is not entirely accurate. It's more accurate to say that a user
creating or replacing a view must have CREATE privileges on the schema
containing the view and USAGE privileges on any schemas referred to in
the view query, whereas a user using the view only needs USAGE
privileges on the schema containing the view.
(Note that, for the view creator, USAGE is required on any schema
referred to in the query -- e.g., schemas containing functions as well
as base relations.)
Improved in the attached v9.
2). The patch is adding a new field to RangeTblEntry which seems to be
unnecessary -- it's set, and copied around, but never read, so it
should just be removed.
I removed that field in v9 since it is indeed completely unused. I
initially added it to be consistent with the "security_barrier"
implementation and than somewhat forgot about it.
3). Looking at this change:
[..]
I think it should call setRuleCheckAsUser() in all cases. It might be
true that the rule fetched has checkAsUser set to InvalidOid
throughout its action and quals, but it seems unwise to rely on that
-- better to code defensively and explicitly set it in all cases.
It probably doesn't really matter, but I agree that coding defensively
is always a good thing.
Changed that in v9 to call setRuleCheckAsUser() either with ->relowner
or InvalidOid.
4). In the same code block, I think the new behaviour should be
applied to SELECT rules only. The view may have other non-SELECT rules
(just as a table may have non-SELECT rules), created using CREATE
RULE, but their actions are independent of the view definition.
Currently their permissions are checked as the view/table owner, and
if anyone wanted to change that, it should be an option on the rule,
not the view (just as triggers can be made security definer or
invoker, depending on how the trigger function is defined).
Good catch, I added a additional check for rule->event and a test for
that in v9.
[ I also had to add a missing DROP statement to some previous test, just
a heads up. ]
It makes sense to mimic the behavior of triggers and further,
user-created rules otherwise might behave differently for tables and
views, depending on the view definition.
[ But I'm not _that_ familiar with CREATE RULE, FWIW. ]
5). In the same function, the block of code that fetches rules and
triggers has been moved. I think it would be worth adding a comment to
explain why it's now important to extract the reloptions *before*
fetching the relation's rules and triggers.
Added a small comment explaining that in v9.
6). The second set of tests added to rowsecurity.sql seem to have
nothing to do with RLS, and probably belong in updatable_views.sql,
and I think it would be worth adding a few more tests for things like
views on top of views.
Seems reasonable to move them into updatable_views.sql, done that for
v9. Further I added two (simple) tests for chained views as you
mentioned, hope they reflect what you had in mind.
Thanks,
Christoph
From a7e84c92761881419bf8d63ebfcc528417dc8d24 Mon Sep 17 00:00:00 2001
From: Christoph Heiss <christoph.he...@cybertec.at>
Date: Tue, 1 Mar 2022 17:36:42 +0100
Subject: [PATCH v9 1/1] Add new boolean reloption "check_permissions_owner" to
views
When this reloption is set to "false", all permissions on the underlying
relations will be checked against the invoking user rather than the view
owner. The latter remains the default behavior.
Author: Christoph Heiss <christoph.he...@cybertec.at>
Co-Author: Laurenz Albe <laurenz.a...@cybertec.at>
Reviewed-By: Laurenz Albe, Wolfgang Walther
Discussion: https://postgr.es/m/b66dd6d6-ad3e-c6f2-8b90-47be773da240%40cybertec.at
Signed-off-by: Christoph Heiss <christoph.he...@cybertec.at>
---
doc/src/sgml/ref/alter_view.sgml | 13 ++-
doc/src/sgml/ref/create_view.sgml | 72 ++++++++++++++---
src/backend/access/common/reloptions.c | 11 +++
src/backend/rewrite/rewriteHandler.c | 18 +++--
src/backend/utils/cache/relcache.c | 79 ++++++++++++-------
src/include/utils/rel.h | 11 +++
src/test/regress/expected/create_view.out | 73 ++++++++++++++---
src/test/regress/expected/rowsecurity.out | 43 +++++++++-
src/test/regress/expected/rules.out | 20 +++++
src/test/regress/expected/updatable_views.out | 28 +++++++
src/test/regress/sql/create_view.sql | 44 ++++++++++-
src/test/regress/sql/rowsecurity.sql | 26 ++++++
src/test/regress/sql/rules.sql | 23 ++++++
src/test/regress/sql/updatable_views.sql | 27 +++++++
14 files changed, 426 insertions(+), 62 deletions(-)
diff --git a/doc/src/sgml/ref/alter_view.sgml b/doc/src/sgml/ref/alter_view.sgml
index 98c312c5bf..0ea764738a 100644
--- a/doc/src/sgml/ref/alter_view.sgml
+++ b/doc/src/sgml/ref/alter_view.sgml
@@ -156,11 +156,22 @@ ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RESET
<listitem>
<para>
Changes the security-barrier property of the view. The value must
- be Boolean value, such as <literal>true</literal>
+ be a Boolean value, such as <literal>true</literal>
or <literal>false</literal>.
</para>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>check_permissions_owner</literal> (<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ Changes whether permission checks on the underlying relations are
+ performed as the view owner or as the calling user. Default is
+ <literal>true</literal>. The value must be a Boolean value, such as
+ <literal>true</literal> or <literal>false</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist></para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/create_view.sgml b/doc/src/sgml/ref/create_view.sgml
index bf03287592..15655c346c 100644
--- a/doc/src/sgml/ref/create_view.sgml
+++ b/doc/src/sgml/ref/create_view.sgml
@@ -137,8 +137,6 @@ CREATE VIEW [ <replaceable>schema</replaceable> . ] <replaceable>view_name</repl
This parameter may be either <literal>local</literal> or
<literal>cascaded</literal>, and is equivalent to specifying
<literal>WITH [ CASCADED | LOCAL ] CHECK OPTION</literal> (see below).
- This option can be changed on existing views using <link
- linkend="sql-alterview"><command>ALTER VIEW</command></link>.
</para>
</listitem>
</varlistentry>
@@ -152,7 +150,22 @@ CREATE VIEW [ <replaceable>schema</replaceable> . ] <replaceable>view_name</repl
</para>
</listitem>
</varlistentry>
- </variablelist></para>
+
+ <varlistentry>
+ <term><literal>check_permissions_owner</literal> (<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ Set by default. If this option is set to <literal>true</literal>,
+ it will cause all access to underlying tables to be checked as
+ referenced by the view owner, otherwise as the invoking user.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ All of the above options can be changed on existing views using <link
+ linkend="sql-alterview"><command>ALTER VIEW</command></link>.
+ </para>
</listitem>
</varlistentry>
@@ -265,13 +278,39 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
</para>
<para>
- Access to tables referenced in the view is determined by permissions of
- the view owner. In some cases, this can be used to provide secure but
- restricted access to the underlying tables. However, not all views are
- secure against tampering; see <xref linkend="rules-privileges"/> for
- details. Functions called in the view are treated the same as if they had
- been called directly from the query using the view. Therefore the user of
+ By default, access to relations referenced in the view is determined
+ by permissions of the view owner. This can be used to provide secure
+ but restricted access to the underlying tables. However, not all views
+ are secure against tampering; see <xref linkend="rules-privileges"/>
+ for details.
+ </para>
+
+ <para>
+ Functions called in the view are treated the same as if they had been
+ called directly from the query using the view. Therefore, the user of
a view must have permissions to call all functions used by the view.
+ This also means that functions are executed as the invoking user, not
+ the view owner. In particular, <literal>CURRENT_USER</literal>
+ will always return the invoking user.
+ </para>
+
+ <para>
+ If the <literal>check_permissions_owner</literal> property is set to
+ <literal>false</literal> on the view, access to relations referenced in
+ the view is determined by permissions of the invoking user, rather than
+ the view owner. If <link linkend="ddl-rowsecurity">row-level
+ security</link> is enabled on the referenced tables, policies are also
+ invoked for the invoking user. This is useful if you want the view to
+ behave just as if the defining query had been used instead.
+ </para>
+
+ <para>
+ When creating (or replacing) a view, the user must have
+ <literal>CREATE</literal> privileges on the schema containing the view and
+ <literal>USAGE</literal> privileges on any schemas referred to in the view
+ query. The invoking user (which might be different from the view owner if
+ <literal>check_permissions_owner</literal> is used) only needs to have
+ <literal>USAGE</literal> privileges on the schema containing the view.
</para>
<para>
@@ -387,10 +426,17 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
<para>
Note that the user performing the insert, update or delete on the view
must have the corresponding insert, update or delete privilege on the
- view. In addition the view's owner must have the relevant privileges on
- the underlying base relations, but the user performing the update does
- not need any permissions on the underlying base relations (see
- <xref linkend="rules-privileges"/>).
+ view.
+ </para>
+
+ <para>
+ Additionally, by default the view's owner must have the relevant privileges
+ on the underlying base relations, but the user performing the update does
+ not need any permissions on the underlying base relations. (see
+ <xref linkend="rules-privileges"/>) If the view has the
+ <literal>check_permissions_owner</literal> property is set to <literal>false</literal>,
+ the invoking user will need to have the relevant privileges rather than the
+ view owner.
</para>
</refsect2>
</refsect1>
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index d592655258..64d31cd032 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -140,6 +140,15 @@ static relopt_bool boolRelOpts[] =
},
false
},
+ {
+ {
+ "check_permissions_owner",
+ "Privileges on underlying relations are checked as the view owner, not the calling user",
+ RELOPT_KIND_VIEW,
+ AccessExclusiveLock
+ },
+ true
+ },
{
{
"vacuum_truncate",
@@ -1996,6 +2005,8 @@ view_reloptions(Datum reloptions, bool validate)
static const relopt_parse_elt tab[] = {
{"security_barrier", RELOPT_TYPE_BOOL,
offsetof(ViewOptions, security_barrier)},
+ {"check_permissions_owner", RELOPT_TYPE_BOOL,
+ offsetof(ViewOptions, check_permissions_owner)},
{"check_option", RELOPT_TYPE_ENUM,
offsetof(ViewOptions, check_option)}
};
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 3d82138cb3..be8beba436 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -3242,18 +3242,24 @@ rewriteTargetView(Query *parsetree, Relation view)
0);
/*
- * Mark the new target RTE for the permissions checks that we want to
- * enforce against the view owner, as distinct from the query caller. At
- * the relation level, require the same INSERT/UPDATE/DELETE permissions
- * that the query caller needs against the view. We drop the ACL_SELECT
- * bit that is presumably in new_rte->requiredPerms initially.
+ * If the view has "check_permissions_owner" set, mark the new target RTE
+ * for the permissions checks that we want to enforce against the view
+ * owner. Otherwise we want to enforce them against the query caller.
+ *
+ * At the relation level, require the same INSERT/UPDATE/DELETE
+ * permissions that the query caller needs against the view. We drop the
+ * ACL_SELECT bit that is presumably in new_rte->requiredPerms initially.
*
* Note: the original view RTE remains in the query's rangetable list.
* Although it will be unused in the query plan, we need it there so that
* the executor still performs appropriate permissions checks for the
* query caller's use of the view.
*/
- new_rte->checkAsUser = view->rd_rel->relowner;
+ if (RelationSubqueryCheckPermsOwner(view))
+ new_rte->checkAsUser = view->rd_rel->relowner;
+ else
+ new_rte->checkAsUser = view_rte->checkAsUser;
+
new_rte->requiredPerms = view_rte->requiredPerms;
/*
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index fccffce572..d04b92a4e4 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -826,11 +826,19 @@ RelationBuildRuleLock(Relation relation)
pfree(rule_str);
/*
- * We want the rule's table references to be checked as though by the
- * table owner, not the user referencing the rule. Therefore, scan
- * through the rule's actions and set the checkAsUser field on all
- * rtable entries. We have to look at the qual as well, in case it
- * contains sublinks.
+ * If we're dealing with a view that has the "check_permissions_owner"
+ * relopt set to false, we want the rule's table references to be
+ * checked as the user invoking the rule by setting the checkAsUser
+ * field to "InvalidOid".
+ * This also should only apply for SELECT statements, thus check for
+ * that too. The view might have some non-SELECT rules created using
+ * CREATE RULE, but their actions should be independent of the view
+ * definition.
+ *
+ * In all other cases, we want the rule's table references to be
+ * checked as though by the table owner. Therefore, scan through the
+ * rule's actions and set the checkAsUser field on all rtable entries.
+ * We have to look at the qual as well, in case it contains sublinks.
*
* The reason for doing this when the rule is loaded, rather than when
* it is stored, is that otherwise ALTER TABLE OWNER would have to
@@ -838,8 +846,18 @@ RelationBuildRuleLock(Relation relation)
* the rule tree during load is relatively cheap (compared to
* constructing it in the first place), so we do it here.
*/
- setRuleCheckAsUser((Node *) rule->actions, relation->rd_rel->relowner);
- setRuleCheckAsUser(rule->qual, relation->rd_rel->relowner);
+ if (rule->event == CMD_SELECT
+ && relation->rd_rel->relkind == RELKIND_VIEW
+ && !RelationSubqueryCheckPermsOwner(relation))
+ {
+ setRuleCheckAsUser((Node *) rule->actions, InvalidOid);
+ setRuleCheckAsUser(rule->qual, InvalidOid);
+ }
+ else
+ {
+ setRuleCheckAsUser((Node *) rule->actions, relation->rd_rel->relowner);
+ setRuleCheckAsUser(rule->qual, relation->rd_rel->relowner);
+ }
if (numlocks >= maxlocks)
{
@@ -1164,27 +1182,6 @@ retry:
*/
RelationBuildTupleDesc(relation);
- /*
- * Fetch rules and triggers that affect this relation
- */
- if (relation->rd_rel->relhasrules)
- RelationBuildRuleLock(relation);
- else
- {
- relation->rd_rules = NULL;
- relation->rd_rulescxt = NULL;
- }
-
- if (relation->rd_rel->relhastriggers)
- RelationBuildTriggers(relation);
- else
- relation->trigdesc = NULL;
-
- if (relation->rd_rel->relrowsecurity)
- RelationBuildRowSecurity(relation);
- else
- relation->rd_rsdesc = NULL;
-
/* foreign key data is not loaded till asked for */
relation->rd_fkeylist = NIL;
relation->rd_fkeyvalid = false;
@@ -1213,9 +1210,33 @@ retry:
else
Assert(relation->rd_rel->relam == InvalidOid);
- /* extract reloptions if any */
+ /*
+ * Extract reloptions (if any) before fetching rules and triggers.
+ * RelationBuildRuleLock() depends on having ->rd_options already set up.
+ */
RelationParseRelOptions(relation, pg_class_tuple);
+ /*
+ * Fetch rules and triggers that affect this relation
+ */
+ if (relation->rd_rel->relhasrules)
+ RelationBuildRuleLock(relation);
+ else
+ {
+ relation->rd_rules = NULL;
+ relation->rd_rulescxt = NULL;
+ }
+
+ if (relation->rd_rel->relhastriggers)
+ RelationBuildTriggers(relation);
+ else
+ relation->trigdesc = NULL;
+
+ if (relation->rd_rel->relrowsecurity)
+ RelationBuildRowSecurity(relation);
+ else
+ relation->rd_rsdesc = NULL;
+
/*
* initialize the relation lock manager information
*/
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 3b4ab65ae2..c8c8b7ba0b 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -398,6 +398,7 @@ typedef struct ViewOptions
{
int32 vl_len_; /* varlena header (do not touch directly!) */
bool security_barrier;
+ bool check_permissions_owner;
ViewOptCheckOption check_option;
} ViewOptions;
@@ -411,6 +412,16 @@ typedef struct ViewOptions
(relation)->rd_options ? \
((ViewOptions *) (relation)->rd_options)->security_barrier : false)
+/*
+ * RelationSubqueryRunAsOwner
+ * Returns true if the relation has the check_permissions_owner property
+ * set, or not. Note multiple eval of argument!
+ */
+#define RelationSubqueryCheckPermsOwner(relation) \
+ (AssertMacro(relation->rd_rel->relkind == RELKIND_VIEW), \
+ (relation)->rd_options ? \
+ ((ViewOptions *) (relation)->rd_options)->check_permissions_owner : true)
+
/*
* RelationHasCheckOption
* Returns true if the relation is a view defined with either the local
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index ae7c04353c..9813b6973d 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -296,17 +296,31 @@ ERROR: invalid value for boolean option "security_barrier": 100
CREATE VIEW mysecview6 WITH (invalid_option) -- Error
AS SELECT * FROM tbl1 WHERE a < 100;
ERROR: unrecognized parameter "invalid_option"
+CREATE VIEW mysecview7 WITH (check_permissions_owner=false)
+ AS SELECT * FROM tbl1 WHERE a = 100;
+CREATE VIEW mysecview8 WITH (check_permissions_owner=true, security_barrier=true)
+ AS SELECT * FROM tbl1 WHERE a > 100;
+CREATE VIEW mysecview9 WITH (check_permissions_owner)
+ AS SELECT * FROM tbl1 WHERE a < 100;
+CREATE VIEW mysecview10 WITH (check_permissions_owner=100) -- Error
+ AS SELECT * FROM tbl1 WHERE a <> 100;
+ERROR: invalid value for boolean option "check_permissions_owner": 100
SELECT relname, relkind, reloptions FROM pg_class
WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass,
- 'mysecview3'::regclass, 'mysecview4'::regclass)
+ 'mysecview3'::regclass, 'mysecview4'::regclass,
+ 'mysecview7'::regclass, 'mysecview8'::regclass,
+ 'mysecview9'::regclass)
ORDER BY relname;
- relname | relkind | reloptions
-------------+---------+--------------------------
+ relname | relkind | reloptions
+------------+---------+------------------------------------------------------
mysecview1 | v |
mysecview2 | v | {security_barrier=true}
mysecview3 | v | {security_barrier=false}
mysecview4 | v | {security_barrier=true}
-(4 rows)
+ mysecview7 | v | {check_permissions_owner=false}
+ mysecview8 | v | {check_permissions_owner=true,security_barrier=true}
+ mysecview9 | v | {check_permissions_owner=true}
+(7 rows)
CREATE OR REPLACE VIEW mysecview1
AS SELECT * FROM tbl1 WHERE a = 256;
@@ -316,18 +330,50 @@ CREATE OR REPLACE VIEW mysecview3 WITH (security_barrier=true)
AS SELECT * FROM tbl1 WHERE a < 256;
CREATE OR REPLACE VIEW mysecview4 WITH (security_barrier=false)
AS SELECT * FROM tbl1 WHERE a <> 256;
+CREATE OR REPLACE VIEW mysecview7
+ AS SELECT * FROM tbl1 WHERE a > 256;
+CREATE OR REPLACE VIEW mysecview8 WITH (check_permissions_owner=false)
+ AS SELECT * FROM tbl1 WHERE a < 256;
+CREATE OR REPLACE VIEW mysecview9 WITH (check_permissions_owner=true, security_barrier=true)
+ AS SELECT * FROM tbl1 WHERE a <> 256;
SELECT relname, relkind, reloptions FROM pg_class
WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass,
- 'mysecview3'::regclass, 'mysecview4'::regclass)
+ 'mysecview3'::regclass, 'mysecview4'::regclass,
+ 'mysecview7'::regclass, 'mysecview8'::regclass,
+ 'mysecview9'::regclass)
ORDER BY relname;
- relname | relkind | reloptions
-------------+---------+--------------------------
+ relname | relkind | reloptions
+------------+---------+------------------------------------------------------
mysecview1 | v |
mysecview2 | v |
mysecview3 | v | {security_barrier=true}
mysecview4 | v | {security_barrier=false}
-(4 rows)
-
+ mysecview7 | v |
+ mysecview8 | v | {check_permissions_owner=false}
+ mysecview9 | v | {check_permissions_owner=true,security_barrier=true}
+(7 rows)
+
+-- Test chained views when using "check_permissions_owner"
+CREATE TABLE ct1 (x int);
+CREATE VIEW cv1 WITH (check_permissions_owner=true) AS
+SELECT * FROM ct1;
+CREATE VIEW cv2 WITH (check_permissions_owner=false) AS
+SELECT * FROM cv1;
+CREATE ROLE alice NOLOGIN;
+GRANT USAGE ON SCHEMA testviewschm2 TO alice;
+GRANT SELECT ON cv1, cv2 TO alice;
+SET ROLE alice;
+SELECT * FROM cv2;
+ x
+---
+(0 rows)
+
+RESET SESSION AUTHORIZATION;
+REVOKE SELECT ON cv1 FROM alice;
+SET ROLE alice;
+SELECT * FROM cv2;
+ERROR: permission denied for view cv1
+RESET SESSION AUTHORIZATION;
-- Check that unknown literals are converted to "text" in CREATE VIEW,
-- so that we don't end up with unknown-type columns.
CREATE VIEW unspecified_types AS
@@ -2039,7 +2085,7 @@ drop cascades to view aliased_view_2
drop cascades to view aliased_view_3
drop cascades to view aliased_view_4
DROP SCHEMA testviewschm2 CASCADE;
-NOTICE: drop cascades to 74 other objects
+NOTICE: drop cascades to 80 other objects
DETAIL: drop cascades to table t1
drop cascades to view temporal1
drop cascades to view temporal2
@@ -2060,6 +2106,12 @@ drop cascades to view mysecview1
drop cascades to view mysecview2
drop cascades to view mysecview3
drop cascades to view mysecview4
+drop cascades to view mysecview7
+drop cascades to view mysecview8
+drop cascades to view mysecview9
+drop cascades to table ct1
+drop cascades to view cv1
+drop cascades to view cv2
drop cascades to view unspecified_types
drop cascades to table tt1
drop cascades to table tx1
@@ -2114,3 +2166,4 @@ drop cascades to view tt23v
drop cascades to view tt24v
drop cascades to view tt25v
drop cascades to view tt26v
+DROP ROLE alice;
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 89397e41f0..948cb7d3d9 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -8,9 +8,11 @@ DROP USER IF EXISTS regress_rls_alice;
DROP USER IF EXISTS regress_rls_bob;
DROP USER IF EXISTS regress_rls_carol;
DROP USER IF EXISTS regress_rls_dave;
+DROP USER IF EXISTS regress_rls_emily;
DROP USER IF EXISTS regress_rls_exempt_user;
DROP ROLE IF EXISTS regress_rls_group1;
DROP ROLE IF EXISTS regress_rls_group2;
+DROP ROLE IF EXISTS regress_rls_group3;
DROP SCHEMA IF EXISTS regress_rls_schema CASCADE;
RESET client_min_messages;
-- initial setup
@@ -18,11 +20,14 @@ CREATE USER regress_rls_alice NOLOGIN;
CREATE USER regress_rls_bob NOLOGIN;
CREATE USER regress_rls_carol NOLOGIN;
CREATE USER regress_rls_dave NOLOGIN;
+CREATE USER regress_rls_emily NOLOGIN;
CREATE USER regress_rls_exempt_user BYPASSRLS NOLOGIN;
CREATE ROLE regress_rls_group1 NOLOGIN;
CREATE ROLE regress_rls_group2 NOLOGIN;
+CREATE ROLE regress_rls_group3 NOLOGIN;
GRANT regress_rls_group1 TO regress_rls_bob;
GRANT regress_rls_group2 TO regress_rls_carol;
+GRANT regress_rls_group3 TO regress_rls_emily;
CREATE SCHEMA regress_rls_schema;
GRANT ALL ON SCHEMA regress_rls_schema to public;
SET search_path = regress_rls_schema;
@@ -627,6 +632,39 @@ SELECT * FROM category;
44 | manga
(4 rows)
+-- Test views with check_permissions_owner reloption set to false
+RESET SESSION AUTHORIZATION;
+SET row_security TO ON;
+CREATE POLICY p3 ON category FOR ALL TO regress_rls_group3 USING (cname = 'novel');
+CREATE VIEW v1 WITH (check_permissions_owner=false) AS
+SELECT * FROM category;
+CREATE OR REPLACE FUNCTION check_permissions_owner_func() RETURNS SETOF category
+ AS 'SELECT * FROM category'
+ LANGUAGE SQL STABLE STRICT;
+CREATE VIEW v1f WITH (check_permissions_owner=false) AS
+SELECT * FROM check_permissions_owner_func();
+GRANT SELECT ON category TO regress_rls_group3;
+GRANT SELECT ON v1 TO regress_rls_group3;
+GRANT SELECT ON v1f TO regress_rls_group3;
+SET SESSION AUTHORIZATION regress_rls_emily;
+SELECT * FROM category;
+ cid | cname
+-----+-------
+ 11 | novel
+(1 row)
+
+SELECT * FROM v1;
+ cid | cname
+-----+-------
+ 11 | novel
+(1 row)
+
+SELECT * FROM v1f;
+ cid | cname
+-----+-------
+ 11 | novel
+(1 row)
+
--
-- Table inheritance and RLS policy
--
@@ -3987,11 +4025,14 @@ RESET SESSION AUTHORIZATION;
--
RESET SESSION AUTHORIZATION;
DROP SCHEMA regress_rls_schema CASCADE;
-NOTICE: drop cascades to 29 other objects
+NOTICE: drop cascades to 32 other objects
DETAIL: drop cascades to function f_leak(text)
drop cascades to table uaccount
drop cascades to table category
drop cascades to table document
+drop cascades to view v1
+drop cascades to function check_permissions_owner_func()
+drop cascades to view v1f
drop cascades to table part_document
drop cascades to table dependent
drop cascades to table rec1
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index ac468568a1..c9d23613a4 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2823,6 +2823,10 @@ select * from only t1_2;
(10 rows)
reset constraint_exclusion;
+drop table t1 cascade;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to table t1_1
+drop cascades to table t1_2
-- test FOR UPDATE in rules
create table rules_base(f1 int, f2 int);
insert into rules_base values(1,2), (11,12);
@@ -3496,3 +3500,19 @@ SELECT * FROM ruletest2;
DROP TABLE ruletest1;
DROP TABLE ruletest2;
+-- Test rules on views with "check_permissions_owner" set to false.
+CREATE ROLE role1;
+CREATE TABLE t1 (x int);
+CREATE TABLE t2 (x int);
+CREATE VIEW v1 WITH (check_permissions_owner=false) AS
+SELECT * FROM t1;
+GRANT INSERT ON t1, v1 TO role1;
+CREATE RULE testrule1 AS ON INSERT TO v1
+DO INSTEAD INSERT INTO t2 VALUES (NEW.*);
+SET ROLE role1;
+INSERT INTO v1 VALUES (1);
+RESET SESSION AUTHORIZATION;
+DROP VIEW v1;
+DROP TABLE t2;
+DROP TABLE t1;
+DROP ROLE role1;
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index cdff914b93..e2e61fc34d 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -2578,6 +2578,34 @@ DROP VIEW v2;
DROP VIEW v1;
DROP TABLE t2;
DROP TABLE t1;
+-- Check UPDATE/INSERT/DELETE on views with check_permissions_owner set to false
+RESET SESSION AUTHORIZATION;
+CREATE ROLE regress_role1;
+CREATE ROLE regress_role2;
+CREATE TABLE t1 (x int);
+CREATE VIEW v1 WITH (check_permissions_owner=false) AS
+SELECT * FROM t1;
+ALTER VIEW v1 OWNER TO regress_role1;
+GRANT INSERT, UPDATE, DELETE ON t1 TO regress_role1;
+GRANT SELECT ON t1 TO regress_role2;
+GRANT SELECT, INSERT, UPDATE, DELETE ON v1 TO regress_role2;
+SET SESSION AUTHORIZATION regress_role2;
+SELECT * FROM v1;
+ x
+---
+(0 rows)
+
+INSERT INTO v1 values (1);
+ERROR: permission denied for table t1
+UPDATE v1 SET x = 2;
+ERROR: permission denied for table t1
+DELETE FROM v1;
+ERROR: permission denied for table t1
+RESET SESSION AUTHORIZATION;
+DROP VIEW v1;
+DROP TABLE t1;
+DROP ROLE regress_role2;
+DROP ROLE regress_role1;
--
-- Test CREATE OR REPLACE VIEW turning a non-updatable view into an
-- auto-updatable view and adding check options in a single step
diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql
index 829f3ddbe6..cc42f21b12 100644
--- a/src/test/regress/sql/create_view.sql
+++ b/src/test/regress/sql/create_view.sql
@@ -254,9 +254,19 @@ CREATE VIEW mysecview5 WITH (security_barrier=100) -- Error
AS SELECT * FROM tbl1 WHERE a > 100;
CREATE VIEW mysecview6 WITH (invalid_option) -- Error
AS SELECT * FROM tbl1 WHERE a < 100;
+CREATE VIEW mysecview7 WITH (check_permissions_owner=false)
+ AS SELECT * FROM tbl1 WHERE a = 100;
+CREATE VIEW mysecview8 WITH (check_permissions_owner=true, security_barrier=true)
+ AS SELECT * FROM tbl1 WHERE a > 100;
+CREATE VIEW mysecview9 WITH (check_permissions_owner)
+ AS SELECT * FROM tbl1 WHERE a < 100;
+CREATE VIEW mysecview10 WITH (check_permissions_owner=100) -- Error
+ AS SELECT * FROM tbl1 WHERE a <> 100;
SELECT relname, relkind, reloptions FROM pg_class
WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass,
- 'mysecview3'::regclass, 'mysecview4'::regclass)
+ 'mysecview3'::regclass, 'mysecview4'::regclass,
+ 'mysecview7'::regclass, 'mysecview8'::regclass,
+ 'mysecview9'::regclass)
ORDER BY relname;
CREATE OR REPLACE VIEW mysecview1
@@ -267,11 +277,40 @@ CREATE OR REPLACE VIEW mysecview3 WITH (security_barrier=true)
AS SELECT * FROM tbl1 WHERE a < 256;
CREATE OR REPLACE VIEW mysecview4 WITH (security_barrier=false)
AS SELECT * FROM tbl1 WHERE a <> 256;
+CREATE OR REPLACE VIEW mysecview7
+ AS SELECT * FROM tbl1 WHERE a > 256;
+CREATE OR REPLACE VIEW mysecview8 WITH (check_permissions_owner=false)
+ AS SELECT * FROM tbl1 WHERE a < 256;
+CREATE OR REPLACE VIEW mysecview9 WITH (check_permissions_owner=true, security_barrier=true)
+ AS SELECT * FROM tbl1 WHERE a <> 256;
SELECT relname, relkind, reloptions FROM pg_class
WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass,
- 'mysecview3'::regclass, 'mysecview4'::regclass)
+ 'mysecview3'::regclass, 'mysecview4'::regclass,
+ 'mysecview7'::regclass, 'mysecview8'::regclass,
+ 'mysecview9'::regclass)
ORDER BY relname;
+-- Test chained views when using "check_permissions_owner"
+CREATE TABLE ct1 (x int);
+CREATE VIEW cv1 WITH (check_permissions_owner=true) AS
+SELECT * FROM ct1;
+CREATE VIEW cv2 WITH (check_permissions_owner=false) AS
+SELECT * FROM cv1;
+
+CREATE ROLE alice NOLOGIN;
+GRANT USAGE ON SCHEMA testviewschm2 TO alice;
+GRANT SELECT ON cv1, cv2 TO alice;
+
+SET ROLE alice;
+SELECT * FROM cv2;
+
+RESET SESSION AUTHORIZATION;
+REVOKE SELECT ON cv1 FROM alice;
+SET ROLE alice;
+SELECT * FROM cv2;
+
+RESET SESSION AUTHORIZATION;
+
-- Check that unknown literals are converted to "text" in CREATE VIEW,
-- so that we don't end up with unknown-type columns.
@@ -722,3 +761,4 @@ select pg_get_viewdef('tt26v', true);
-- clean up all the random objects we made above
DROP SCHEMA temp_view_test CASCADE;
DROP SCHEMA testviewschm2 CASCADE;
+DROP ROLE alice;
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 44deb42bad..ba91a9be34 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -11,9 +11,11 @@ DROP USER IF EXISTS regress_rls_alice;
DROP USER IF EXISTS regress_rls_bob;
DROP USER IF EXISTS regress_rls_carol;
DROP USER IF EXISTS regress_rls_dave;
+DROP USER IF EXISTS regress_rls_emily;
DROP USER IF EXISTS regress_rls_exempt_user;
DROP ROLE IF EXISTS regress_rls_group1;
DROP ROLE IF EXISTS regress_rls_group2;
+DROP ROLE IF EXISTS regress_rls_group3;
DROP SCHEMA IF EXISTS regress_rls_schema CASCADE;
@@ -24,12 +26,15 @@ CREATE USER regress_rls_alice NOLOGIN;
CREATE USER regress_rls_bob NOLOGIN;
CREATE USER regress_rls_carol NOLOGIN;
CREATE USER regress_rls_dave NOLOGIN;
+CREATE USER regress_rls_emily NOLOGIN;
CREATE USER regress_rls_exempt_user BYPASSRLS NOLOGIN;
CREATE ROLE regress_rls_group1 NOLOGIN;
CREATE ROLE regress_rls_group2 NOLOGIN;
+CREATE ROLE regress_rls_group3 NOLOGIN;
GRANT regress_rls_group1 TO regress_rls_bob;
GRANT regress_rls_group2 TO regress_rls_carol;
+GRANT regress_rls_group3 TO regress_rls_emily;
CREATE SCHEMA regress_rls_schema;
GRANT ALL ON SCHEMA regress_rls_schema to public;
@@ -225,6 +230,27 @@ SET row_security TO OFF;
SELECT * FROM document;
SELECT * FROM category;
+-- Test views with check_permissions_owner reloption set to false
+RESET SESSION AUTHORIZATION;
+SET row_security TO ON;
+CREATE POLICY p3 ON category FOR ALL TO regress_rls_group3 USING (cname = 'novel');
+CREATE VIEW v1 WITH (check_permissions_owner=false) AS
+SELECT * FROM category;
+CREATE OR REPLACE FUNCTION check_permissions_owner_func() RETURNS SETOF category
+ AS 'SELECT * FROM category'
+ LANGUAGE SQL STABLE STRICT;
+CREATE VIEW v1f WITH (check_permissions_owner=false) AS
+SELECT * FROM check_permissions_owner_func();
+
+GRANT SELECT ON category TO regress_rls_group3;
+GRANT SELECT ON v1 TO regress_rls_group3;
+GRANT SELECT ON v1f TO regress_rls_group3;
+
+SET SESSION AUTHORIZATION regress_rls_emily;
+SELECT * FROM category;
+SELECT * FROM v1;
+SELECT * FROM v1f;
+
--
-- Table inheritance and RLS policy
--
diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql
index 8bdab6dec3..782a2a30af 100644
--- a/src/test/regress/sql/rules.sql
+++ b/src/test/regress/sql/rules.sql
@@ -991,6 +991,7 @@ select * from only t1_1;
select * from only t1_2;
reset constraint_exclusion;
+drop table t1 cascade;
-- test FOR UPDATE in rules
@@ -1257,3 +1258,25 @@ SELECT * FROM ruletest2;
DROP TABLE ruletest1;
DROP TABLE ruletest2;
+
+-- Test rules on views with "check_permissions_owner" set to false.
+CREATE ROLE role1;
+
+CREATE TABLE t1 (x int);
+CREATE TABLE t2 (x int);
+CREATE VIEW v1 WITH (check_permissions_owner=false) AS
+SELECT * FROM t1;
+GRANT INSERT ON t1, v1 TO role1;
+
+CREATE RULE testrule1 AS ON INSERT TO v1
+DO INSTEAD INSERT INTO t2 VALUES (NEW.*);
+
+SET ROLE role1;
+
+INSERT INTO v1 VALUES (1);
+
+RESET SESSION AUTHORIZATION;
+DROP VIEW v1;
+DROP TABLE t2;
+DROP TABLE t1;
+DROP ROLE role1;
diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql
index 09328e582b..45ff1a6e48 100644
--- a/src/test/regress/sql/updatable_views.sql
+++ b/src/test/regress/sql/updatable_views.sql
@@ -1226,6 +1226,33 @@ DROP VIEW v1;
DROP TABLE t2;
DROP TABLE t1;
+-- Check UPDATE/INSERT/DELETE on views with check_permissions_owner set to false
+RESET SESSION AUTHORIZATION;
+CREATE ROLE regress_role1;
+CREATE ROLE regress_role2;
+
+CREATE TABLE t1 (x int);
+CREATE VIEW v1 WITH (check_permissions_owner=false) AS
+SELECT * FROM t1;
+ALTER VIEW v1 OWNER TO regress_role1;
+
+GRANT INSERT, UPDATE, DELETE ON t1 TO regress_role1;
+GRANT SELECT ON t1 TO regress_role2;
+GRANT SELECT, INSERT, UPDATE, DELETE ON v1 TO regress_role2;
+
+SET SESSION AUTHORIZATION regress_role2;
+
+SELECT * FROM v1;
+INSERT INTO v1 values (1);
+UPDATE v1 SET x = 2;
+DELETE FROM v1;
+
+RESET SESSION AUTHORIZATION;
+DROP VIEW v1;
+DROP TABLE t1;
+DROP ROLE regress_role2;
+DROP ROLE regress_role1;
+
--
-- Test CREATE OR REPLACE VIEW turning a non-updatable view into an
-- auto-updatable view and adding check options in a single step
--
2.35.1
