Skip to content

Commit 491c029

Browse files
sfrostsfrost
committed
Row-Level Security Policies (RLS)
Building on the updatable security-barrier views work, add the ability to define policies on tables to limit the set of rows which are returned from a query and which are allowed to be added to a table. Expressions defined by the policy for filtering are added to the security barrier quals of the query, while expressions defined to check records being added to a table are added to the with-check options of the query. New top-level commands are CREATE/ALTER/DROP POLICY and are controlled by the table owner. Row Security is able to be enabled and disabled by the owner on a per-table basis using ALTER TABLE .. ENABLE/DISABLE ROW SECURITY. Per discussion, ROW SECURITY is disabled on tables by default and must be enabled for policies on the table to be used. If no policies exist on a table with ROW SECURITY enabled, a default-deny policy is used and no records will be visible. By default, row security is applied at all times except for the table owner and the superuser. A new GUC, row_security, is added which can be set to ON, OFF, or FORCE. When set to FORCE, row security will be applied even for the table owner and superusers. When set to OFF, row security will be disabled when allowed and an error will be thrown if the user does not have rights to bypass row security. Per discussion, pg_dump sets row_security = OFF by default to ensure that exports and backups will have all data in the table or will error if there are insufficient privileges to bypass row security. A new option has been added to pg_dump, --enable-row-security, to ask pg_dump to export with row security enabled. A new role capability, BYPASSRLS, which can only be set by the superuser, is added to allow other users to be able to bypass row security using row_security = OFF. Many thanks to the various individuals who have helped with the design, particularly Robert Haas for his feedback. Authors include Craig Ringer, KaiGai Kohei, Adam Brightwell, Dean Rasheed, with additional changes and rework by me. Reviewers have included all of the above, Greg Smith, Jeff McCormick, and Robert Haas.
1 parent e5603a2 commit 491c029

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

82 files changed

+7279
-152
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 100 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -238,6 +238,11 @@
238238
<entry>replication slot information</entry>
239239
</row>
240240

241+
<row>
242+
<entry><link linkend="catalog-pg-rowsecurity"><structname>pg_rowsecurity</structname></link></entry>
243+
<entry>table row-level security policies</entry>
244+
</row>
245+
241246
<row>
242247
<entry><link linkend="catalog-pg-seclabel"><structname>pg_seclabel</structname></link></entry>
243248
<entry>security labels on database objects</entry>
@@ -1935,6 +1940,15 @@
19351940
</entry>
19361941
</row>
19371942

1943+
<row>
1944+
<entry><structfield>relhasrowsecurity</structfield></entry>
1945+
<entry><type>bool</type></entry>
1946+
<entry>
1947+
True if table has row-security enabled; see
1948+
<link linkend="catalog-pg-rowsecurity"><structname>pg_rowsecurity</structname></link> catalog
1949+
</entry>
1950+
</row>
1951+
19381952
<row>
19391953
<entry><structfield>relhassubclass</structfield></entry>
19401954
<entry><type>bool</type></entry>
@@ -5328,6 +5342,86 @@
53285342
</table>
53295343
</sect1>
53305344

