Hi Laurenz,
thanks for the review!
I've attached a v2 where I addressed the things you mentioned.
On 1/11/22 19:59, Laurenz Albe wrote:
[..]
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.
A boolean option would have been indeed the better choice, I agree.
I haven't though of any specific other values for this enum, it was
rather a decision following a off-list discussion.
I've changed the option to be boolean and renamed it to
"security_invoker". This puts it in line with how other systems (e.g.
MySQL) name their equivalent feature, so I think this should be an
appropriate choice.
Finally, patch 0003 updates the documentation for this new reloption.
[..]
Please avoid long lines like that.
Fixed.
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).
It makes sense to put it there, thanks for the pointer! I wasn't really
that sure where to put the documentation to start with, and this seems
like a more appropriate place.
Please review further.
Thanks,
Christoph Heiss
From 25267e6b8a2ffd81f14acbee95ef08d9edf3d31c Mon Sep 17 00:00:00 2001
From: Christoph Heiss <christoph.he...@cybertec.at>
Date: Tue, 18 Jan 2022 15:42:58 +0100
Subject: [PATCH 1/3] [PATCH v2 1/3] Add new boolean reloption security_invoker
to views
When this reloption is set to true, all references to the underlying tables will
be checked against the invoking user rather than the view owner, as is currently
implemented.
Row level security must be enabled on the tables for this to take effect.
Signed-off-by: Christoph Heiss <christoph.he...@cybertec.at>
---
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 | 1 +
src/backend/optimizer/prep/prepjointree.c | 1 +
src/backend/rewrite/rewriteHandler.c | 1 +
src/backend/utils/cache/relcache.c | 63 +++++++++++++----------
src/include/nodes/parsenodes.h | 1 +
src/include/utils/rel.h | 11 ++++
11 files changed, 65 insertions(+), 28 deletions(-)
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index b5602f5323..3c84982fda 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -140,6 +140,15 @@ static relopt_bool boolRelOpts[] =
},
false
},
+ {
+ {
+ "security_invoker",
+ "View subquery in invoked within the current security context.",
+ RELOPT_KIND_VIEW,
+ AccessExclusiveLock
+ },
+ false
+ },
{
{
"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)},
+ {"security_invoker", RELOPT_TYPE_BOOL,
+ offsetof(ViewOptions, security_invoker)},
{"check_option", RELOPT_TYPE_ENUM,
offsetof(ViewOptions, check_option)}
};
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index df0b747883..6efaa07523 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2464,6 +2464,7 @@ _copyRangeTblEntry(const RangeTblEntry *from)
COPY_NODE_FIELD(tablesample);
COPY_NODE_FIELD(subquery);
COPY_SCALAR_FIELD(security_barrier);
+ COPY_SCALAR_FIELD(security_invoker);
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 cb7ddd463c..7f0401fa84 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2766,6 +2766,7 @@ _equalRangeTblEntry(const RangeTblEntry *a, const RangeTblEntry *b)
COMPARE_NODE_FIELD(tablesample);
COMPARE_NODE_FIELD(subquery);
COMPARE_SCALAR_FIELD(security_barrier);
+ COMPARE_SCALAR_FIELD(security_invoker);
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 91a89b6d51..7dee550856 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -3260,6 +3260,7 @@ _outRangeTblEntry(StringInfo str, const RangeTblEntry *node)
case RTE_SUBQUERY:
WRITE_NODE_FIELD(subquery);
WRITE_BOOL_FIELD(security_barrier);
+ WRITE_BOOL_FIELD(security_invoker);
break;
case RTE_JOIN:
WRITE_ENUM_FIELD(jointype, JoinType);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index dcec3b728f..95f006cf04 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -1446,6 +1446,7 @@ _readRangeTblEntry(void)
case RTE_SUBQUERY:
READ_NODE_FIELD(subquery);
READ_BOOL_FIELD(security_barrier);
+ READ_BOOL_FIELD(security_invoker);
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 c9f7a09d10..c2b1a201ba 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -1216,6 +1216,7 @@ inline_cte_walker(Node *node, inline_cte_walker_context *context)
rte->rtekind = RTE_SUBQUERY;
rte->subquery = newquery;
rte->security_barrier = false;
+ rte->security_invoker = false;
/* Zero out CTE-specific fields */
rte->ctename = NULL;
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 387a35e112..18b209ef7d 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -660,6 +660,7 @@ preprocess_function_rtes(PlannerInfo *root)
rte->rtekind = RTE_SUBQUERY;
rte->subquery = funcquery;
rte->security_barrier = false;
+ rte->security_invoker = false;
/* 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 9521e81100..68830412c5 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->security_invoker = RelationHasSecurityInvoker(relation);
/* Clear fields that should not be set in a subquery RTE */
rte->relid = InvalidOid;
rte->relkind = 0;
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 105d8d4601..e9515ee896 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 security_invoker relopt
+ * set to true, we want the rule's table references to be checked as
+ * the user referencing 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
+ && RelationHasSecurityInvoker(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 4c5a8a39bf..31d8a951ea 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1041,6 +1041,7 @@ typedef struct RangeTblEntry
*/
Query *subquery; /* the sub-query */
bool security_barrier; /* is from security_barrier view? */
+ bool security_invoker; /* from a view with security_invoker set? */
/*
* Fields valid for a join RTE (else NULL/zero):
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 31281279cf..d84491baa7 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 security_invoker;
ViewOptCheckOption check_option;
} ViewOptions;
@@ -411,6 +412,16 @@ typedef struct ViewOptions
(relation)->rd_options ? \
((ViewOptions *) (relation)->rd_options)->security_barrier : false)
+/*
+ * RelationHasSecurityRelationPermissions
+ * Returns true if the relation has the security invoker property set, or
+ * not. Note multiple eval of argument!
+ */
+#define RelationHasSecurityInvoker(relation) \
+ (AssertMacro(relation->rd_rel->relkind == RELKIND_VIEW), \
+ (relation)->rd_options ? \
+ ((ViewOptions *) (relation)->rd_options)->security_invoker : false)
+
/*
* RelationHasCheckOption
* Returns true if the relation is a view defined with either the local
--
2.34.1
From 2ee36aba5cb9c042bb9492d8c9fd0ab2aa1512c6 Mon Sep 17 00:00:00 2001
From: Christoph Heiss <christoph.he...@cybertec.at>
Date: Tue, 18 Jan 2022 15:34:13 +0100
Subject: [PATCH 2/3] [PATCH v2 2/3] Add regression tests for new
security_invoker reloption on views
This expands on the current regressions tests for CREATE VIEW and
ROW LEVEL SECURITY-related matters.
Signed-off-by: Christoph Heiss <christoph.he...@cybertec.at>
---
src/test/regress/expected/create_view.out | 42 ++++++++++++++++++----
src/test/regress/expected/rowsecurity.out | 43 ++++++++++++++++++++++-
src/test/regress/sql/create_view.sql | 26 +++++++++++---
src/test/regress/sql/rowsecurity.sql | 26 ++++++++++++++
4 files changed, 125 insertions(+), 12 deletions(-)
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index f50ef76685..5df4bf389f 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -261,15 +261,26 @@ CREATE VIEW mysecview3 WITH (security_barrier=false)
AS SELECT * FROM tbl1 WHERE a < 0;
CREATE VIEW mysecview4 WITH (security_barrier)
AS SELECT * FROM tbl1 WHERE a <> 0;
-CREATE VIEW mysecview5 WITH (security_barrier=100) -- Error
+CREATE VIEW mysecview5 WITH (security_invoker=true)
+ AS SELECT * FROM tbl1 WHERE a = 100;
+CREATE VIEW mysecview6 WITH (security_invoker=false)
AS SELECT * FROM tbl1 WHERE a > 100;
+CREATE VIEW mysecview7 WITH (security_invoker)
+ AS SELECT * FROM tbl1 WHERE a < 100;
+CREATE VIEW mysecview8 WITH (security_barrier=100) -- Error
+ AS SELECT * FROM tbl1 WHERE a <> 100;
ERROR: invalid value for boolean option "security_barrier": 100
-CREATE VIEW mysecview6 WITH (invalid_option) -- Error
+CREATE VIEW mysecview9 WITH (security_invoker=100) -- Error
+ AS SELECT * FROM tbl1 WHERE a = 100;
+ERROR: invalid value for boolean option "security_invoker": 100
+CREATE VIEW mysecview10 WITH (invalid_option) -- Error
AS SELECT * FROM tbl1 WHERE a < 100;
ERROR: unrecognized parameter "invalid_option"
SELECT relname, relkind, reloptions FROM pg_class
WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass,
- 'mysecview3'::regclass, 'mysecview4'::regclass)
+ 'mysecview3'::regclass, 'mysecview4'::regclass,
+ 'mysecview5'::regclass, 'mysecview6'::regclass,
+ 'mysecview7'::regclass)
ORDER BY relname;
relname | relkind | reloptions
------------+---------+--------------------------
@@ -277,7 +288,10 @@ SELECT relname, relkind, reloptions FROM pg_class
mysecview2 | v | {security_barrier=true}
mysecview3 | v | {security_barrier=false}
mysecview4 | v | {security_barrier=true}
-(4 rows)
+ mysecview5 | v | {security_invoker=true}
+ mysecview6 | v | {security_invoker=false}
+ mysecview7 | v | {security_invoker=true}
+(7 rows)
CREATE OR REPLACE VIEW mysecview1
AS SELECT * FROM tbl1 WHERE a = 256;
@@ -287,9 +301,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 mysecview5
+ AS SELECT * FROM tbl1 WHERE a > 256;
+CREATE OR REPLACE VIEW mysecview6 WITH (security_invoker=true)
+ AS SELECT * FROM tbl1 WHERE a < 256;
+CREATE OR REPLACE VIEW mysecview7 WITH (security_invoker=false)
+ 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,
+ 'mysecview5'::regclass, 'mysecview6'::regclass,
+ 'mysecview7'::regclass)
ORDER BY relname;
relname | relkind | reloptions
------------+---------+--------------------------
@@ -297,7 +319,10 @@ SELECT relname, relkind, reloptions FROM pg_class
mysecview2 | v |
mysecview3 | v | {security_barrier=true}
mysecview4 | v | {security_barrier=false}
-(4 rows)
+ mysecview5 | v |
+ mysecview6 | v | {security_invoker=true}
+ mysecview7 | v | {security_invoker=false}
+(7 rows)
-- Check that unknown literals are converted to "text" in CREATE VIEW,
-- so that we don't end up with unknown-type columns.
@@ -1994,7 +2019,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 73 other objects
+NOTICE: drop cascades to 76 other objects
DETAIL: drop cascades to table t1
drop cascades to view temporal1
drop cascades to view temporal2
@@ -2015,6 +2040,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 mysecview5
+drop cascades to view mysecview6
+drop cascades to view mysecview7
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..d10ecaa272 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_grace;
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_grace 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_grace;
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 security_invoker reloption set
+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 (security_invoker=true) AS
+SELECT * FROM category;
+CREATE OR REPLACE FUNCTION security_invoker_func() RETURNS SETOF category
+ AS 'SELECT * FROM category'
+ LANGUAGE SQL STABLE STRICT;
+CREATE VIEW v1f WITH (security_invoker=true) AS
+SELECT * FROM security_invoker_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_grace;
+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 security_invoker_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/sql/create_view.sql b/src/test/regress/sql/create_view.sql
index bdda56e8de..9736fba8fd 100644
--- a/src/test/regress/sql/create_view.sql
+++ b/src/test/regress/sql/create_view.sql
@@ -214,13 +214,23 @@ CREATE VIEW mysecview3 WITH (security_barrier=false)
AS SELECT * FROM tbl1 WHERE a < 0;
CREATE VIEW mysecview4 WITH (security_barrier)
AS SELECT * FROM tbl1 WHERE a <> 0;
-CREATE VIEW mysecview5 WITH (security_barrier=100) -- Error
+CREATE VIEW mysecview5 WITH (security_invoker=true)
+ AS SELECT * FROM tbl1 WHERE a = 100;
+CREATE VIEW mysecview6 WITH (security_invoker=false)
AS SELECT * FROM tbl1 WHERE a > 100;
-CREATE VIEW mysecview6 WITH (invalid_option) -- Error
+CREATE VIEW mysecview7 WITH (security_invoker)
+ AS SELECT * FROM tbl1 WHERE a < 100;
+CREATE VIEW mysecview8 WITH (security_barrier=100) -- Error
+ AS SELECT * FROM tbl1 WHERE a <> 100;
+CREATE VIEW mysecview9 WITH (security_invoker=100) -- Error
+ AS SELECT * FROM tbl1 WHERE a = 100;
+CREATE VIEW mysecview10 WITH (invalid_option) -- 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,
+ 'mysecview5'::regclass, 'mysecview6'::regclass,
+ 'mysecview7'::regclass)
ORDER BY relname;
CREATE OR REPLACE VIEW mysecview1
@@ -231,9 +241,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 mysecview5
+ AS SELECT * FROM tbl1 WHERE a > 256;
+CREATE OR REPLACE VIEW mysecview6 WITH (security_invoker=true)
+ AS SELECT * FROM tbl1 WHERE a < 256;
+CREATE OR REPLACE VIEW mysecview7 WITH (security_invoker=false)
+ 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,
+ 'mysecview5'::regclass, 'mysecview6'::regclass,
+ 'mysecview7'::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..239a15e3d1 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_grace;
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_grace 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_grace;
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 security_invoker reloption set
+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 (security_invoker=true) AS
+SELECT * FROM category;
+CREATE OR REPLACE FUNCTION security_invoker_func() RETURNS SETOF category
+ AS 'SELECT * FROM category'
+ LANGUAGE SQL STABLE STRICT;
+CREATE VIEW v1f WITH (security_invoker=true) AS
+SELECT * FROM security_invoker_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_grace;
+SELECT * FROM category;
+SELECT * FROM v1;
+SELECT * FROM v1f;
+
--
-- Table inheritance and RLS policy
--
--
2.34.1
From 55a0982d1124f8a26926d172542fdc771ccdc594 Mon Sep 17 00:00:00 2001
From: Christoph Heiss <christoph.he...@cybertec.at>
Date: Tue, 18 Jan 2022 16:05:07 +0100
Subject: [PATCH 3/3] [PATCH v2 3/3] Add documentation for new security_invoker
reloption on views
Signed-off-by: Christoph Heiss <christoph.he...@cybertec.at>
---
doc/src/sgml/ref/alter_view.sgml | 10 ++++++++
doc/src/sgml/ref/create_view.sgml | 38 +++++++++++++++++++++++++------
2 files changed, 41 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/ref/alter_view.sgml b/doc/src/sgml/ref/alter_view.sgml
index 98c312c5bf..cb9df185e2 100644
--- a/doc/src/sgml/ref/alter_view.sgml
+++ b/doc/src/sgml/ref/alter_view.sgml
@@ -161,6 +161,16 @@ ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RESET
</para>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>security_invoker</literal> (<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ Changes the security-invoker property of the view. The value must
+ be 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..0507551c2d 100644
--- a/doc/src/sgml/ref/create_view.sgml
+++ b/doc/src/sgml/ref/create_view.sgml
@@ -152,6 +152,23 @@ CREATE VIEW [ <replaceable>schema</replaceable> . ] <replaceable>view_name</repl
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>security_invoker</literal> (<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ If this option is set, it will cause all access to the underlying
+ tables to be checked as referenced by the invoking user, rather than
+ the view owner. This will only take effect when row level security is
+ enabled on the underlying tables (using <link linkend="sql-altertable">
+ <command>ALTER TABLE ... ENABLE ROW LEVEL SECURITY</command></link>).
+ </para>
+ <para>This option can be changed on existing views using <link
+ linkend="sql-alterview"><command>ALTER VIEW</command></link>. See
+ <xref linkend="ddl-rowsecurity"/> for more details on row level security.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist></para>
</listitem>
</varlistentry>
@@ -265,13 +282,20 @@ 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 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.
+ </para>
+
+ <para>
+ If the <firstterm>security_invoker</firstterm> option is set on the view,
+ access to tables is determined by permissions of the invoking user, rather
+ than the view owner. This can be used to provide stricter permission
+ checking to the underlying tables than by default.
</para>
<para>
--
2.34.1
