Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
ddl-rowsecurity.html382 linesDownload Raw Back to html
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=&gt; set role admin;186SET187postgres=&gt; 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=&gt; set role alice;197SET198postgres=&gt; table passwd;199ERROR:  permission denied for table passwd200postgres=&gt; 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=&gt; update passwd set user_name = 'joe';209ERROR:  permission denied for table passwd210-- Alice is allowed to change her own real_name, but no others211postgres=&gt; update passwd set real_name = 'Alice Doe';212UPDATE 1213postgres=&gt; update passwd set real_name = 'John Doe' where user_name = 'admin';214UPDATE 0215postgres=&gt; update passwd set shell = '/bin/xx';216ERROR:  new row violates WITH CHECK OPTION for "passwd"217postgres=&gt; delete from passwd;218ERROR:  permission denied for table passwd219postgres=&gt; 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=&gt; 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=&gt; SELECT current_user;242 current_user243--------------244 admin245(1 row)246 247=&gt; select inet_client_addr();248 inet_client_addr249------------------250 127.0.0.1251(1 row)252 253=&gt; TABLE passwd;254 user_name | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell255-----------+--------+-----+-----+-----------+------------+------------+----------+-------256(0 rows)257 258=&gt; 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 &lt;= (SELECT group_id FROM users WHERE user_name = current_user));326CREATE POLICY fp_u ON information FOR UPDATE327  USING (group_id &lt;= (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>
codekingpro/portable-devtools · Team Ai