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