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

Commit093129c

Browse files
committed
Add support for restrictive RLS policies
We have had support for restrictive RLS policies since 9.5, but theywere only available through extensions which use the appropriate hooks.This adds support into the grammer, catalog, psql and pg_dump forrestrictive RLS policies, thus reducing the cases where an extension isnecessary.In passing, also move away from using "AND"d and "OR"d in comments.As pointed out by Alvaro, it's not really appropriate to attemptto make verbs out of "AND" and "OR", so reword those comments whichattempted to.Reviewed By: Jeevan Chalke, Dean RasheedDiscussion:https://postgr.es/m/20160901063404.GY4028@tamriel.snowman.net
1 parent2bbdc68 commit093129c

File tree

21 files changed

+665
-150
lines changed

21 files changed

+665
-150
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4747,6 +4747,13 @@
47474747
or <literal>*</> for all</entry>
47484748
</row>
47494749

4750+
<row>
4751+
<entry><structfield>polpermissive</structfield></entry>
4752+
<entry><type>boolean</type></entry>
4753+
<entry></entry>
4754+
<entry>Is the policy permissive or restrictive?</entry>
4755+
</row>
4756+
47504757
<row>
47514758
<entry><structfield>polroles</structfield></entry>
47524759
<entry><type>oid[]</type></entry>
@@ -8437,6 +8444,12 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
84378444
<entry><literal><link linkend="catalog-pg-policy"><structname>pg_policy</structname></link>.polname</literal></entry>
84388445
<entry>Name of policy</entry>
84398446
</row>
8447+
<row>
8448+
<entry><structfield>polpermissive</structfield></entry>
8449+
<entry><type>text</type></entry>
8450+
<entry></entry>
8451+
<entry>Is the policy permissive or restrictive?</entry>
8452+
</row>
84408453
<row>
84418454
<entry><structfield>roles</structfield></entry>
84428455
<entry><type>name[]</type></entry>

‎doc/src/sgml/ddl.sgml

Lines changed: 55 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1599,9 +1599,11 @@ REVOKE ALL ON accounts FROM PUBLIC;
15991599

16001600
<para>
16011601
When multiple policies apply to a given query, they are combined using
1602-
<literal>OR</literal>, so that a row is accessible if any policy allows
1603-
it. This is similar to the rule that a given role has the privileges
1604-
of all roles that they are a member of.
1602+
either <literal>OR</literal> (for permissive policies, which are the
1603+
default) or using <literal>AND</literal> (for restrictive policies).
1604+
This is similar to the rule that a given role has the privileges
1605+
of all roles that they are a member of. Permissive vs. restrictive
1606+
policies are discussed further below.
16051607
</para>
16061608

16071609
<para>
@@ -1761,6 +1763,56 @@ ERROR: permission denied for relation passwd
17611763
-- Alice can change her own password; RLS silently prevents updating other rows
17621764
postgres=&gt; update passwd set pwhash = 'abc';
17631765
UPDATE 1
1766+
</programlisting>
1767+
1768+
<para>
1769+
All of the policies constructed thus far have been permissive policies,
1770+
meaning that when multiple policies are applied they are combined using
1771+
the "OR" boolean operator. While permissive policies can be constructed
1772+
to only allow access to rows in the intended cases, it can be simpler to
1773+
combine permissive policies with restrictive policies (which the records
1774+
must pass and which are combined using the "AND" boolean operator).
1775+
Building on the example above, we add a restrictive policy to require
1776+
the administrator to be connected over a local unix socket to access the
1777+
records of the passwd table:
1778+
</para>
1779+
1780+
<programlisting>
1781+
CREATE POLICY admin_local_only ON passwd AS RESTRICTIVE TO admin
1782+
USING (pg_catalog.inet_client_addr() IS NULL);
1783+
</programlisting>
1784+
1785+
<para>
1786+
We can then see that an administrator connecting over a network will not
1787+
see any records, due to the restrictive policy:
1788+
</para>
1789+
1790+
<programlisting>
1791+
=&gt; SELECT current_user;
1792+
current_user
1793+
--------------
1794+
admin
1795+
(1 row)
1796+
1797+
=&gt; select inet_client_addr();
1798+
inet_client_addr
1799+
------------------
1800+
127.0.0.1
1801+
(1 row)
1802+
1803+
=&gt; SELECT current_user;
1804+
current_user
1805+
--------------
1806+
admin
1807+
(1 row)
1808+
1809+
=&gt; TABLE passwd;
1810+
user_name | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell
1811+
-----------+--------+-----+-----+-----------+------------+------------+----------+-------
1812+
(0 rows)
1813+
1814+
=&gt; UPDATE passwd set pwhash = NULL;
1815+
UPDATE 0
17641816
</programlisting>
17651817

17661818
<para>

‎doc/src/sgml/ref/alter_policy.sgml

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -35,7 +35,12 @@ ALTER POLICY <replaceable class="parameter">name</replaceable> ON <replaceable c
3535

