🌐 AI搜索 & 代理 主页
Skip to content

Commit 0e4611c

Browse files
robertmhaasrobertmhaas
committed
Add a security_barrier option for views.
When a view is marked as a security barrier, it will not be pulled up into the containing query, and no quals will be pushed down into it, so that no function or operator chosen by the user can be applied to rows not exposed by the view. Views not configured with this option cannot provide robust row-level security, but will perform far better. Patch by KaiGai Kohei; original problem report by Heikki Linnakangas (in October 2009!). Review (in earlier versions) by Noah Misch and others. Design advice by Tom Lane and myself. Further review and cleanup by me.
1 parent f90dd28 commit 0e4611c

File tree

22 files changed

+300
-35
lines changed

22 files changed

+300
-35
lines changed

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

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -26,6 +26,8 @@ ALTER VIEW <replaceable class="parameter">name</replaceable> ALTER [ COLUMN ] <r
2626
ALTER VIEW <replaceable class="parameter">name</replaceable> OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
2727
ALTER VIEW <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
2828
ALTER VIEW <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
29+
ALTER VIEW <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">view_option_name</replaceable> [= <replaceable class="parameter">view_option_value</replaceable>] [, ... ] )
30+
ALTER VIEW <replaceable class="parameter">name</replaceable> RESET ( <replaceable class="parameter">view_option_name</replaceable> [, ... ] )
2931
</synopsis>
3032
</refsynopsisdiv>
3133

@@ -102,6 +104,24 @@ ALTER VIEW <replaceable class="parameter">name</replaceable> SET SCHEMA <replace
102104
</para>
103105
</listitem>
104106
</varlistentry>
107+
108+
<varlistentry>
109+
<term><replaceable class="parameter">view_option_name</replaceable></term>
110+
<listitem>
111+
<para>
112+
The name of a view option to be set or reset.
113+
</para>
114+
</listitem>
115+
</varlistentry>
116+
117+
<varlistentry>
118+
<term><replaceable class="parameter">view_option_name</replaceable></term>
119+
<listitem>
120+
<para>
121+
The new value for a view option.
122+
</para>
123+
</listitem>
124+
</varlistentry>
105125
</variablelist>
106126
</refsect1>
107127

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

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -22,6 +22,7 @@ PostgreSQL documentation
2222
<refsynopsisdiv>
2323
<synopsis>
2424
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW <replaceable class="PARAMETER">name</replaceable> [ ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) ]
25+
[ WITH ( <replaceable class="PARAMETER">view_option_name</replaceable> [= <replaceable class="PARAMETER">view_option_value</replaceable>] [, ... ] ) ]
2526
AS <replaceable class="PARAMETER">query</replaceable>
2627
</synopsis>
2728
</refsynopsisdiv>
@@ -98,6 +99,18 @@ CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW <replaceable class="PARAMETER">n
9899
</listitem>
99100
</varlistentry>
100101

102+
<varlistentry>
103+
<term><literal>WITH ( <replaceable class="PARAMETER">view_option_name</replaceable> [= <replaceable class="PARAMETER">view_option_value</replaceable>] [, ... ] )</literal></term>
104+
<listitem>
105+
<para>
106+
This clause specifies optional parameters for a view; currently, the
107+
only suppored parameter name is <literal>security_barrier</literal>,
108+
which should be enabled when a view is intended to provide row-level
109+
security. See <xref linkend="rules-privileges"> for full details.
110+
</para>
111+
</listitem>
112+
</varlistentry>
113+
101114
<varlistentry>
102115
<term><replaceable class="parameter">query</replaceable></term>
103116
<listitem>

‎doc/src/sgml/rules.sgml‎

