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;
</programlisting>

  <para>
   Now suppose that <literal>alice</literal> wishes to change the <quote>slightly
   secret</quote> information, but decides that <literal>mallory</literal> should not
   be trusted with the new content of that row, so she does:
  </para>

<programlisting>
BEGIN;
UPDATE users SET group_id = 1 WHERE user_name = 'mallory';
UPDATE information SET info = 'secret from mallory' WHERE group_id = 2;
COMMIT;
</programlisting>

  <para>
   That looks safe; there is no window wherein <literal>mallory</literal> should be
   able to see the <quote>secret from mallory</quote> string.  However, there is
   a race condition here.  If <literal>mallory</literal> is concurrently doing,
   say,
<programlisting>
SELECT * FROM information WHERE group_id = 2 FOR UPDATE;
</programlisting>
   and her transaction is in <literal>READ COMMITTED</literal> mode, it is possible
   for her to see <quote>secret from mallory</quote>.  That happens if her
   transaction reaches the <structname>information</structname> row just
   after <literal>alice</literal>'s does.  It blocks waiting
   for <literal>alice</literal>'s transaction to commit, then fetches the updated
   row contents thanks to the <literal>FOR UPDATE</literal> clause.  However, it
   does <emphasis>not</emphasis> fetch an updated row for the
   implicit <command>SELECT</command> from <structname>users</structname>, because that
   sub-<command>SELECT</command> did not have <literal>FOR UPDATE</literal>; instead
   the <structname>users</structname> row is read with the snapshot taken at the start
   of the query.  Therefore, the policy expression tests the old value
   of <literal>mallory</literal>'s privilege level and allows her to see the
   updated row.
  </para>

  <para>
   There are several ways around this problem.  One simple answer is to use
   <literal>SELECT ... FOR SHARE</literal> in sub-<command>SELECT</command>s in row
   security policies.  However, that requires granting <literal>UPDATE</literal>
   privilege on the referenced table (here <structname>users</structname>) 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-<command>SELECT</command> 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
   <literal>ACCESS EXCLUSIVE</literal> lock on the
   referenced