5345+
<sect1 id="catalog-pg-rowsecurity">
5346+
<title><structname>pg_rowsecurity</structname></title>
5347+
5348+
<indexterm zone="catalog-pg-rowsecurity">
5349+
<primary>pg_rowsecurity</primary>
5350+
</indexterm>
5351+
5352+
<para>
5353+
The catalog <structname>pg_rowsecurity</structname> stores row-level
5354+
security policies for each table. A policy includes the kind of
5355+
command which it applies to (or all commands), the roles which it
5356+
applies to, the expression to be added as a security-barrier
5357+
qualification to queries which include the table and the expression
5358+
to be added as a with-check option for queries which attempt to add
5359+
new records to the table.
5360+
</para>
5361+
5362+
<table>
5363+
5364+
<title><structname>pg_rowsecurity</structname> Columns</title>
5365+
5366+
<tgroup cols="4">
5367+
<thead>
5368+
<row>
5369+
<entry>Name</entry>
5370+
<entry>Type</entry>
5371+
<entry>References</entry>
5372+
<entry>Description</entry>
5373+
</row>
5374+
</thead>
5375+
5376+
<tbody>
5377+
<row>
5378+
<entry><structfield>rsecpolname</structfield></entry>
5379+
<entry><type>name</type></entry>
5380+
<entry></entry>
5381+
<entry>The name of the row-security policy</entry>
5382+
</row>
5383+
5384+
<row>
5385+
<entry><structfield>rsecrelid</structfield></entry>
5386+
<entry><type>oid</type></entry>
5387+
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
5388+
<entry>The table to which the row-security policy belongs</entry>
5389+
</row>
5390+
5391+
<row>
5392+
<entry><structfield>rseccmd</structfield></entry>
5393+
<entry><type>char</type></entry>
5394+
<entry></entry>
5395+
<entry>The command type to which the row-security policy is applied.</entry>
5396+
</row>
5397+
5398+
<row>
5399+
<entry><structfield>rsecqual</structfield></entry>
5400+
<entry><type>pg_node_tree</type></entry>
5401+
<entry></entry>
5402+
<entry>The expression tree to be added to the security barrier qualifications for queries which use the table.</entry>
5403+
</row>
5404+
5405+
<row>
5406+
<entry><structfield>rsecwithcheck</structfield></entry>
5407+
<entry><type>pg_node_tree</type></entry>
5408+
<entry></entry>
5409+
<entry>The expression tree to be added to the with check qualifications for queries which attempt to add rows to the table.</entry>
5410+
</row>
5411+
5412+
</tbody>
5413+
</tgroup>
5414+
</table>
5415+
5416+
<note>
5417+
<para>
5418+
<literal>pg_class.relhasrowsecurity</literal>
5419+
True if the table has row-security enabled.
5420+
Must be true if the table has a row-security policy in this catalog.
5421+
</para>
5422+
</note>
5423+
5424+
</sect1>
53315425

53325426
<sect1 id="catalog-pg-seclabel">
53335427
<title><structname>pg_seclabel</structname></title>
@@ -9133,6 +9227,12 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
91339227
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.relhastriggers</literal></entry>
91349228
<entry>True if table has (or once had) triggers</entry>
91359229
</row>
9230+
<row>
9231+
<entry><structfield>hasrowsecurity</structfield></entry>
9232+
<entry><type>boolean</type></entry>
9233+
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.relhasrowsecurity</literal></entry>
9234+
<entry>True if table has row security enabled</entry>
9235+
</row>
91369236
</tbody>
91379237
</tgroup>
91389238
</table>

‎doc/src/sgml/config.sgml

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5429,6 +5429,46 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
54295429
</listitem>
54305430
</varlistentry>
54315431

5432+
<varlistentry id="guc-row-security" xreflabel="row_security">
5433+
<term><varname>row_security</varname> (<type>enum</type>)
5434+
<indexterm>
5435+
<primary><varname>row_security</> configuration parameter</primary>
5436+
</indexterm>
5437+
</term>
5438+
<listitem>
5439+
<para>
5440+
This variable controls if row security policies are to be applied
5441+
to queries which are run against tables that have row security enabled.
5442+
The default is 'on'. When set to 'on', all users, except superusers
5443+
and the owner of the table, will have the row policies for the table
5444+
applied to their queries. The table owner and superuser can request
5445+
that row policies be applied to their queries by setting this to
5446+
'force'. Lastly, this can also be set to 'off' which will bypass row
5447+
policies for the table, if possible, and error if not.
5448+
</para>
5449+
5450+
<para>
5451+
For a user who is not a superuser and not the table owner to bypass
5452+
row policies for the table, they must have the BYPASSRLS role attribute.
5453+
If this is set to 'off' and the user queries a table which has row
5454+
policies enabled and the user does not have the right to bypass
5455+
row policies then a permission denied error will be returned.
5456+
</para>
5457+
5458+
<para>
5459+
The allowed values of <varname>row_security</> are
5460+
<literal>on</> (apply normally- not to superuser or table owner),
5461+
<literal>off</> (fail if row security would be applied), and
5462+
<literal>force</> (apply always- even to superuser and table owner).
5463+
</para>
5464+
5465+
<para>
5466+
For more information on row security policies,
5467+
see <xref linkend="SQL-CREATEPOLICY">.
5468+
</para>
5469+
</listitem>
5470+
</varlistentry>
5471+
54325472
<varlistentry id="guc-default-tablespace" xreflabel="default_tablespace">
54335473
<term><varname>default_tablespace</varname> (<type>string</type>)
54345474
<indexterm>

