Hi,
On 2/15/22 09:37, walt...@technowledgy.de wrote:
Christoph Heiss:
xxx_owner=true would be the default and xxx_owner=false could be set
explicitly to get the behavior we are looking for in this patch?
I'm not sure if an option which is on by default would be best, IMHO.
I would rather have an off-by-default option, so that you explicitly
have to turn *on* that behavior rather than turning *off* the current.
Just out of curiosity I asked myself whether there were any other
boolean options that default to true in postgres - and there are plenty.
./configure options, client connection settings, server config options,
etc - but also some SQL statements:
- CREATE USER defaults to LOGIN
- CREATE ROLE defaults to INHERIT
- CREATE COLLATION defaults to DETERMINISTIC=true
There's even reloptions, that do, e.g. vacuum_truncate.
Knowing that I happily drop my objection about that. :^)
[..] The more I think about it, the more it becomes clear that
really the current default behavior of "running the query as the view
owner" is the special thing here, not the behavior you are introducing.
If we were to start from scratch, it would be pretty obvious - to me -
that run_as_owner=false would be the default, and the run_as_owner=true
would need to be turned on explicitly. I'm thinking about "run_as_owner"
as the better design and "defaults to true" as a backwards compatibility
thing.
Right, if we treat that as a kind of "backwards-compatible" feature,
having an reloption that is on by default makes sense.
I converted the option to run_as_owner=true|false in the attached v7.
It now definitely seems like the right way to move forward and getting
more feedback.
Thanks,
Christoph Heiss
From 27b3c425bc000d32253a6c33057c75dfb2deb6ef Mon Sep 17 00:00:00 2001
From: Christoph Heiss <christoph.he...@cybertec.at>
Date: Tue, 15 Feb 2022 12:25:46 +0100
Subject: [PATCH v7 1/1] Add new boolean reloption "security_invoker" to views
When this reloption is set to true, all permissions on the underlying
objects will be checked against the invoking user rather than the view
owner, as is currently implemented.
Signed-off-by: Christoph Heiss <christoph.he...@cybertec.at>
Co-Author: Laurenz Albe <laurenz.a...@cybertec.at>
Discussion: https://postgr.es/m/b66dd6d6-ad3e-c6f2-8b90-47be773da240%40cybertec.at
---
doc/src/sgml/ref/alter_view.sgml | 12 +++-
doc/src/sgml/ref/create_view.sgml | 74 ++++++++++++++++++-----
src/backend/access/common/reloptions.c | 11 ++++
src/backend/nodes/copyfuncs.c | 1 +
src/backend/nodes/equalfuncs.c | 1 +
src/backend/nodes/outfuncs.c | 1 +
src/backend/nodes/readfuncs.c | 1 +
src/backend/optimizer/plan/subselect.c | 3 +
src/backend/optimizer/prep/prepjointree.c | 2 +
src/backend/rewrite/rewriteHandler.c | 19 ++++--
src/backend/utils/cache/relcache.c | 63 ++++++++++---------
src/include/nodes/parsenodes.h | 1 +
src/include/utils/rel.h | 11 ++++
src/test/regress/expected/create_view.out | 46 +++++++++++---
src/test/regress/expected/rowsecurity.out | 65 +++++++++++++++++++-
src/test/regress/sql/create_view.sql | 22 ++++++-
src/test/regress/sql/rowsecurity.sql | 44 ++++++++++++++
17 files changed, 316 insertions(+), 61 deletions(-)
diff --git a/doc/src/sgml/ref/alter_view.sgml b/doc/src/sgml/ref/alter_view.sgml
index 98c312c5bf..bc6184bfea 100644
--- a/doc/src/sgml/ref/alter_view.sgml
+++ b/doc/src/sgml/ref/alter_view.sgml
@@ -156,11 +156,21 @@ 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>run_as_owner</literal> (<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ Changes the user as which the subquery is run. 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..c34df4e746 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>run_as_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
- a view must have permissions to call all functions used by the view.
+ By default, access to tables and functions 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 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.
+ </para>
+
+ <para>
+ However, when using chained views, the <literal>CURRENT_USER</literal> user
+ will always stay the invoking user, regardless of whether the query is run
+ as the view owner (the default) or the invoking user (when
+ <literal>run_as_owner</literal> is set to <literal>false</literal>)
+ and the depth of the current invocation.
+ </para>
+
+ <para>
+ If the <literal>run_as_owner</literal> property is set to
+ <literal>false</literal> on the view, access to tables and functions
+ 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>
+ Be aware that <literal>USAGE</literal> privileges on schemas are not checked
+ when referencing the underlying base relations, even if they are part of a
+ different schema.
</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>run_as_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..ca0461f52a 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -140,6 +140,15 @@ static relopt_bool boolRelOpts[] =
},
false
},
+ {
+ {
+ "run_as_owner",
+ "Privileges on underlying relations and functions 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)},
+ {"run_as_owner", RELOPT_TYPE_BOOL,
+ offsetof(ViewOptions, run_as_owner)},
{"check_option", RELOPT_TYPE_ENUM,
offsetof(ViewOptions, check_option)}
};
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index bc0d90b4b1..d529782377 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2465,6 +2465,7 @@ _copyRangeTblEntry(const RangeTblEntry *from)
COPY_NODE_FIELD(tablesample);
COPY_NODE_FIELD(subquery);
COPY_SCALAR_FIELD(security_barrier);
+ COPY_SCALAR_FIELD(run_as_owner);
COPY_SCALAR_FIELD(jointype);
COPY_SCALAR_FIELD(joinmergedcols);
COPY_NODE_FIELD(joinaliasvars);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 2e7122ad2f..857fc0a9ac 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2776,6 +2776,7 @@ _equalRangeTblEntry(const RangeTblEntry *a, const RangeTblEntry *b)
COMPARE_NODE_FIELD(tablesample);
COMPARE_NODE_FIELD(subquery);
COMPARE_SCALAR_FIELD(security_barrier);
+ COMPARE_SCALAR_FIELD(run_as_owner);
COMPARE_SCALAR_FIELD(jointype);
COMPARE_SCALAR_FIELD(joinmergedcols);
COMPARE_NODE_FIELD(joinaliasvars);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 6bdad462c7..08d3abfdbf 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -3262,6 +3262,7 @@ _outRangeTblEntry(StringInfo str, const RangeTblEntry *node)
case RTE_SUBQUERY:
WRITE_NODE_FIELD(subquery);
WRITE_BOOL_FIELD(security_barrier);
+ WRITE_BOOL_FIELD(run_as_owner);
break;
case RTE_JOIN:
WRITE_ENUM_FIELD(jointype, JoinType);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 3f68f7c18d..be06f57422 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -1444,6 +1444,7 @@ _readRangeTblEntry(void)
case RTE_SUBQUERY:
READ_NODE_FIELD(subquery);
READ_BOOL_FIELD(security_barrier);
+ READ_BOOL_FIELD(run_as_owner);
break;
case RTE_JOIN:
READ_ENUM_FIELD(jointype, JoinType);
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 41bd1ae7d4..c85ad430c7 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -1217,6 +1217,9 @@ inline_cte_walker(Node *node, inline_cte_walker_context *context)
rte->subquery = newquery;
rte->security_barrier = false;
+ /* Run sub-query as owner by default */
+ rte->run_as_owner = true;
+
/* Zero out CTE-specific fields */
rte->ctename = NULL;
rte->ctelevelsup = 0;
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 282589dec8..54eeef8a13 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -660,6 +660,8 @@ preprocess_function_rtes(PlannerInfo *root)
rte->rtekind = RTE_SUBQUERY;
rte->subquery = funcquery;
rte->security_barrier = false;
+ /* Run sub-query as owner by default */
+ rte->run_as_owner = true;
/* Clear fields that should not be set in a subquery RTE */
rte->functions = NIL;
rte->funcordinality = false;
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 3d82138cb3..796553bbcc 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -1838,6 +1838,7 @@ ApplyRetrieveRule(Query *parsetree,
rte->rtekind = RTE_SUBQUERY;
rte->subquery = rule_action;
rte->security_barrier = RelationIsSecurityView(relation);
+ rte->run_as_owner = RelationSubqueryRunAsOwner(relation);
/* Clear fields that should not be set in a subquery RTE */
rte->relid = InvalidOid;
rte->relkind = 0;
@@ -3242,18 +3243,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 "run_as_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 (RelationSubqueryRunAsOwner(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 2707fed12f..aa322f99bd 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -825,11 +825,14 @@ 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 "run_as_owner" relopt
+ * set to false, we want the rule's table references to be checked as
+ * the user invoking the rule.
+ *
+ * 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
@@ -837,8 +840,12 @@ 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 (!(relation->rd_rel->relkind == RELKIND_VIEW
+ && !RelationSubqueryRunAsOwner(relation)))
+ {
+ setRuleCheckAsUser((Node *) rule->actions, relation->rd_rel->relowner);
+ setRuleCheckAsUser(rule->qual, relation->rd_rel->relowner);
+ }
if (numlocks >= maxlocks)
{
@@ -1163,27 +1170,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;
@@ -1215,6 +1201,27 @@ retry:
/* extract reloptions if any */
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/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 34218b718c..407d664d9c 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1042,6 +1042,7 @@ typedef struct RangeTblEntry
*/
Query *subquery; /* the sub-query */
bool security_barrier; /* is from security_barrier view? */
+ bool run_as_owner; /* run the sub-query as view owner */
/*
* Fields valid for a join RTE (else NULL/zero):
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 6da1b220cd..dbdd8b3967 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 run_as_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 run_as_owner property set, or
+ * not. Note multiple eval of argument!
+ */
+#define RelationSubqueryRunAsOwner(relation) \
+ (AssertMacro(relation->rd_rel->relkind == RELKIND_VIEW), \
+ (relation)->rd_options ? \
+ ((ViewOptions *) (relation)->rd_options)->run_as_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 ca1833dc66..42b54357a4 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -288,17 +288,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 (run_as_owner=false)
+ AS SELECT * FROM tbl1 WHERE a = 100;
+CREATE VIEW mysecview8 WITH (run_as_owner=true, security_barrier=true)
+ AS SELECT * FROM tbl1 WHERE a > 100;
+CREATE VIEW mysecview9 WITH (run_as_owner)
+ AS SELECT * FROM tbl1 WHERE a < 100;
+CREATE VIEW mysecview10 WITH (run_as_owner=100) -- Error
+ AS SELECT * FROM tbl1 WHERE a <> 100;
+ERROR: invalid value for boolean option "run_as_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 | {run_as_owner=false}
+ mysecview8 | v | {run_as_owner=true,security_barrier=true}
+ mysecview9 | v | {run_as_owner=true}
+(7 rows)
CREATE OR REPLACE VIEW mysecview1
AS SELECT * FROM tbl1 WHERE a = 256;
@@ -308,17 +322,28 @@ 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 (run_as_owner=false)
+ AS SELECT * FROM tbl1 WHERE a < 256;
+CREATE OR REPLACE VIEW mysecview9 WITH (run_as_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 | {run_as_owner=false}
+ mysecview9 | v | {run_as_owner=true,security_barrier=true}
+(7 rows)
-- Check that unknown literals are converted to "text" in CREATE VIEW,
-- so that we don't end up with unknown-type columns.
@@ -2031,7 +2056,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 77 other objects
DETAIL: drop cascades to table t1
drop cascades to view temporal1
drop cascades to view temporal2
@@ -2052,6 +2077,9 @@ 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 view unspecified_types
drop cascades to table tt1
drop cascades to table tx1
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 89397e41f0..02d3f63c6f 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,59 @@ SELECT * FROM category;
44 | manga
(4 rows)
+-- Test views with run_as_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 (run_as_owner=false) AS
+SELECT * FROM category;
+CREATE OR REPLACE FUNCTION run_as_owner_func() RETURNS SETOF category
+ AS 'SELECT * FROM category'
+ LANGUAGE SQL STABLE STRICT;
+CREATE VIEW v1f WITH (run_as_owner=false) AS
+SELECT * FROM run_as_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)
+
+RESET SESSION AUTHORIZATION;
+CREATE TABLE sivt1 (x int);
+CREATE VIEW v1t WITH (run_as_owner=false) AS
+SELECT * FROM sivt1;
+ALTER VIEW v1t OWNER TO regress_rls_group1;
+GRANT INSERT, UPDATE, DELETE ON sivt1 TO regress_rls_group1;
+GRANT SELECT ON sivt1 TO regress_rls_group2;
+GRANT SELECT, INSERT, UPDATE, DELETE ON v1t TO regress_rls_group2;
+SET SESSION AUTHORIZATION regress_rls_carol;
+SELECT * FROM v1t;
+ x
+---
+(0 rows)
+
+INSERT INTO v1t values (1);
+ERROR: permission denied for table sivt1
+UPDATE v1t SET x = 2;
+ERROR: permission denied for table sivt1
+DELETE FROM v1t;
+ERROR: permission denied for table sivt1
--
-- Table inheritance and RLS policy
--
@@ -3987,11 +4045,16 @@ RESET SESSION AUTHORIZATION;
--
RESET SESSION AUTHORIZATION;
DROP SCHEMA regress_rls_schema CASCADE;
-NOTICE: drop cascades to 29 other objects
+NOTICE: drop cascades to 34 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 run_as_owner_func()
+drop cascades to view v1f
+drop cascades to table sivt1
+drop cascades to view v1t
drop cascades to table part_document
drop cascades to table dependent
drop cascades to table rec1
diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql
index 6bb5b8df5e..2e4457cbf6 100644
--- a/src/test/regress/sql/create_view.sql
+++ b/src/test/regress/sql/create_view.sql
@@ -245,9 +245,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 (run_as_owner=false)
+ AS SELECT * FROM tbl1 WHERE a = 100;
+CREATE VIEW mysecview8 WITH (run_as_owner=true, security_barrier=true)
+ AS SELECT * FROM tbl1 WHERE a > 100;
+CREATE VIEW mysecview9 WITH (run_as_owner)
+ AS SELECT * FROM tbl1 WHERE a < 100;
+CREATE VIEW mysecview10 WITH (run_as_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
@@ -258,9 +268,17 @@ 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 (run_as_owner=false)
+ AS SELECT * FROM tbl1 WHERE a < 256;
+CREATE OR REPLACE VIEW mysecview9 WITH (run_as_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;
-- Check that unknown literals are converted to "text" in CREATE VIEW,
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 44deb42bad..a5b5a86108 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,45 @@ SET row_security TO OFF;
SELECT * FROM document;
SELECT * FROM category;
+-- Test views with run_as_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 (run_as_owner=false) AS
+SELECT * FROM category;
+CREATE OR REPLACE FUNCTION run_as_owner_func() RETURNS SETOF category
+ AS 'SELECT * FROM category'
+ LANGUAGE SQL STABLE STRICT;
+CREATE VIEW v1f WITH (run_as_owner=false) AS
+SELECT * FROM run_as_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;
+
+RESET SESSION AUTHORIZATION;
+
+CREATE TABLE sivt1 (x int);
+CREATE VIEW v1t WITH (run_as_owner=false) AS
+SELECT * FROM sivt1;
+ALTER VIEW v1t OWNER TO regress_rls_group1;
+
+GRANT INSERT, UPDATE, DELETE ON sivt1 TO regress_rls_group1;
+GRANT SELECT ON sivt1 TO regress_rls_group2;
+GRANT SELECT, INSERT, UPDATE, DELETE ON v1t TO regress_rls_group2;
+
+SET SESSION AUTHORIZATION regress_rls_carol;
+
+SELECT * FROM v1t;
+INSERT INTO v1t values (1);
+UPDATE v1t SET x = 2;
+DELETE FROM v1t;
+
--
-- Table inheritance and RLS policy
--
--
2.35.1