Lines changed: 37 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1822,8 +1822,9 @@ GRANT SELECT ON phone_number TO secretary;
18221822
<para>
18231823
Note that while views can be used to hide the contents of certain
18241824
columns using the technique shown above, they cannot be used to reliably
1825-
conceal the data in unseen rows. For example, the following view is
1826-
insecure:
1825+
conceal the data in unseen rows unless the
1826+
<literal>security_barrier</literal> flag has been set. For example,
1827+
the following view is insecure:
18271828
<programlisting>
18281829
CREATE VIEW phone_number AS
18291830
SELECT person, phone FROM phone_data WHERE phone NOT LIKE '412%';
@@ -1870,6 +1871,40 @@ SELECT * FROM phone_number WHERE tricky(person, phone);
18701871
which references <literal>shoelace_log</> is an unqualified
18711872
<literal>INSERT</>. This might not be true in more complex scenarios.
18721873
</para>
1874+
1875+
<para>
1876+
When it is necessary for a view to provide row-level security, the
1877+
<literal>security_barrier</literal> attribute should be applied to
1878+
the view. This prevents maliciously-chosen functions and operators from
1879+
being invoked on rows until afterthe view has done its work. For
1880+
example, if the view shown above had been created like this, it would
1881+
be secure:
1882+
<programlisting>
1883+
CREATE VIEW phone_number WITH (security_barrier) AS
1884+
SELECT person, phone FROM phone_data WHERE phone NOT LIKE '412%';
1885+
</programlisting>
1886+
Views created with the <literal>security_barrier</literal> may perform
1887+
far worse than views created without this option. In general, there is
1888+
no way to avoid this: the fastest possible plan must be rejected
1889+
if it may compromise security. For this reason, this option is not
1890+
enabled by default.
1891+
</para>
1892+
1893+
<para>
1894+
It is important to understand that even a view created with the
1895+
<literal>security_barrier</literal> option is intended to be secure only
1896+
in the limited sense that the contents of the invisible tuples will not
1897+
passed to possibly-insecure functions. The user may well have other means
1898+
of making inferences about the unseen data; for example, they can see the
1899+
query plan using <command>EXPLAIN</command>, or measure the runtime of
1900+
queries against the view. A malicious attacker might be able to infer
1901+
something about the amount of unseen data, or even gain some information
1902+
about the data distribution or most common values (since these things may
1903+
affect the runtime of the plan; or even, since they are also reflected in
1904+
the optimizer statistics, the choice of plan). If these types of "covert
1905+
channel" attacks are of concern, it is probably unwise to grant any access
1906+
to the data at all.
1907+
</para>
18731908
</sect1>
18741909

18751910
<sect1 id="rules-status">

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

Lines changed: 15 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -67,6 +67,14 @@ static relopt_bool boolRelOpts[] =
6767
},
6868
true
6969
},
70+
{
71+
{
72+
"security_barrier",
73+
"View acts as a row security barrier",
74+
RELOPT_KIND_VIEW
75+
},
76+
false
77+
},
7078
/* list terminator */
7179
{{NULL}}
7280
};
@@ -781,6 +789,7 @@ extractRelOptions(HeapTuple tuple, TupleDesc tupdesc, Oid amoptions)
781789
{
782790
case RELKIND_RELATION:
783791
case RELKIND_TOASTVALUE:
792+
case RELKIND_VIEW:
784793
case RELKIND_UNCATALOGED:
785794
options = heap_reloptions(classForm->relkind, datum, false);
786795
break;
@@ -1139,7 +1148,9 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
11391148
{"autovacuum_vacuum_scale_factor", RELOPT_TYPE_REAL,
11401149
offsetof(StdRdOptions, autovacuum) +offsetof(AutoVacOpts, vacuum_scale_factor)},
11411150
{"autovacuum_analyze_scale_factor", RELOPT_TYPE_REAL,
1142-
offsetof(StdRdOptions, autovacuum) +offsetof(AutoVacOpts, analyze_scale_factor)}
1151+
offsetof(StdRdOptions, autovacuum) +offsetof(AutoVacOpts, analyze_scale_factor)},
1152+
{"security_barrier", RELOPT_TYPE_BOOL,
1153+
offsetof(StdRdOptions, security_barrier)},
11431154
};
11441155

