Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit0e4611c

Browse files
committed
Add a security_barrier option for views.
When a view is marked as a security barrier, it will not be pulled upinto 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 torows not exposed by the view. Views not configured with thisoption cannot provide robust row-level security, but will perform farbetter.Patch by KaiGai Kohei; original problem report by Heikki Linnakangas(in October 2009!). Review (in earlier versions) by Noah Misch andothers. Design advice by Tom Lane and myself. Further review andcleanup by me.
1 parentf90dd28 commit0e4611c

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
caseRELKIND_RELATION:
783791
caseRELKIND_TOASTVALUE:
792+
caseRELKIND_VIEW:
784793
caseRELKIND_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(charrelkind,Datumreloptions,boolvalidate)
@@ -1181,6 +1192,8 @@ heap_reloptions(char relkind, Datum reloptions, bool validate)
11811192
return (bytea*)rdopts;
11821193
caseRELKIND_RELATION:
11831194
returndefault_reloptions(reloptions,validate,RELOPT_KIND_HEAP);
1195+
caseRELKIND_VIEW:
1196+
returndefault_reloptions(reloptions,validate,RELOPT_KIND_VIEW);
11841197
default:
11851198
/* other relkinds are not supported */
11861199
returnNULL;

‎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
staticvoidATPrepSetTableSpace(AlteredTableInfo*tab,Relationrel,
367367
char*tablespacename,LOCKMODElockmode);
368368
staticvoidATExecSetTableSpace(OidtableOid,OidnewTableSpace,LOCKMODElockmode);
369-
staticvoidATExecSetRelOptions(Relationrel,List*defList,boolisReset,LOCKMODElockmode);
369+
staticvoidATExecSetRelOptions(Relationrel,List*defList,
370+
AlterTableTypeoperation,
371+
LOCKMODElockmode);
370372
staticvoidATExecEnableDisableTrigger(Relationrel,char*trigname,
371373
charfires_when,boolskip_system,LOCKMODElockmode);
372374
staticvoidATExecEnableDisableRule(Relationrel,char*rulename,
@@ -2866,6 +2868,7 @@ AlterTableGetLockLevel(List *cmds)
28662868
caseAT_DropCluster:
28672869
caseAT_SetRelOptions:
28682870
caseAT_ResetRelOptions:
2871+
caseAT_ReplaceRelOptions:
28692872
caseAT_SetOptions:
28702873
caseAT_ResetOptions:
28712874
caseAT_SetStorage:
@@ -3094,8 +3097,9 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
30943097
pass=AT_PASS_MISC;/* doesn't actually matter */
30953098
break;
30963099
caseAT_SetRelOptions:/* SET (...) */
3097-
caseAT_ResetRelOptions:/* RESET (...) */
3098-
ATSimplePermissions(rel,ATT_TABLE |ATT_INDEX);
3100+
caseAT_ResetRelOptions:/* RESET (...) */
3101+
caseAT_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
caseAT_SetRelOptions:/* SET (...) */
3341-
ATExecSetRelOptions(rel, (List*)cmd->def, false,lockmode);
3342-
break;
33433345
caseAT_ResetRelOptions:/* RESET (...) */
3344-
ATExecSetRelOptions(rel, (List*)cmd->def, true,lockmode);
3346+
caseAT_ReplaceRelOptions:/* replace entire option list */
3347+
ATExecSetRelOptions(rel, (List*)cmd->def,cmd->subtype,lockmode);
33453348
break;
3346-
33473349
caseAT_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 (...)orRESET (...)
8276+
*Set, reset,orreplace reloptions.
82758277
*/
82768278
staticvoid
8277-
ATExecSetRelOptions(Relationrel,List*defList,boolisReset,LOCKMODElockmode)
8279+
ATExecSetRelOptions(Relationrel,List*defList,AlterTableTypeoperation,
8280+
LOCKMODElockmode)
82788281
{
82798282
Oidrelid;
82808283
Relationpgclass;
@@ -8288,28 +8291,44 @@ ATExecSetRelOptions(Relation rel, List *defList, bool isReset, LOCKMODE lockmode
82888291
boolrepl_repl[Natts_pg_class];
82898292
staticchar*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
caseRELKIND_RELATION:
83128330
caseRELKIND_TOASTVALUE:
8331+
caseRELKIND_VIEW:
83138332
(void)heap_reloptions(rel->rd_rel->relkind,newOptions, true);
83148333
break;
83158334
caseRELKIND_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
staticvoidcheckViewTupleDesc(TupleDescnewdesc,TupleDescolddesc);
@@ -98,7 +99,7 @@ isViewOnTempTable_walker(Node *node, void *context)
9899
*/
99100
staticOid
100101
DefineVirtualRelation(constRangeVar*relation,List*tlist,boolreplace,
101-
OidnamespaceId)
102+
OidnamespaceId,List*options)
102103
{
103104
OidviewOid;
104105
CreateStmt*createStmt=makeNode(CreateStmt);
@@ -166,6 +167,8 @@ DefineVirtualRelation(const RangeVar *relation, List *tlist, bool replace,
166167
{
167168
Relationrel;
168169
TupleDescdescriptor;
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
intskip=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)
[8]ページ先頭
©2009-2025 Movatter.jp