Skip to content

Commit 7faa5fc

Browse files
deanrasheeddeanrasheed
committed
Add support for security invoker views.
A security invoker view checks permissions for accessing its underlying base relations using the privileges of the user of the view, rather than the privileges of the view owner. Additionally, if any of the base relations are tables with RLS enabled, the policies of the user of the view are applied, rather than those of the view owner. This allows views to be defined without giving away additional privileges on the underlying base relations, and matches a similar feature available in other database systems. It also allows views to operate more naturally with RLS, without affecting the assignments of policies to users. Christoph Heiss, with some additional hacking by me. Reviewed by Laurenz Albe and Wolfgang Walther. Discussion: https://postgr.es/m/b66dd6d6-ad3e-c6f2-8b90-47be773da240%40cybertec.at
1 parent f5576a2 commit 7faa5fc

20 files changed

+1239
-75
lines changed

‎doc/src/sgml/ref/alter_view.sgml

+11-1
Original file line numberDiff line numberDiff line change
@@ -156,7 +156,17 @@ ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RESET
156156
<listitem>
157157
<para>
158158
Changes the security-barrier property of the view. The value must
159-
be Boolean value, such as <literal>true</literal>
159+
be a Boolean value, such as <literal>true</literal>
160+
or <literal>false</literal>.
161+
</para>
162+
</listitem>
163+
</varlistentry>
164+
<varlistentry>
165+
<term><literal>security_invoker</literal> (<type>boolean</type>)</term>
166+
<listitem>
167+
<para>
168+
Changes the security-invoker property of the view. The value must
169+
be a Boolean value, such as <literal>true</literal>
160170
or <literal>false</literal>.
161171
</para>
162172
</listitem>

‎doc/src/sgml/ref/create_policy.sgml

+3-1
Original file line numberDiff line numberDiff line change
@@ -608,7 +608,9 @@ AND
608608
This does not change how views
609609
work, however. As with normal queries and views, permission checks and
610610
policies for the tables which are referenced by a view will use the view
611-
owner's rights and any policies which apply to the view owner.
611+
owner's rights and any policies which apply to the view owner, except if
612+
the view is defined using the <literal>security_invoker</literal> option
613+
(see <link linkend="sql-createview"><command>CREATE VIEW</command></link>).
612614
</para>
613615

614616
<para>

‎doc/src/sgml/ref/create_view.sgml

+89-16
Original file line numberDiff line numberDiff line change
@@ -137,8 +137,6 @@ CREATE VIEW [ <replaceable>schema</replaceable> . ] <replaceable>view_name</repl
137137
This parameter may be either <literal>local</literal> or
138138
<literal>cascaded</literal>, and is equivalent to specifying
139139
<literal>WITH [ CASCADED | LOCAL ] CHECK OPTION</literal> (see below).
140-
This option can be changed on existing views using <link
141-
linkend="sql-alterview"><command>ALTER VIEW</command></link>.
142140
</para>
143141
</listitem>
144142
</varlistentry>
@@ -152,7 +150,22 @@ CREATE VIEW [ <replaceable>schema</replaceable> . ] <replaceable>view_name</repl
152150
</para>
153151
</listitem>
154152
</varlistentry>
155-
</variablelist></para>
153+
154+
<varlistentry>
155+
<term><literal>security_invoker</literal> (<type>boolean</type>)</term>
156+
<listitem>
157+
<para>
158+
This option causes the underlying base relations to be checked
159+
against the privileges of the user of the view rather than the view
160+
owner. See the notes below for full details.
161+
</para>
162+
</listitem>
163+
</varlistentry>
164+
</variablelist>
165+
166+
All of the above options can be changed on existing views using <link
167+
linkend="sql-alterview"><command>ALTER VIEW</command></link>.
168+
</para>
156169
</listitem>
157170
</varlistentry>
158171

@@ -265,18 +278,74 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
265278
</para>
266279

267280
<para>
268-
Access to tables referenced in the view is determined by permissions of
269-
the view owner. In some cases, this can be used to provide secure but
270-
restricted access to the underlying tables. However, not all views are
271-
secure against tampering; see <xref linkend="rules-privileges"/> for
272-
details. Functions called in the view are treated the same as if they had
273-
been called directly from the query using the view. Therefore the user of
281+
By default, access to the underlying base relations referenced in the view
282+
is determined by the permissions of the view owner. In some cases, this
283+
can be used to provide secure but restricted access to the underlying
284+
tables. However, not all views are secure against tampering; see <xref
285+
linkend="rules-privileges"/> for details.
286+
</para>
287+
288+
<para>
289+
If the view has the <literal>security_invoker</literal> property set to
290+
<literal>true</literal>, access to the underlying base relations is
291+
determined by the permissions of the user executing the query, rather than
292+
the view owner. Thus, the user of a security invoker view must have the
293+
relevant permissions on the view and its underlying base relations.
294+
</para>
295+
296+
<para>
297+
If any of the underlying base relations is a security invoker view, it
298+
will be treated as if it had been accessed directly from the original
299+
query. Thus, a security invoker view will always check its underlying
300+
base relations using the permissions of the current user, even if it is
301+
accessed from a view without the <literal>security_invoker</literal>
302+
property.
303+
</para>
304+
305+
<para>
306+
If any of the underlying base relations has
307+
<link linkend="ddl-rowsecurity">row-level security</link> enabled, then
308+
by default, the row-level security policies of the view owner are applied,
309+
and access to any additional relations referred to by those policies is
310+
determined by the permissions of the view owner. However, if the view has
311+
<literal>security_invoker</literal> set to <literal>true</literal>, then
312+
the policies and permissions of the invoking user are used instead, as if
313+
the base relations had been referenced directly from the query using the
314+
view.
315+
</para>
316+
317+
<para>
318+
Functions called in the view are treated the same as if they had been
319+
called directly from the query using the view. Therefore, the user of
274320
a view must have permissions to call all functions used by the view.
321+
Functions in the view are executed with the privileges of the user
322+
executing the query or the function owner, depending on whether the
323+
functions are defined as <literal>SECURITY INVOKER</literal> or
324+
<literal>SECURITY DEFINER</literal>. Thus, for example, calling
325+
<literal>CURRENT_USER</literal> directly in a view will always return the
326+
invoking user, not the view owner. This is not affected by the view's
327+
<literal>security_invoker</literal> setting, and so a view with
328+
<literal>security_invoker</literal> set to <literal>false</literal> is
329+
<emphasis>not</emphasis> equivalent to a
330+
<literal>SECURITY DEFINER</literal> function and those concepts should not
331+
be confused.
332+
</para>
333+
334+
<para>
335+
The user creating or replacing a view must have <literal>USAGE</literal>
336+
privileges on any schemas referred to in the view query, in order to look
337+
up the referenced objects in those schemas. Note, however, that this
338+
lookup only happens when the view is created or replaced. Therefore, the
339+
user of the view only requires the <literal>USAGE</literal> privilege on
340+
the schema containing the view, not on the schemas referred to in the view
341+
query, even for a security invoker view.
275342
</para>
276343