3636
<para>
3737
<command>ALTER POLICY</command> changes the definition of an existing
38-
row-level security policy.
38+
row-level security policy. Note that <command>ALTER POLICY</command>
39+
only allows the set of roles to which the policy applies and the
40+
<literal>USING</literal> and <literal>WITH CHECK</literal> expressions to
41+
be modified. To change other properties of a policy, such as the command
42+
to which it applies or whether it is permissive or restrictive, the policy
43+
must be dropped and recreated.
3944
</para>
4045

4146
<para>

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

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -22,6 +22,7 @@ PostgreSQL documentation
2222
<refsynopsisdiv>
2323
<synopsis>
2424
CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable class="parameter">table_name</replaceable>
25+
[ AS { PERMISSIVE | RESTRICTIVE } ]
2526
[ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
2627
[ TO { <replaceable class="parameter">role_name</replaceable> | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
2728
[ USING ( <replaceable class="parameter">using_expression</replaceable> ) ]
@@ -119,6 +120,33 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
119120
</listitem>
120121
</varlistentry>
121122

123+
<varlistentry>
124+
<term><replaceable class="parameter">PERMISSIVE</replaceable></term>
125+
<listitem>
126+
<para>
127+
Specify that the policy is to be created as a permissive policy.
128+
All permissive policies which are applicable to a given query will
129+
be combined together using the boolean "OR" operator. By creating
130+
permissive policies, administrators can add to the set of records
131+
which can be accessed. Policies are PERMISSIVE by default.
132+
</para>
133+
</listitem>
134+
</varlistentry>
135+
136+
<varlistentry>
137+
<term><replaceable class="parameter">RESTRICTIVE</replaceable></term>
138+
<listitem>
139+
<para>
140+
Specify that the policy is to be created as a restrictive policy.
141+
All restrictive policies which are applicable to a given query will
142+
be combined together using the boolean "AND" operator. By creating
143+
restrictive policies, administrators can reduce the set of records
144+
which can be accessed as all restrictive policies must be passed for
145+
each record.
146+
</para>
147+
</listitem>
148+
</varlistentry>
149+
122150
<varlistentry>
123151
<term><replaceable class="parameter">command</replaceable></term>
124152
<listitem>
@@ -390,6 +418,16 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
390418
keys) instead of keys with external meanings.
391419
</para>
392420

421+
<para>
422+
Note that there needs to be at least one permissive policy to grant
423+
access to records before restrictive policies can be usefully used to
424+
reduce that access. If only restrictive policies exist, then no records
425+
will be accessible. When a mix of permissive and restrictive policies
426+
are present, a record is only accessible if at least one of the
427+
permissive policies passes, in addition to all the restrictive
428+
policies.
429+
</para>
430+
393431
<para>
394432
Generally, the system will enforce filter conditions imposed using
395433
security policies prior to qualifications that appear in user queries,

‎src/backend/catalog/system_views.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -75,6 +75,12 @@ CREATE VIEW pg_policies AS
7575
N.nspnameAS schemaname,
7676
C.relnameAS tablename,
7777
pol.polnameAS policyname,
78+
CASE
79+
WHENpol.polpermissive THEN
80+
'PERMISSIVE'
81+
ELSE
82+
'RESTRICTIVE'
83+
ENDAS permissive,
7884
CASE
7985
WHENpol.polroles='{0}' THEN
8086
string_to_array('public','')

‎src/backend/commands/policy.c

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -235,6 +235,7 @@ RelationBuildRowSecurity(Relation relation)
235235
{
236236
Datumvalue_datum;
237237
charcmd_value;
238+
boolpermissive_value;
238239
Datumroles_datum;
239240
char*qual_value;
240241
Expr*qual_expr;
@@ -257,6 +258,12 @@ RelationBuildRowSecurity(Relation relation)
257258
Assert(!isnull);
258259
cmd_value=DatumGetChar(value_datum);
259260

261+
/* Get policy permissive or restrictive */
262+
value_datum=heap_getattr(tuple,Anum_pg_policy_polpermissive,
263+
RelationGetDescr(catalog),&isnull);
264+
Assert(!isnull);
265+
permissive_value=DatumGetBool(value_datum);
266+
260267
/* Get policy name */
261268
value_datum=heap_getattr(tuple,Anum_pg_policy_polname,
262269
RelationGetDescr(catalog),&isnull);
@@ -298,6 +305,7 @@ RelationBuildRowSecurity(Relation relation)
298305
policy=palloc0(sizeof(RowSecurityPolicy));
299306
policy->policy_name=pstrdup(policy_name_value);
300307
policy->polcmd=cmd_value;
308+
policy->permissive=permissive_value;
301309
policy->roles=DatumGetArrayTypePCopy(roles_datum);
302310
policy->qual=copyObject(qual_expr);
303311
policy->with_check_qual=copyObject(with_check_qual);
@@ -796,6 +804,7 @@ CreatePolicy(CreatePolicyStmt *stmt)
796804
values[Anum_pg_policy_polname-1]=DirectFunctionCall1(namein,
797805
CStringGetDatum(stmt->policy_name));
798806
values[Anum_pg_policy_polcmd-1]=CharGetDatum(polcmd);
807+
values[Anum_pg_policy_polpermissive-1]=BoolGetDatum(stmt->permissive);
799808
values[Anum_pg_policy_polroles-1]=PointerGetDatum(role_ids);
800809

801810
/* Add qual if present. */

‎src/backend/nodes/copyfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4166,6 +4166,7 @@ _copyCreatePolicyStmt(const CreatePolicyStmt *from)
41664166
COPY_STRING_FIELD(policy_name);
41674167
COPY_NODE_FIELD(table);
41684168
COPY_STRING_FIELD(cmd_name);
4169+
COPY_SCALAR_FIELD(permissive);
41694170
COPY_NODE_FIELD(roles);
41704171
COPY_NODE_FIELD(qual);
41714172
COPY_NODE_FIELD(with_check);

‎src/backend/nodes/equalfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2125,6 +2125,7 @@ _equalCreatePolicyStmt(const CreatePolicyStmt *a, const CreatePolicyStmt *b)
21252125
COMPARE_STRING_FIELD(policy_name);
21262126
COMPARE_NODE_FIELD(table);
21272127
COMPARE_STRING_FIELD(cmd_name);
2128+
COMPARE_SCALAR_FIELD(permissive);
21282129
COMPARE_NODE_FIELD(roles);
21292130
COMPARE_NODE_FIELD(qual);
21302131
COMPARE_NODE_FIELD(with_check);

‎src/backend/parser/gram.y

Lines changed: 33 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -332,6 +332,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
332332
%type<str>all_OpMathOp
333333

334334
%type<str>row_security_cmdRowSecurityDefaultForCmd
335+
%type<boolean>RowSecurityDefaultPermissive
335336
%type<node>RowSecurityOptionalWithCheckRowSecurityOptionalExpr
336337
%type<list>RowSecurityDefaultToRoleRowSecurityOptionalToRole
337338

@@ -4628,26 +4629,30 @@ AlterUserMappingStmt: ALTER USER MAPPING FOR auth_ident SERVER name alter_generi
46284629
/*****************************************************************************
46294630
*
46304631
*QUERIES:
4631-
*CREATE POLICY name ON table [FOR cmd] [TO role, ...]
4632-
*[USING (qual)] [WITH CHECK (with_check)]
4632+
*CREATE POLICY name ON table
4633+
*[AS { PERMISSIVE | RESTRICTIVE } ]
4634+
*[FOR { SELECT | INSERT | UPDATE | DELETE } ]
4635+
*[TO role, ...]
4636+
*[USING (qual)] [WITH CHECK (with check qual)]
46334637
*ALTER POLICY name ON table [TO role, ...]
4634-
*[USING (qual)] [WITH CHECK (with_check)]
4638+
*[USING (qual)] [WITH CHECK (with check qual)]
46354639
*DROP POLICY name ON table
46364640
*
46374641
*****************************************************************************/
46384642

46394643
CreatePolicyStmt:
4640-
CREATEPOLICYnameONqualified_nameRowSecurityDefaultForCmd
4641-
RowSecurityDefaultToRoleRowSecurityOptionalExpr
4642-
RowSecurityOptionalWithCheck
4644+
CREATEPOLICYnameONqualified_nameRowSecurityDefaultPermissive
4645+
RowSecurityDefaultForCmdRowSecurityDefaultToRole
4646+
RowSecurityOptionalExprRowSecurityOptionalWithCheck
46434647
{
46444648
CreatePolicyStmt *n = makeNode(CreatePolicyStmt);
46454649
n->policy_name =$3;
46464650
n->table =$5;
4647-
n->cmd_name =$6;
4648-
n->roles =$7;
4649-
n->qual =$8;
4650-
n->with_check =$9;
4651+
n->permissive =$6;
4652+
n->cmd_name =$7;
4653+
n->roles =$8;
4654+
n->qual =$9;
4655+
n->with_check =$10;
46514656
$$ = (Node *) n;
46524657
}
46534658
;
@@ -4711,6 +4716,24 @@ RowSecurityOptionalToRole:
47114716
|/* EMPTY*/{$$ =NULL; }
47124717
;
47134718

4719+
RowSecurityDefaultPermissive:
4720+
ASIDENT
4721+
{
4722+
if (strcmp($2,"permissive") ==0)
4723+
$$ =true;
4724+
elseif (strcmp($2,"restrictive") ==0)
4725+
$$ =false;
4726+
else
4727+
ereport(ERROR,
4728+
(errcode(ERRCODE_SYNTAX_ERROR),
4729+
errmsg("unrecognized row security option\"%s\"", $2),
4730+
errhint("Only PERMISSIVE or RESTRICTIVE policies are supported currently."),
4731+
parser_errposition(@2)));
4732+
4733+
}
4734+
|/* EMPTY*/{$$ =true; }
4735+
;
4736+
47144737
RowSecurityDefaultForCmd:
47154738
FORrow_security_cmd{$$ =$2; }
47164739
|/* EMPTY*/{$$ ="all"; }

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp