codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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.3 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>3 In addition to the SQL-standard <a class="link" href="ddl-priv.html" title="5.7. Privileges">privilege4 system</a> available through <a class="xref" href="sql-grant.html" title="GRANT"><span class="refentrytitle">GRANT</span></a>,5 tables can have <em class="firstterm">row security policies</em> that restrict,6 on a per-user basis, which rows can be returned by normal queries7 or inserted, updated, or deleted by data modification commands.8 This feature is also known as <em class="firstterm">Row-Level Security</em>.9 By default, tables do not have any policies, so that if a user has10 access privileges to a table according to the SQL privilege system,11 all rows within it are equally available for querying or updating.12 </p><p>13 When row security is enabled on a table (with14 <a class="link" href="sql-altertable.html" title="ALTER TABLE">ALTER TABLE ... ENABLE ROW LEVEL15 SECURITY</a>), all normal access to the table for selecting rows or16 modifying rows must be allowed by a row security policy. (However, the17 table's owner is typically not subject to row security policies.) If no18 policy exists for the table, a default-deny policy is used, meaning that19 no rows are visible or can be modified. Operations that apply to the20 whole table, such as <code class="command">TRUNCATE</code> and <code class="literal">REFERENCES</code>,21 are not subject to row security.22 </p><p>23 Row security policies can be specific to commands, or to roles, or to24 both. A policy can be specified to apply to <code class="literal">ALL</code>25 commands, or to <code class="literal">SELECT</code>, <code class="literal">INSERT</code>, <code class="literal">UPDATE</code>,26 or <code class="literal">DELETE</code>. Multiple roles can be assigned to a given27 policy, and normal role membership and inheritance rules apply.28 </p><p>29 To specify which rows are visible or modifiable according to a policy,30 an expression is required that returns a Boolean result. This31 expression will be evaluated for each row prior to any conditions or32 functions coming from the user's query. (The only exceptions to this33 rule are <code class="literal">leakproof</code> functions, which are guaranteed to34 not leak information; the optimizer may choose to apply such functions35 ahead of the row-security check.) Rows for which the expression does36 not return <code class="literal">true</code> will not be processed. Separate expressions37 may be specified to provide independent control over the rows which are38 visible and the rows which are allowed to be modified. Policy39 expressions are run as part of the query and with the privileges of the40 user running the query, although security-definer functions can be used41 to access data not available to the calling user.42 </p><p>43 Superusers and roles with the <code class="literal">BYPASSRLS</code> attribute always44 bypass the row security system when accessing a table. Table owners45 normally bypass row security as well, though a table owner can choose to46 be subject to row security with <a class="link" href="sql-altertable.html" title="ALTER TABLE">ALTER47 TABLE ... FORCE ROW LEVEL SECURITY</a>.48 </p><p>49 Enabling and disabling row security, as well as adding policies to a50 table, is always the privilege of the table owner only.51 </p><p>52 Policies are created using the <a class="xref" href="sql-createpolicy.html" title="CREATE POLICY"><span class="refentrytitle">CREATE POLICY</span></a>53 command, altered using the <a class="xref" href="sql-alterpolicy.html" title="ALTER POLICY"><span class="refentrytitle">ALTER POLICY</span></a> command,54 and dropped using the <a class="xref" href="sql-droppolicy.html" title="DROP POLICY"><span class="refentrytitle">DROP POLICY</span></a> command. To55 enable and disable row security for a given table, use the56 <a class="xref" href="sql-altertable.html" title="ALTER TABLE"><span class="refentrytitle">ALTER TABLE</span></a> command.57 </p><p>58 Each policy has a name and multiple policies can be defined for a59 table. As policies are table-specific, each policy for a table must60 have a unique name. Different tables may have policies with the61 same name.62 </p><p>63 When multiple policies apply to a given query, they are combined using64 either <code class="literal">OR</code> (for permissive policies, which are the65 default) or using <code class="literal">AND</code> (for restrictive policies).66 This is similar to the rule that a given role has the privileges67 of all roles that they are a member of. Permissive vs. restrictive68 policies are discussed further below.69 </p><p>70 As a simple example, here is how to create a policy on71 the <code class="literal">account</code> relation to allow only members of72 the <code class="literal">managers</code> role to access rows, and only rows of their73 accounts:74 </p><pre class="programlisting">75CREATE TABLE accounts (manager text, company text, contact_email text);76 77ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;78 79CREATE POLICY account_managers ON accounts TO managers80 USING (manager = current_user);81</pre><p>82 The policy above implicitly provides a <code class="literal">WITH CHECK</code>83 clause identical to its <code class="literal">USING</code> clause, so that the84 constraint applies both to rows selected by a command (so a manager85 cannot <code class="command">SELECT</code>, <code class="command">UPDATE</code>,86 or <code class="command">DELETE</code> existing rows belonging to a different87 manager) and to rows modified by a command (so rows belonging to a88 different manager cannot be created via <code class="command">INSERT</code>89 or <code class="command">UPDATE</code>).90 </p><p>91 If no role is specified, or the special user name92 <code class="literal">PUBLIC</code> is used, then the policy applies to all93 users on the system. To allow all users to access only their own row in94 a <code class="literal">users</code> table, a simple policy can be used:95 </p><pre class="programlisting">96CREATE POLICY user_policy ON users97 USING (user_name = current_user);98</pre><p>99 This works similarly to the previous example.100 </p><p>101 To use a different policy for rows that are being added to the table102 compared to those rows that are visible, multiple policies can be103 combined. This pair of policies would allow all users to view all rows104 in the <code class="literal">users</code> table, but only modify their own:105 </p><pre class="programlisting">106CREATE POLICY user_sel_policy ON users107 FOR SELECT108 USING (true);109CREATE POLICY user_mod_policy ON users110 USING (user_name = current_user);111</pre><p>112 In a <code class="command">SELECT</code> command, these two policies are combined113 using <code class="literal">OR</code>, with the net effect being that all rows114 can be selected. In other command types, only the second policy applies,115 so that the effects are the same as before.116 </p><p>117 Row security can also be disabled with the <code class="command">ALTER TABLE</code>118 command. Disabling row security does not remove any policies that are119 defined on the table; they are simply ignored. Then all rows in the120 table are visible and modifiable, subject to the standard SQL privileges121 system.122 </p><p>123 Below is a larger example of how this feature can be used in production124 environments. The table <code class="literal">passwd</code> emulates a Unix password125 file:126 </p><pre class="programlisting">127-- Simple passwd-file based example128CREATE TABLE passwd (129 user_name text UNIQUE NOT NULL,130 pwhash text,131 uid int PRIMARY KEY,132 gid int NOT NULL,133 real_name text NOT NULL,134 home_phone text,135 extra_info text,136 home_dir text NOT NULL,137 shell text NOT NULL138);139 140CREATE ROLE admin; -- Administrator141CREATE ROLE bob; -- Normal user142CREATE ROLE alice; -- Normal user143 144-- Populate the table145INSERT INTO passwd VALUES146 ('admin','xxx',0,0,'Admin','111-222-3333',null,'/root','/bin/dash');147INSERT INTO passwd VALUES148 ('bob','xxx',1,1,'Bob','123-456-7890',null,'/home/bob','/bin/zsh');149INSERT INTO passwd VALUES150 ('alice','xxx',2,1,'Alice','098-765-4321',null,'/home/alice','/bin/zsh');151 152-- Be sure to enable row-level security on the table153ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;154 155-- Create policies156-- Administrator can see all rows and add any rows157CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);158-- Normal users can view all rows159CREATE POLICY all_view ON passwd FOR SELECT USING (true);160-- Normal users can update their own records, but161-- limit which shells a normal user is allowed to set162CREATE POLICY user_mod ON passwd FOR UPDATE163 USING (current_user = user_name)164 WITH CHECK (165 current_user = user_name AND166 shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh')167 );168 169-- Allow admin all normal rights170GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;171-- Users only get select access on public columns172GRANT SELECT173 (user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)174 ON passwd TO public;175-- Allow users to update certain columns176GRANT UPDATE177 (pwhash, real_name, home_phone, extra_info, shell)178 ON passwd TO public;179</pre><p>180 As with any security settings, it's important to test and ensure that181 the system is behaving as expected. Using the example above, this182 demonstrates that the permission system is working properly.183 </p><pre class="programlisting">184-- admin can view all rows and fields185postgres=> set role admin;186SET187postgres=> table passwd;188 user_name | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell189-----------+--------+-----+-----+-----------+--------------+------------+-------------+-----------190 admin | xxx | 0 | 0 | Admin | 111-222-3333 | | /root | /bin/dash191 bob | xxx | 1 | 1 | Bob | 123-456-7890 | | /home/bob | /bin/zsh192 alice | xxx | 2 | 1 | Alice | 098-765-4321 | | /home/alice | /bin/zsh193(3 rows)194 195-- Test what Alice is able to do196postgres=> set role alice;197SET198postgres=> table passwd;199ERROR: permission denied for table passwd200postgres=> select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd;201 user_name | real_name | home_phone | extra_info | home_dir | shell202-----------+-----------+--------------+------------+-------------+-----------203 admin | Admin | 111-222-3333 | | /root | /bin/dash204 bob | Bob | 123-456-7890 | | /home/bob | /bin/zsh205 alice | Alice | 098-765-4321 | | /home/alice | /bin/zsh206(3 rows)207 208postgres=> update passwd set user_name = 'joe';209ERROR: permission denied for table passwd210-- Alice is allowed to change her own real_name, but no others211postgres=> update passwd set real_name = 'Alice Doe';212UPDATE 1213postgres=> update passwd set real_name = 'John Doe' where user_name = 'admin';214UPDATE 0215postgres=> update passwd set shell = '/bin/xx';216ERROR: new row violates WITH CHECK OPTION for "passwd"217postgres=> delete from passwd;218ERROR: permission denied for table passwd219postgres=> insert into passwd (user_name) values ('xxx');220ERROR: permission denied for table passwd221-- Alice can change her own password; RLS silently prevents updating other rows222postgres=> update passwd set pwhash = 'abc';223UPDATE 1224</pre><p>225 All of the policies constructed thus far have been permissive policies,226 meaning that when multiple policies are applied they are combined using227 the <span class="quote">“<span class="quote">OR</span>”</span> Boolean operator. While permissive policies can be constructed228 to only allow access to rows in the intended cases, it can be simpler to229 combine permissive policies with restrictive policies (which the records230 must pass and which are combined using the <span class="quote">“<span class="quote">AND</span>”</span> Boolean operator).231 Building on the example above, we add a restrictive policy to require232 the administrator to be connected over a local Unix socket to access the233 records of the <code class="literal">passwd</code> table:234 </p><pre class="programlisting">235CREATE POLICY admin_local_only ON passwd AS RESTRICTIVE TO admin236 USING (pg_catalog.inet_client_addr() IS NULL);237</pre><p>238 We can then see that an administrator connecting over a network will not239 see any records, due to the restrictive policy:240 </p><pre class="programlisting">241=> SELECT current_user;242 current_user243--------------244 admin245(1 row)246 247=> select inet_client_addr();248 inet_client_addr249------------------250 127.0.0.1251(1 row)252 253=> TABLE passwd;254 user_name | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell255-----------+--------+-----+-----+-----------+------------+------------+----------+-------256(0 rows)257 258=> UPDATE passwd set pwhash = NULL;259UPDATE 0260</pre><p>261 Referential integrity checks, such as unique or primary key constraints262 and foreign key references, always bypass row security to ensure that263 data integrity is maintained. Care must be taken when developing264 schemas and row level policies to avoid <span class="quote">“<span class="quote">covert channel</span>”</span> leaks of265 information through such referential integrity checks.266 </p><p>267 In some contexts it is important to be sure that row security is268 not being applied. For example, when taking a backup, it could be269 disastrous if row security silently caused some rows to be omitted270 from the backup. In such a situation, you can set the271 <a class="xref" href="runtime-config-client.html#GUC-ROW-SECURITY">row_security</a> configuration parameter272 to <code class="literal">off</code>. This does not in itself bypass row security;273 what it does is throw an error if any query's results would get filtered274 by a policy. The reason for the error can then be investigated and275 fixed.276 </p><p>277 In the examples above, the policy expressions consider only the current278 values in the row to be accessed or updated. This is the simplest and279 best-performing case; when possible, it's best to design row security280 applications to work this way. If it is necessary to consult other rows281 or other tables to make a policy decision, that can be accomplished using282 sub-<code class="command">SELECT</code>s, or functions that contain <code class="command">SELECT</code>s,283 in the policy expressions. Be aware however that such accesses can284 create race conditions that could allow information leakage if care is285 not taken. As an example, consider the following table design:286 </p><pre class="programlisting">287-- definition of privilege groups288CREATE TABLE groups (group_id int PRIMARY KEY,289 group_name text NOT NULL);290 291INSERT INTO groups VALUES292 (1, 'low'),293 (2, 'medium'),294 (5, 'high');295 296GRANT ALL ON groups TO alice; -- alice is the administrator297GRANT SELECT ON groups TO public;298 299-- definition of users' privilege levels300CREATE TABLE users (user_name text PRIMARY KEY,301 group_id int NOT NULL REFERENCES groups);302 303INSERT INTO users VALUES304 ('alice', 5),305 ('bob', 2),306 ('mallory', 2);307 308GRANT ALL ON users TO alice;309GRANT SELECT ON users TO public;310 311-- table holding the information to be protected312CREATE TABLE information (info text,313 group_id int NOT NULL REFERENCES groups);314 315INSERT INTO information VALUES316 ('barely secret', 1),317 ('slightly secret', 2),318 ('very secret', 5);319 320ALTER TABLE information ENABLE ROW LEVEL SECURITY;321 322-- a row should be visible to/updatable by users whose security group_id is323-- greater than or equal to the row's group_id324CREATE POLICY fp_s ON information FOR SELECT325 USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));326CREATE POLICY fp_u ON information FOR UPDATE327 USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));328 329-- we rely only on RLS to protect the information table330GRANT ALL ON information TO public;331</pre><p>332 Now suppose that <code class="literal">alice</code> wishes to change the <span class="quote">“<span class="quote">slightly333 secret</span>”</span> information, but decides that <code class="literal">mallory</code> should not334 be trusted with the new content of that row, so she does:335 </p><pre class="programlisting">336BEGIN;337UPDATE users SET group_id = 1 WHERE user_name = 'mallory';338UPDATE information SET info = 'secret from mallory' WHERE group_id = 2;339COMMIT;340</pre><p>341 That looks safe; there is no window wherein <code class="literal">mallory</code> should be342 able to see the <span class="quote">“<span class="quote">secret from mallory</span>”</span> string. However, there is343 a race condition here. If <code class="literal">mallory</code> is concurrently doing,344 say,345</p><pre class="programlisting">346SELECT * FROM information WHERE group_id = 2 FOR UPDATE;347</pre><p>348 and her transaction is in <code class="literal">READ COMMITTED</code> mode, it is possible349 for her to see <span class="quote">“<span class="quote">secret from mallory</span>”</span>. That happens if her350 transaction reaches the <code class="structname">information</code> row just351 after <code class="literal">alice</code>'s does. It blocks waiting352 for <code class="literal">alice</code>'s transaction to commit, then fetches the updated353 row contents thanks to the <code class="literal">FOR UPDATE</code> clause. However, it354 does <span class="emphasis"><em>not</em></span> fetch an updated row for the355 implicit <code class="command">SELECT</code> from <code class="structname">users</code>, because that356 sub-<code class="command">SELECT</code> did not have <code class="literal">FOR UPDATE</code>; instead357 the <code class="structname">users</code> row is read with the snapshot taken at the start358 of the query. Therefore, the policy expression tests the old value359 of <code class="literal">mallory</code>'s privilege level and allows her to see the360 updated row.361 </p><p>362 There are several ways around this problem. One simple answer is to use363 <code class="literal">SELECT ... FOR SHARE</code> in sub-<code class="command">SELECT</code>s in row364 security policies. However, that requires granting <code class="literal">UPDATE</code>365 privilege on the referenced table (here <code class="structname">users</code>) to the366 affected users, which might be undesirable. (But another row security367 policy could be applied to prevent them from actually exercising that368 privilege; or the sub-<code class="command">SELECT</code> could be embedded into a security369 definer function.) Also, heavy concurrent use of row share locks on the370 referenced table could pose a performance problem, especially if updates371 of it are frequent. Another solution, practical if updates of the372 referenced table are infrequent, is to take an373 <code class="literal">ACCESS EXCLUSIVE</code> lock on the374 referenced table when updating it, so that no concurrent transactions375 could be examining old row values. Or one could just wait for all376 concurrent transactions to end after committing an update of the377 referenced table and before making changes that rely on the new security378 situation.379 </p><p>380 For additional details see <a class="xref" href="sql-createpolicy.html" title="CREATE POLICY"><span class="refentrytitle">CREATE POLICY</span></a>381 and <a class="xref" href="sql-altertable.html" title="ALTER TABLE"><span class="refentrytitle">ALTER TABLE</span></a>.382 </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.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 5.9. Schemas</td></tr></table></div></body></html>