11451156
options = parseRelOptions(reloptions, validate, kind, &numoptions);
@@ -1159,7 +1170,7 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
11591170
}
11601171

11611172
/*
1162-
* Parse options for heaps and toast tables.
1173+
* Parse options for heaps, views and toast tables.
11631174
*/
11641175
bytea *
11651176
heap_reloptions(char relkind, Datum reloptions, bool validate)
@@ -1181,6 +1192,8 @@ heap_reloptions(char relkind, Datum reloptions, bool validate)
11811192
return (bytea *) rdopts;
11821193
case RELKIND_RELATION:
11831194
return default_reloptions(reloptions, validate, RELOPT_KIND_HEAP);
1195+
case RELKIND_VIEW:
1196+
return default_reloptions(reloptions, validate, RELOPT_KIND_VIEW);
11841197
default:
11851198
/* other relkinds are not supported */
11861199
return NULL;

‎src/backend/commands/tablecmds.c‎

Lines changed: 50 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -366,7 +366,9 @@ static void ATExecDropCluster(Relation rel, LOCKMODE lockmode);
366366
static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
367367
char *tablespacename, LOCKMODE lockmode);
368368
static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode);
369-
static void ATExecSetRelOptions(Relation rel, List *defList, bool isReset, LOCKMODE lockmode);
369+
static void ATExecSetRelOptions(Relation rel, List *defList,
370+
AlterTableType operation,
371+
LOCKMODE lockmode);
370372
static void ATExecEnableDisableTrigger(Relation rel, char *trigname,
371373
char fires_when, bool skip_system, LOCKMODE lockmode);
372374
static void ATExecEnableDisableRule(Relation rel, char *rulename,
@@ -2866,6 +2868,7 @@ AlterTableGetLockLevel(List *cmds)
28662868
case AT_DropCluster:
28672869
case AT_SetRelOptions:
28682870
case AT_ResetRelOptions:
2871+
case AT_ReplaceRelOptions:
28692872
case AT_SetOptions:
28702873
case AT_ResetOptions:
28712874
case AT_SetStorage:
@@ -3094,8 +3097,9 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
30943097
pass = AT_PASS_MISC; /* doesn't actually matter */
30953098
break;
30963099
case AT_SetRelOptions: /* SET (...) */
3097-
case AT_ResetRelOptions: /* RESET (...) */
3098-
ATSimplePermissions(rel, ATT_TABLE | ATT_INDEX);
3100+
case AT_ResetRelOptions: /* RESET (...) */
3101+
case AT_ReplaceRelOptions: /* reset them all, then set just these */
3102+
ATSimplePermissions(rel, ATT_TABLE | ATT_INDEX | ATT_VIEW);
30993103
/* This command never recurses */
31003104
/* No command-specific prep needed */
31013105
pass = AT_PASS_MISC;
@@ -3338,12 +3342,10 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
33383342
*/
33393343
break;
33403344
case AT_SetRelOptions: /* SET (...) */
3341-
ATExecSetRelOptions(rel, (List *) cmd->def, false, lockmode);
3342-
break;
33433345
case AT_ResetRelOptions: /* RESET (...) */
3344-
ATExecSetRelOptions(rel, (List *) cmd->def, true, lockmode);
3346+
case AT_ReplaceRelOptions: /* replace entire option list */
3347+
ATExecSetRelOptions(rel, (List *) cmd->def, cmd->subtype, lockmode);
33453348
break;
3346-
33473349
case AT_EnableTrig: /* ENABLE TRIGGER name */
33483350
ATExecEnableDisableTrigger(rel, cmd->name,
33493351
TRIGGER_FIRES_ON_ORIGIN, false, lockmode);
@@ -8271,10 +8273,11 @@ ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel, char *tablespacename, L
82718273
}
82728274

82738275
/*
8274-
* ALTER TABLE/INDEX SET (...) or RESET (...)
8276+
* Set, reset, or replace reloptions.
82758277
*/
82768278
static void
8277-
ATExecSetRelOptions(Relation rel, List *defList, bool isReset, LOCKMODE lockmode)
8279+
ATExecSetRelOptions(Relation rel, List *defList, AlterTableType operation,
8280+
LOCKMODE lockmode)
82788281
{
82798282
Oid relid;
82808283
Relation pgclass;
@@ -8288,28 +8291,44 @@ ATExecSetRelOptions(Relation rel, List *defList, bool isReset, LOCKMODE lockmode
82888291
bool repl_repl[Natts_pg_class];
82898292
static char *validnsps[] = HEAP_RELOPT_NAMESPACES;
82908293

8291-
if (defList == NIL)
8294+
if (defList == NIL && operation != AT_ReplaceRelOptions)
82928295
return; /* nothing to do */
82938296

82948297
pgclass = heap_open(RelationRelationId, RowExclusiveLock);
82958298

8296-
/* Get the old reloptions */
8299+
/* Fetch heap tuple */
82978300
relid = RelationGetRelid(rel);
82988301
tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
82998302
if (!HeapTupleIsValid(tuple))
83008303
elog(ERROR, "cache lookup failed for relation %u", relid);
83018304

8302-
datum = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_reloptions, &isnull);
8305+
if (operation == AT_ReplaceRelOptions)
8306+
{
8307+
/*
8308+
* If we're supposed to replace the reloptions list, we just pretend
8309+
* there were none before.
8310+
*/
8311+
datum = (Datum) 0;
8312+
isnull = true;
8313+
}
8314+
else
8315+
{
8316+
/* Get the old reloptions */
8317+
datum = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_reloptions,
8318+
&isnull);
8319+
}
83038320

83048321
/* Generate new proposed reloptions (text array) */
83058322
newOptions = transformRelOptions(isnull ? (Datum) 0 : datum,
8306-
defList, NULL, validnsps, false, isReset);
8323+
defList, NULL, validnsps, false,
8324+
operation == AT_ResetRelOptions);
83078325

83088326
/* Validate */
83098327
switch (rel->rd_rel->relkind)
83108328
{
83118329
case RELKIND_RELATION:
83128330
case RELKIND_TOASTVALUE:
8331+
case RELKIND_VIEW:
83138332
(void) heap_reloptions(rel->rd_rel->relkind, newOptions, true);
83148333
break;
83158334
case RELKIND_INDEX:
@@ -8357,15 +8376,30 @@ ATExecSetRelOptions(Relation rel, List *defList, bool isReset, LOCKMODE lockmode
83578376

83588377
toastrel = heap_open(toastid, lockmode);
83598378

8360-
/* Get the old reloptions */
8379+
/* Fetch heap tuple */
83618380
tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(toastid));
83628381
if (!HeapTupleIsValid(tuple))
83638382
elog(ERROR, "cache lookup failed for relation %u", toastid);
83648383

8365-
datum = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_reloptions, &isnull);
8384+
if (operation == AT_ReplaceRelOptions)
8385+
{
8386+
/*
8387+
* If we're supposed to replace the reloptions list, we just
8388+
* pretend there were none before.
8389+
*/
8390+
datum = (Datum) 0;
8391+
isnull = true;
8392+
}
8393+
else
8394+
{
8395+
/* Get the old reloptions */
8396+
datum = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_reloptions,
8397+
&isnull);
8398+
}
83668399

83678400
newOptions = transformRelOptions(isnull ? (Datum) 0 : datum,
8368-
defList, "toast", validnsps, false, isReset);
8401+
defList, "toast", validnsps, false,
8402+
operation == AT_ResetRelOptions);
83698403

83708404
(void) heap_reloptions(RELKIND_TOASTVALUE, newOptions, true);
83718405