‎doc/src/sgml/event-trigger.sgml

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -195,6 +195,12 @@
195195
<entry align="center"><literal>X</literal></entry>
196196
<entry align="center"><literal>-</literal></entry>
197197
</row>
198+
<row>
199+
<entry align="left"><literal>ALTER POLICY</literal></entry>
200+
<entry align="center"><literal>X</literal></entry>
201+
<entry align="center"><literal>X</literal></entry>
202+
<entry align="center"><literal>-</literal></entry>
203+
</row>
198204
<row>
199205
<entry align="left"><literal>ALTER SCHEMA</literal></entry>
200206
<entry align="center"><literal>X</literal></entry>
@@ -351,6 +357,12 @@
351357
<entry align="center"><literal>X</literal></entry>
352358
<entry align="center"><literal>-</literal></entry>
353359
</row>
360+
<row>
361+
<entry align="left"><literal>CREATE POLICY</literal></entry>
362+
<entry align="center"><literal>X</literal></entry>
363+
<entry align="center"><literal>X</literal></entry>
364+
<entry align="center"><literal>-</literal></entry>
365+
</row>
354366
<row>
355367
<entry align="left"><literal>CREATE RULE</literal></entry>
356368
<entry align="center"><literal>X</literal></entry>
@@ -525,6 +537,12 @@
525537
<entry align="center"><literal>X</literal></entry>
526538
<entry align="center"><literal>X</literal></entry>
527539
</row>
540+
<row>
541+
<entry align="left"><literal>DROP POLICY</literal></entry>
542+
<entry align="center"><literal>X</literal></entry>
543+
<entry align="center"><literal>X</literal></entry>
544+
<entry align="center"><literal>X</literal></entry>
545+
</row>
528546
<row>
529547
<entry align="left"><literal>DROP RULE</literal></entry>
530548
<entry align="center"><literal>X</literal></entry>

‎doc/src/sgml/keywords.sgml

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3422,6 +3422,13 @@
34223422
<entry>non-reserved</entry>
34233423
<entry>non-reserved</entry>
34243424
</row>
3425+
<row>
3426+
<entry><token>POLICY</token></entry>
3427+
<entry>non-reserved</entry>
3428+
<entry></entry>
3429+
<entry></entry>
3430+
<entry></entry>
3431+
</row>
34253432
<row>
34263433
<entry><token>PORTION</token></entry>
34273434
<entry></entry>

‎doc/src/sgml/ref/allfiles.sgml

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,7 @@ Complete list of usable sgml source files in this directory.
2525
<!ENTITY alterOperator SYSTEM "alter_operator.sgml">
2626
<!ENTITY alterOperatorClass SYSTEM "alter_opclass.sgml">
2727
<!ENTITY alterOperatorFamily SYSTEM "alter_opfamily.sgml">
28+
<!ENTITY alterPolicy SYSTEM "alter_policy.sgml">
2829
<!ENTITY alterRole SYSTEM "alter_role.sgml">
2930
<!ENTITY alterRule SYSTEM "alter_rule.sgml">
3031
<!ENTITY alterSchema SYSTEM "alter_schema.sgml">
@@ -69,6 +70,7 @@ Complete list of usable sgml source files in this directory.
6970
<!ENTITY createOperator SYSTEM "create_operator.sgml">
7071
<!ENTITY createOperatorClass SYSTEM "create_opclass.sgml">
7172
<!ENTITY createOperatorFamily SYSTEM "create_opfamily.sgml">
73+
<!ENTITY createPolicy SYSTEM "create_policy.sgml">
7274
<!ENTITY createRole SYSTEM "create_role.sgml">
7375
<!ENTITY createRule SYSTEM "create_rule.sgml">
7476
<!ENTITY createSchema SYSTEM "create_schema.sgml">
@@ -110,6 +112,7 @@ Complete list of usable sgml source files in this directory.
110112
<!ENTITY dropOperatorClass SYSTEM "drop_opclass.sgml">
111113
<!ENTITY dropOperatorFamily SYSTEM "drop_opfamily.sgml">
112114
<!ENTITY dropOwned SYSTEM "drop_owned.sgml">
115+
<!ENTITY dropPolicy SYSTEM "drop_policy.sgml">
113116
<!ENTITY dropRole SYSTEM "drop_role.sgml">
114117
<!ENTITY dropRule SYSTEM "drop_rule.sgml">
115118
<!ENTITY dropSchema SYSTEM "drop_schema.sgml">

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

