1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>5.8. Row Security Policies</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="ddl-priv.html" title="5.7. Privileges" /><link rel="next" href="ddl-schemas.html" title="5.9. Schemas" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">5.8. Row Security Policies</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="ddl-priv.html" title="5.7. Privileges">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="ddl.html" title="Chapter 5. Data Definition">Up</a></td><th width="60%" align="center">Chapter 5. Data Definition</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="ddl-schemas.html" title="5.9. Schemas">Next</a></td></tr></table><hr /></div><div class="sect1" id="DDL-ROWSECURITY"><div class="titlepage"><div><div><h2 class="title" style="clear: both">5.8. Row Security Policies <a href="#DDL-ROWSECURITY" class="id_link">#</a></h2></div></div></div><a id="id-1.5.4.10.2" class="indexterm"></a><a id="id-1.5.4.10.3" class="indexterm"></a><p>
In addition to the SQL-standard <a class="link" href="ddl-priv.html" title="5.7. Privileges">privilege
system</a> available through <a class="xref" href="sql-grant.html" title="GRANT"><span class="refentrytitle">GRANT</span></a>,
tables can have <em class="firstterm">row security policies</em> that restrict,
on a per-user basis, which rows can be returned by normal queries
or inserted, updated, or deleted by data modification commands.
This feature is also known as <em class="firstterm">Row-Level Security</em>.
By default, tables do not have any policies, so that if a user has
access privileges to a table according to the SQL privilege system,
all rows within it are equally available for querying or updating.
</p><p>
When row security is enabled on a table (with
<a class="link" href="sql-altertable.html" title="ALTER TABLE">ALTER TABLE ... ENABLE ROW LEVEL
SECURITY</a>), all normal access to the table for selecting rows or
modifying rows must be allowed by a row security policy. (However, the
table's owner is typically not subject to row security policies.) If no
policy exists for the table, a default-deny policy is used, meaning that
no rows are visible or can be modified. Operations that apply to the
whole table, such as <code class="command">TRUNCATE</code> and <code class="literal">REFERENCES</code>,
are not subject to row security.
</p><p>
Row security policies can be specific to commands, or to roles, or to
both. A policy can be specified to apply to <code class="literal">ALL</code>
commands, or to <code class="literal">SELECT</code>, <code class="literal">INSERT</code>, <code class="literal">UPDATE</code>,
or <code class="literal">DELETE</code>. Multiple roles can be assigned to a given
policy, and normal role membership and inheritance rules apply.
</p><p>
To specify which rows are visible or modifiable according to a policy,
an expression is required that returns a Boolean result. This
expression will be evaluated for each row prior to any conditions or
functions coming from the user's query. (The only exceptions to this
rule are <code class="literal">leakproof</code> functions, which are guaranteed to
not leak information; the optimizer may choose to apply such functions
ahead of the row-security check.) Rows for which the expression does
not return <code class="literal">true</code> will not be processed. Separate expressions
may be specified to provide independent control over the rows which are
visible and the rows which are allowed to be modified. Policy
expressions are run as part of the query and with the privileges of the
user running the query, although security-definer functions can be used
to access data not available to the calling user.
</p><p>
Superusers and roles with the <code class="literal">BYPASSRLS</code> attribute always
bypass the row security system when accessing a table. Table owners
normally bypass row security as well, though a table owner can choose to
be subject to row security with <a class="link" href="sql-altertable.html" title="ALTER TABLE">ALTER
TABLE ... FORCE ROW LEVEL SECURITY</a>.
</p><p>
Enabling and disabling row security, as well as adding policies to a
table, is always the privilege of the table owner only.
</p><p>
Policies are created using the <a class="xref" href="sql-createpolicy.html" title="CREATE POLICY"><span class="refentrytitle">CREATE POLICY</span></a>
command, altered using the <a class="xref" href="sql-alterpolicy.html" title="ALTER POLICY"><span class="refentrytitle">ALTER POLICY</span></a> command,
and dropped using the <a class="xref" href="sql-droppolicy.html" title="DROP POLICY"><span class="refentrytitle">DROP POLICY</span></a> command. To
enable and disable row security for a given table, use the
<a class="xref" href="sql-altertable.html" title="ALTER TABLE"><span class="refentrytitle">ALTER TABLE</span></a> command.
</p><p>
Each policy has a name and multiple policies can be defined for a
table. As policies are table-specific, each policy for a table must
have a unique name. Different tables may have policies with the
same name.
</p><p>
When multiple policies apply to a given query, they are combined using
either <code class="literal">OR</code> (for permissive policies, which are the
default) or using <code class="literal">AND</code> (for restrictive policies).
This is similar to the rule that a given role has the privileges
of all roles that they are a member of. Permissive vs. restrictive
policies are discussed further below.
</p><p>
As a simple example, here is how to create a policy on
the <code class="literal">account</code> relation to allow only members of
the <code class="literal">managers</code> role to access rows, and only rows of their
accounts:
</p><pre class="programlisting">
CREATE TABLE accounts (manager text, company text, contact_email text);
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
CREATE POLICY account_managers ON accounts TO managers
USING (manager = current_user);
</pre><p>
The policy above implicitly provides a <code class="literal">WITH CHECK</code>
clause identical to its <code class="literal">USING</code> clause, so that the
constraint applies both to rows selected by a command (so a manager
cannot <code class="command">SELECT</code>, <code class="command">UPDATE</code>,
or <code class="command">DELETE</code> existing rows belonging to a different
manager) and to rows modified by a command (so rows belonging to a
different manager cannot be created via <code class="command">INSERT</code>
or <code class="command">UPDATE</code>).
</p><p>
If no role is specified, or the special user name
<code class="literal">PUBLIC</code> is used, then the policy applies to all
users on the system. To allow all users to access only their own row in
a <code class="literal">users</code> table, a simple policy can be used:
</p><pre class="programlisting">
CREATE POLICY user_policy ON users
USING (user_name = current_user);
</pre><p>
This works similarly to the previous example.
</p><p>
To use a different policy for rows that are being added to the table
compared to those rows that are visible, multiple policies can be
combined. This pair of policies would allow all users to view all rows
in the <code class="literal">users</code> table, but only modify their own:
</p><pre class="programlisting">
CREATE POLICY user_sel_policy ON users
FOR SELECT
USING (true);
CREATE POLICY user_mod_policy ON users
USING (user_name = current_user);
</pre><p>
In a <code class="command">SELECT</code> command, these two policies are combined
using <code class="literal">OR</code>, with the net effect being that all rows
can be selected. In other command types, only the second policy applies,
so that the effects are the same as before.
</p><p>
Row security can also be disabled with the <code class="command">ALTER TABLE</code>
command. Disabling row security does not remove any policies that are
defined on the table; they are simply ignored. Then all rows in the
table are visible and modifiable, subject to the standard SQL privileges
system.
</p><p>
Below is a larger example of how this feature can be used in production
environments. The table <code class="literal">passwd</code> emulates a Unix password
file:
</p><pre class="programlisting">
-- Simple passwd-file based example
CREATE TABLE passwd (
user_name text UNIQUE NOT NULL,
pwhash text,
uid int PRIMARY KEY,
gid int NOT NULL,
real_name text NOT NULL,
home_phone text,
extra_info text,
home_dir text NOT NULL,
shell text NOT NULL
);
CREATE ROLE admin; -- Administrator
CREATE ROLE bob; -- Normal user
CREATE ROLE alice; -- Normal user
-- Populate the table
INSERT INTO passwd VALUES
('admin','xxx',0,0,'Admin','111-222-3333',null,'/root','/bin/dash');
INSERT INTO passwd VALUES
('bob','xxx',1,1,'Bob','123-456-7890',null,'/home/bob','/bin/zsh');
INSERT INTO passwd VALUES
('alice','xxx',2,1,'Alice','098-765-4321',null,'/home/alice','/bin/zsh');
-- Be sure to enable row-level security on the table
ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;
-- Create policies
-- Administrator can see all rows and add any rows
CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);
-- Normal users can view all rows
CREATE POLICY all_view ON passwd FOR SELECT USING (true);
-- Normal users can update their own records, but
-- limit which shells a normal user is allowed to set
CREATE POLICY user_mod ON passwd FOR UPDATE
USING (current_user = user_name)
WITH CHECK (
current_user = user_name AND
shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh')
);
-- Allow admin all normal rights
GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;
-- Users only get select access on public columns
GRANT SELECT
(user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)
ON passwd TO public;
-- Allow users to update certain columns
GRANT UPDATE
(pwhash, real_name, home_phone, extra_info, shell)
ON passwd TO public;
</pre><p>
As with any security settings, it's important to test and ensure that
the system is behaving as expected. Using the example above, this
demonstrates that the permission system is working properly.
</p><pre class="programlisting">
-- admin can view all rows and fields
postgres=> set role admin;
SET
postgres=> table passwd;
user_name | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell
-----------+--------+-----+-----+-----------+--------------+------------+-------------+-----------
admin | xxx | 0 | 0 | Admin | 111-222-3333 | | /root | /bin/dash
bob | xxx | 1 | 1 | Bob | 123-456-7890 | | /home/bob | /bin/zsh
alice | xxx | 2 | 1 | Alice | 098-765-4321 | | /home/alice | /bin/zsh
(3 rows)
-- Test what Alice is able to do
postgres=> set role alice;
SET
postgres=> table passwd;
ERROR: permission denied for table passwd
postgres=> select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd;
user_name | real_name | home_phone | extra_info | home_dir | shell
-----------+-----------+--------------+------------+-------------+-----------
admin | Admin | 111-222-3333 | | /root | /bin/dash
bob | Bob | 123-456-7890 | | /home/bob | /bin/zsh
alice | Alice | 098-765-4321 | | /home/alice | /bin/zsh
(3 rows)
postgres=> update passwd set user_name = 'joe';
ERROR: permission denied for table passwd
-- Alice is allowed to change her own real_name, but no others
postgres=> update passwd set real_name = 'Alice Doe';
UPDATE 1
postgres=> update passwd set real_name = 'John Doe' where user_name = 'admin';
UPDATE 0
postgres=> update passwd set shell = '/bin/xx';
ERROR: new row violates WITH CHECK OPTION for "passwd"
postgres=> delete from passwd;
ERROR: permission denied for table passwd
postgres=> insert into passwd (user_name) values ('xxx');
ERROR: permission denied for table passwd
-- Alice can change her own password; RLS silently prevents updating other rows
postgres=> update passwd set pwhash = 'abc';
UPDATE 1
</pre><p>
All of the policies constructed thus far have been permissive policies,
meaning that when multiple policies are applied they are combined using
the <span class="quote">“<span class="quote">OR</span>”</span> Boolean operator. While permissive policies can be constructed
to only allow access to rows in the intended cases, it can be simpler to
combine permissive policies with restrictive policies (which the records
must pass and which are combined using the <span class="quote">“<span class="quote">AND</span>”</span> Boolean operator).
Building on the example above, we add a restrictive policy to require
the administrator to be connected over a local Unix socket to access the
records of the <code class="literal">passwd</code> table:
</p><pre class="programlisting">
CREATE POLICY admin_local_only ON passwd AS RESTRICTIVE TO admin
USING (pg_catalog.inet_client_addr() IS NULL);
</pre><p>
We can then see that an administrator connecting over a network will not
see any records, due to the restrictive policy:
</p><pre class="programlisting">
=> SELECT current_user;
current_user
--------------
admin
(1 row)
=> select inet_client_addr();
inet_client_addr
------------------
127.0.0.1
(1 row)
=> TABLE passwd;
user_name | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell
-----------+--------+-----+-----+-----------+------------+------------+----------+-------
(0 rows)
=> UPDATE passwd set pwhash = NULL;
UPDATE 0
</pre><p>
Referential integrity checks, such as unique or primary key constraints
and foreign key references, always bypass row security to ensure that
data integrity is maintained. Care must be taken when developing
schemas and row level policies to avoid <span class="quote">“<span class="quote">covert channel</span>”</span> leaks of
information through such referential integrity checks.
</p><p>
In some contexts it is important to be sure that row security is
not being applied. For example, when taking a backup, it could be
disastrous if row security silently caused some rows to be omitted
from the backup. In such a situation, you can set the
<a class="xref" href="runtime-config-client.html#GUC-ROW-SECURITY">row_security</a> configuration parameter
to <code class="literal">off</code>. This does not in itself bypass row security;
what it does is throw an error if any query's results would get filtered
by a policy. The reason for the error can then be investigated and
fixed.
</p><p>
In the examples above, the policy expressions consider only the current
values in the row to be accessed or updated. This is the simplest and
best-performing case; when possible, it's best to design row security
applications to work this way. If it is necessary to consult other rows
or other tables to make a policy decision, that can be accomplished using
sub-<code class="command">SELECT</code>s, or functions that contain <code class="command">SELECT</code>s,
in the policy expressions. Be aware however that such accesses can
create race conditions that could allow information leakage if care is
not taken. As an example, consider the following table design:
</p><pre class="programlisting">
-- definition of privilege groups
CREATE TABLE groups (group_id int PRIMARY KEY,
group_name text NOT NULL);
INSERT INTO groups VALUES
(1, 'low'),
(2, 'medium'),
(5, 'high');
GRANT ALL ON groups TO alice; -- alice is the administrator
GRANT SELECT ON groups TO public;
-- definition of users' privilege levels
CREATE TABLE users (user_name text PRIMARY KEY,
group_id int NOT NULL REFERENCES groups);
INSERT INTO users VALUES
('alice', 5),
('bob', 2),
('mallory', 2);
GRANT ALL ON users TO alice;
GRANT SELECT ON users TO public;
-- table holding the information to be protected
CREATE TABLE information (info text,
group_id int NOT NULL REFERENCES groups);
INSERT INTO information VALUES
('barely secret', 1),
('slightly secret', 2),
('very secret', 5);
ALTER TABLE information ENABLE ROW LEVEL SECURITY;
-- a row should be visible to/updatable by users whose security group_id is
-- greater than or equal to the row's group_id
CREATE POLICY fp_s ON information FOR SELECT
USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));
CREATE POLICY fp_u ON information FOR UPDATE
USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));
-- we rely only on RLS to protect the information table
GRANT ALL ON information TO public;
</pre><p>
Now suppose that <code class="literal">alice</code> wishes to change the <span class="quote">“<span class="quote">slightly
secret</span>”</span> information, but decides that <code class="literal">mallory</code> should not
be trusted with the new content of that row, so she does:
</p><pre class="programlisting">
BEGIN;
UPDATE users SET group_id = 1 WHERE user_name = 'mallory';
UPDATE information SET info = 'secret from mallory' WHERE group_id = 2;
COMMIT;
</pre><p>
That looks safe; there is no window wherein <code class="literal">mallory</code> should be
able to see the <span class="quote">“<span class="quote">secret from mallory</span>”</span> string. However, there is
a race condition here. If <code class="literal">mallory</code> is concurrently doing,
say,
</p><pre class="programlisting">
SELECT * FROM information WHERE group_id = 2 FOR UPDATE;
</pre><p>
and her transaction is in <code class="literal">READ COMMITTED</code> mode, it is possible
for her to see <span class="quote">“<span class="quote">secret from mallory</span>”</span>. That happens if her
transaction reaches the <code class="structname">information</code> row just
after <code class="literal">alice</code>'s does. It blocks waiting
for <code class="literal">alice</code>'s transaction to commit, then fetches the updated
row contents thanks to the <code class="literal">FOR UPDATE</code> clause. However, it
does <span class="emphasis"><em>not</em></span> fetch an updated row for the
implicit <code class="command">SELECT</code> from <code class="structname">users</code>, because that
sub-<code class="command">SELECT</code> did not have <code class="literal">FOR UPDATE</code>; instead
the <code class="structname">users</code> row is read with the snapshot taken at the start
of the query. Therefore, the policy expression tests the old value
of <code class="literal">mallory</code>'s privilege level and allows her to see the
updated row.
</p><p>
There are several ways around this problem. One simple answer is to use
<code class="literal">SELECT ... FOR SHARE</code> in sub-<code class="command">SELECT</code>s in row
security policies. However, that requires granting <code class="literal">UPDATE</code>
privilege on the referenced table (here <code class="structname">users</code>) to the
affected users, which might be undesirable. (But another row security
policy could be applied to prevent them from actually exercising that
privilege; or the sub-<code class="command">SELECT</code> could be embedded into a security
definer function.) Also, heavy concurrent use of row share locks on the
referenced table could pose a performance problem, especially if updates
of it are frequent. Another solution, practical if updates of the
referenced table are infrequent, is to take an
<code class="literal">ACCESS EXCLUSIVE</code> lock on the
referenced table when updating it, so that no concurrent transactions
could be examining old row values. Or one could just wait for all
concurrent transactions to end after committing an update of the
referenced table and before making changes that rely on the new security
situation.
</p><p>
For additional details see <a class="xref" href="sql-createpolicy.html" title="CREATE POLICY"><span class="refentrytitle">CREATE POLICY</span></a>
and <a class="xref" href="sql-altertable.html" title="ALTER TABLE"><span class="refentrytitle">ALTER TABLE</span></a>.
</p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="ddl-priv.html" title="5.7. Privileges">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="ddl.html" title="Chapter 5. Data Definition">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="ddl-schemas.html" title="5.9. Schemas">Next</a></td></tr><tr><td width="40%" align="left" valign="top">5.7. Privileges </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 5.9. Schemas</td></tr></table></div></body></html>
|