277344
<para>
278-
When <command>CREATE OR REPLACE VIEW</command> is used on an
279-
existing view, only the view's defining SELECT rule is changed.
345+
When <command>CREATE OR REPLACE VIEW</command> is used on an existing
346+
view, only the view's defining SELECT rule, plus any
347+
<literal>WITH ( ... )</literal> parameters and its
348+
<literal>CHECK OPTION</literal> are changed.
280349
Other view properties, including ownership, permissions, and non-SELECT
281350
rules, remain unchanged. You must own the view
282351
to replace it (this includes being a member of the owning role).
@@ -387,10 +456,13 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
387456
<para>
388457
Note that the user performing the insert, update or delete on the view
389458
must have the corresponding insert, update or delete privilege on the
390-
view. In addition the view's owner must have the relevant privileges on
391-
the underlying base relations, but the user performing the update does
392-
not need any permissions on the underlying base relations (see
393-
<xref linkend="rules-privileges"/>).
459+
view. In addition, by default, the view's owner must have the relevant
460+
privileges on the underlying base relations, whereas the user performing
461+
the update does not need any permissions on the underlying base relations
462+
(see <xref linkend="rules-privileges"/>). However, if the view has
463+
<literal>security_invoker</literal> set to <literal>true</literal>, the
464+
user performing the update, rather than the view owner, must have the
465+
relevant privileges on the underlying base relations.
394466
</para>
395467
</refsect2>
396468
</refsect1>
@@ -486,7 +558,8 @@ UNION ALL
486558
<command>CREATE OR REPLACE VIEW</command> is a
487559
<productname>PostgreSQL</productname> language extension.
488560
So is the concept of a temporary view.
489-
The <literal>WITH ( ... )</literal> clause is an extension as well.
561+
The <literal>WITH ( ... )</literal> clause is an extension as well, as are
562+
security barrier views and security invoker views.
490563
</para>
491564
</refsect1>
492565

‎doc/src/sgml/ref/lock.sgml

+9-4
Original file line numberDiff line numberDiff line change
@@ -174,10 +174,15 @@ LOCK [ TABLE ] [ ONLY ] <replaceable class="parameter">name</replaceable> [ * ]
174174
</para>
175175

176176
<para>
177-
The user performing the lock on the view must have the corresponding privilege
178-
on the view. In addition the view's owner must have the relevant privileges on
179-
the underlying base relations, but the user performing the lock does
180-
not need any permissions on the underlying base relations.
177+
The user performing the lock on the view must have the corresponding
178+
privilege on the view. In addition, by default, the view's owner must
179+
have the relevant privileges on the underlying base relations, whereas the
180+
user performing the lock does not need any permissions on the underlying
181+
base relations. However, if the view has
182+
<literal>security_invoker</literal> set to <literal>true</literal>
183+
(see <link linkend="sql-createview"><command>CREATE VIEW</command></link>),
184+
the user performing the lock, rather than the view owner, must have the
185+
relevant privileges on the underlying base relations.
181186
</para>
182187

183188
<para>

‎doc/src/sgml/rules.sgml

+7-4
Original file line numberDiff line numberDiff line change
@@ -2007,11 +2007,14 @@ SELECT * FROM shoelace;
20072007
a relation (table or view) is automatically the owner of the
20082008
rewrite rules that are defined for it.
20092009
The <productname>PostgreSQL</productname> rule system changes the
2010-
behavior of the default access control system. Relations that
2011-
are used due to rules get checked against the
2010+
behavior of the default access control system. With the exception of
2011+
<literal>SELECT</literal> rules associated with security invoker views
2012+
(see <link linkend="sql-createview"><command>CREATE VIEW</command></link>),
2013+
all relations that are used due to rules get checked against the
20122014
privileges of the rule owner, not the user invoking the rule.
2013-
This means that users only need the required privileges
2014-
for the tables/views that are explicitly named in their queries.
2015+
This means that, except for security invoker views, users only need the
2016+
required privileges for the tables/views that are explicitly named in
2017+
their queries.
20152018
</para>
20162019

20172020
<para>

‎src/backend/access/common/reloptions.c