Lines changed: 135 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,135 @@
1+
<!--
2+
doc/src/sgml/ref/alter_policy.sgml
3+
PostgreSQL documentation
4+
-->
5+
6+
<refentry id="SQL-ALTERPOLICY">
7+
<indexterm zone="sql-alterpolicy">
8+
<primary>ALTER POLICY</primary>
9+
</indexterm>
10+
11+
<refmeta>
12+
<refentrytitle>ALTER POLICY</refentrytitle>
13+
<manvolnum>7</manvolnum>
14+
<refmiscinfo>SQL - Language Statements</refmiscinfo>
15+
</refmeta>
16+
17+
<refnamediv>
18+
<refname>ALTER POLICY</refname>
19+
<refpurpose>change the definition of a row-security policy</refpurpose>
20+
</refnamediv>
21+
22+
<refsynopsisdiv>
23+
<synopsis>
24+
ALTER POLICY <replaceable class="parameter">name</replaceable> ON <replaceable class="parameter">table_name</replaceable>
25+
[ RENAME TO <replaceable class="PARAMETER">new_name</replaceable> ]
26+
[ TO { <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] ]
27+
[ USING ( <replaceable class="parameter">expression</replaceable> ) ]
28+
[ WITH CHECK ( <replaceable class="parameter">check_expression</replaceable> ) ]
29+
</synopsis>
30+
</refsynopsisdiv>
31+
32+
<refsect1>
33+
<title>Description</title>
34+
35+
<para>
36+
<command>ALTER POLICY</command> changes the <replaceable class="parameter">
37+
definition</replaceable> of an existing row-security policy.
38+
</para>
39+
40+
<para>
41+
To use <command>ALTER POLICY</command>, you must own the table that
42+
the policy applies to.
43+
</para>
44+
</refsect1>
45+
46+
<refsect1>
47+
<title>Parameters</title>
48+
49+
<variablelist>
50+
<varlistentry>
51+
<term><replaceable class="parameter">name</replaceable></term>
52+
<listitem>
53+
<para>
54+
The name of an existing policy to alter.
55+
</para>
56+
</listitem>
57+
</varlistentry>
58+
59+
<varlistentry>
60+
<term><replaceable class="parameter">table_name</replaceable></term>
61+
<listitem>
62+
<para>
63+
The name (optionally schema-qualified) of the table that the
64+
policy is on.
65+
</para>
66+
</listitem>
67+
</varlistentry>
68+
69+
<varlistentry>
70+
<term><replaceable class="parameter">new_name</replaceable></term>
71+
<listitem>
72+
<para>
73+
The new name for the policy.
74+
</para>
75+
</listitem>
76+
</varlistentry>
77+
78+
<varlistentry>
79+
<term><replaceable class="parameter">role_name</replaceable></term>
80+
<listitem>
81+
<para>
82+
The role to which the policy applies. Multiple roles can be specified at one time.
83+
To apply the policy to all roles, use <literal>PUBLIC</literal>, which is also
84+
the default.
85+
</para>
86+
</listitem>
87+
</varlistentry>
88+
89+
<varlistentry>
90+
<term><replaceable class="parameter">expression</replaceable></term>
91+
<listitem>
92+
<para>
93+
The USING expression for the policy. This expression will be added as a
94+
security-barrier qualification to queries which use the table
95+
automatically. If multiple policies are being applied for a given
96+
table then they are all combined and added using OR. The USING
97+
expression applies to records which are being retrived from the table.
98+
</para>
99+
</listitem>
100+
</varlistentry>
101+
102+
<varlistentry>
103+
<term><replaceable class="parameter">check_expression</replaceable></term>
104+
<listitem>
105+
<para>
106+
The with-check expression for the policy. This expression will be
107+
added as a WITH CHECK OPTION qualification to queries which use the
108+
table automatically. If multiple policies are being applied for a
109+
given table then they are all combined and added using OR. The WITH
110+
CHECK expression applies to records which are being added to the table.
111+
</para>
112+
</listitem>
113+
</varlistentry>
114+
115+
</variablelist>
116+
</refsect1>
117+
118+
<refsect1>
119+
<title>Compatibility</title>
120+
121+
<para>
122+
<command>ALTER POLICY</command> is a <productname>PostgreSQL</productname> extension.
123+
</para>
124+
</refsect1>
125+
126+
<refsect1>
127+
<title>See Also</title>
128+
129+
<simplelist type="inline">
130+
<member><xref linkend="sql-createpolicy"></member>
131+
<member><xref linkend="sql-droppolicy"></member>
132+
</simplelist>
133+
</refsect1>
134+
135+
</refentry>

0 commit comments

Comments
 (0)