‎src/backend/commands/view.c‎

Lines changed: 19 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -32,6 +32,7 @@
3232
#include "utils/builtins.h"
3333
#include "utils/lsyscache.h"
3434
#include "utils/rel.h"
35+
#include "utils/syscache.h"
3536

3637

3738
static void checkViewTupleDesc(TupleDesc newdesc, TupleDesc olddesc);
@@ -98,7 +99,7 @@ isViewOnTempTable_walker(Node *node, void *context)
9899
*/
99100
static Oid
100101
DefineVirtualRelation(const RangeVar *relation, List *tlist, bool replace,
101-
Oid namespaceId)
102+
Oid namespaceId, List *options)
102103
{
103104
Oid viewOid;
104105
CreateStmt *createStmt = makeNode(CreateStmt);
@@ -166,6 +167,8 @@ DefineVirtualRelation(const RangeVar *relation, List *tlist, bool replace,
166167
{
167168
Relation rel;
168169
TupleDesc descriptor;
170+
List *atcmds = NIL;
171+
AlterTableCmd *atcmd;
169172

170173
/*
171174
* Yes. Get exclusive lock on the existing view ...
@@ -203,21 +206,27 @@ DefineVirtualRelation(const RangeVar *relation, List *tlist, bool replace,
203206
descriptor = BuildDescForRelation(attrList);
204207
checkViewTupleDesc(descriptor, rel->rd_att);
205208

209+
/*
210+
* The new options list replaces the existing options list, even
211+
* if it's empty.
212+
*/
213+
atcmd = makeNode(AlterTableCmd);
214+
atcmd->subtype = AT_ReplaceRelOptions;
215+
atcmd->def = (Node *) options;
216+
atcmds = lappend(atcmds, atcmd);
217+
206218
/*
207219
* If new attributes have been added, we must add pg_attribute entries
208220
* for them. It is convenient (although overkill) to use the ALTER
209221
* TABLE ADD COLUMN infrastructure for this.
210222
*/
211223
if (list_length(attrList) > rel->rd_att->natts)
212224
{
213-
List *atcmds = NIL;
214225
ListCell *c;
215226
int skip = rel->rd_att->natts;
216227

217228
foreach(c, attrList)
218229
{
219-
AlterTableCmd *atcmd;
220-
221230
if (skip > 0)
222231
{
223232
skip--;
@@ -228,9 +237,11 @@ DefineVirtualRelation(const RangeVar *relation, List *tlist, bool replace,
228237
atcmd->def = (Node *) lfirst(c);
229238
atcmds = lappend(atcmds, atcmd);
230239
}
231-
AlterTableInternal(viewOid, atcmds, true);
232240
}
233241

242+
/* OK, let's do it. */
243+
AlterTableInternal(viewOid, atcmds, true);
244+
234245
/*
235246
* Seems okay, so return the OID of the pre-existing view.
236247
*/
@@ -250,7 +261,8 @@ DefineVirtualRelation(const RangeVar *relation, List *tlist, bool replace,
250261
createStmt->tableElts = attrList;
251262
createStmt->inhRelations = NIL;
252263
createStmt->constraints = NIL;
253-
createStmt->options = list_make1(defWithOids(false));
264+
createStmt->options = options;
265+
createStmt->options = lappend(options, defWithOids(false));
254266
createStmt->oncommit = ONCOMMIT_NOOP;
255267
createStmt->tablespacename = NULL;
256268
createStmt->if_not_exists = false;
@@ -513,7 +525,7 @@ DefineView(ViewStmt *stmt, const char *queryString)
513525
* aborted.
514526
*/
515527
viewOid = DefineVirtualRelation(view, viewParse->targetList,
516-
stmt->replace, namespaceId);
528+
stmt->replace, namespaceId, stmt->options);
517529

518530
/*
519531
* The relation we have just created is not visible to any other commands

0 commit comments

Comments
 (0)