+11
Original file line numberDiff line numberDiff line change
@@ -140,6 +140,15 @@ static relopt_bool boolRelOpts[] =
140140
},
141141
false
142142
},
143+
{
144+
{
145+
"security_invoker",
146+
"Privileges on underlying relations are checked as the invoking user, not the view owner",
147+
RELOPT_KIND_VIEW,
148+
AccessExclusiveLock
149+
},
150+
false
151+
},
143152
{
144153
{
145154
"vacuum_truncate",
@@ -1996,6 +2005,8 @@ view_reloptions(Datum reloptions, bool validate)
19962005
static const relopt_parse_elt tab[] = {
19972006
{"security_barrier", RELOPT_TYPE_BOOL,
19982007
offsetof(ViewOptions, security_barrier)},
2008+
{"security_invoker", RELOPT_TYPE_BOOL,
2009+
offsetof(ViewOptions, security_invoker)},
19992010
{"check_option", RELOPT_TYPE_ENUM,
20002011
offsetof(ViewOptions, check_option)}
20012012
};

‎src/backend/commands/lockcmds.c

+15-4
Original file line numberDiff line numberDiff line change
@@ -169,7 +169,7 @@ typedef struct
169169
{
170170
LOCKMODE lockmode; /* lock mode to use */
171171
bool nowait; /* no wait mode */
172-
Oid viewowner; /* view owner for checking the privilege */
172+
Oid check_as_user; /* user for checking the privilege */
173173
Oid viewoid; /* OID of the view to be locked */
174174
List *ancestor_views; /* OIDs of ancestor views */
175175
} LockViewRecurse_context;
@@ -215,8 +215,12 @@ LockViewRecurse_walker(Node *node, LockViewRecurse_context *context)
215215
if (list_member_oid(context->ancestor_views, relid))
216216
continue;
217217

218-
/* Check permissions with the view owner's privilege. */
219-
aclresult = LockTableAclCheck(relid, context->lockmode, context->viewowner);
218+
/*
219+
* Check permissions as the specified user. This will either be
220+
* the view owner or the current user.
221+
*/
222+
aclresult = LockTableAclCheck(relid, context->lockmode,
223+
context->check_as_user);
220224
if (aclresult != ACLCHECK_OK)
221225
aclcheck_error(aclresult, get_relkind_objtype(relkind), relname);
222226

@@ -259,9 +263,16 @@ LockViewRecurse(Oid reloid, LOCKMODE lockmode, bool nowait,
259263
view = table_open(reloid, NoLock);
260264
viewquery = get_view_query(view);
261265

266+
/*
267+
* If the view has the security_invoker property set, check permissions as
268+
* the current user. Otherwise, check permissions as the view owner.
269+
*/
262270
context.lockmode = lockmode;
263271
context.nowait = nowait;
264-
context.viewowner = view->rd_rel->relowner;
272+
if (RelationHasSecurityInvoker(view))
273+
context.check_as_user = GetUserId();
274+
else
275+
context.check_as_user = view->rd_rel->relowner;
265276
context.viewoid = reloid;
266277
context.ancestor_views = lappend_oid(ancestor_views, reloid);
267278

‎src/backend/rewrite/rewriteHandler.c

+12-6
Original file line numberDiff line numberDiff line change
@@ -3242,18 +3242,24 @@ rewriteTargetView(Query *parsetree, Relation view)
32423242
0);
32433243

32443244
/*
3245-
* Mark the new target RTE for the permissions checks that we want to
3246-
* enforce against the view owner, as distinct from the query caller. At
3247-
* the relation level, require the same INSERT/UPDATE/DELETE permissions
3248-
* that the query caller needs against the view. We drop the ACL_SELECT
3249-
* bit that is presumably in new_rte->requiredPerms initially.
3245+
* If the view has "security_invoker" set, mark the new target RTE for the
3246+
* permissions checks that we want to enforce against the query caller.
3247+
* Otherwise we want to enforce them against the view owner.
3248+
*
3249+
* At the relation level, require the same INSERT/UPDATE/DELETE
3250+
* permissions that the query caller needs against the view. We drop the
3251+
* ACL_SELECT bit that is presumably in new_rte->requiredPerms initially.
32503252
*
32513253
* Note: the original view RTE remains in the query's rangetable list.
32523254
* Although it will be unused in the query plan, we need it there so that
32533255
* the executor still performs appropriate permissions checks for the
32543256
* query caller's use of the view.
32553257
*/
3256-
new_rte->checkAsUser = view->rd_rel->relowner;
3258+
if (RelationHasSecurityInvoker(view))
3259+
new_rte->checkAsUser = InvalidOid;
3260+
else
3261+
new_rte->checkAsUser = view->rd_rel->relowner;
3262+
32573263
new_rte->requiredPerms = view_rte->requiredPerms;
32583264

32593265
/*

0 commit comments

Comments
 (0)