@@ -1781,6 +1781,120 @@ UPDATE 1
17811781 fixed.
17821782 </para>
17831783
1784+ <para>
1785+ In the examples above, the policy expressions consider only the current
1786+ values in the row to be accessed or updated. This is the simplest and
1787+ best-performing case; when possible, it's best to design row security
1788+ applications to work this way. If it is necessary to consult other rows
1789+ or other tables to make a policy decision, that can be accomplished using
1790+ sub-<command>SELECT</>s, or functions that contain <command>SELECT</>s,
1791+ in the policy expressions. Be aware however that such accesses can
1792+ create race conditions that could allow information leakage if care is
1793+ not taken. As an example, consider the following table design:
1794+ </para>
1795+
1796+ <programlisting>
1797+ -- definition of privilege groups
1798+ CREATE TABLE groups (group_id int PRIMARY KEY,
1799+ group_name text NOT NULL);
1800+
1801+ INSERT INTO groups VALUES
1802+ (1, 'low'),
1803+ (2, 'medium'),
1804+ (5, 'high');
1805+
1806+ GRANT ALL ON groups TO alice; -- alice is the administrator
1807+ GRANT SELECT ON groups TO public;
1808+
1809+ -- definition of users' privilege levels
1810+ CREATE TABLE users (user_name text PRIMARY KEY,
1811+ group_id int NOT NULL REFERENCES groups);
1812+
1813+ INSERT INTO users VALUES
1814+ ('alice', 5),
1815+ ('bob', 2),
1816+ ('mallory', 2);
1817+
1818+ GRANT ALL ON users TO alice;
1819+ GRANT SELECT ON users TO public;
1820+
1821+ -- table holding the information to be protected
1822+ CREATE TABLE information (info text,
1823+ group_id int NOT NULL REFERENCES groups);
1824+
1825+ INSERT INTO information VALUES
1826+ ('barely secret', 1),
1827+ ('slightly secret', 2),
1828+ ('very secret', 5);
1829+
1830+ ALTER TABLE information ENABLE ROW LEVEL SECURITY;
1831+
1832+ -- a row should be visible to/updatable by users whose security group_id is
1833+ -- greater than or equal to the row's group_id
1834+ CREATE POLICY fp_s ON information FOR SELECT
1835+ USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));
1836+ CREATE POLICY fp_u ON information FOR UPDATE
1837+ USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));
1838+
1839+ -- we rely only on RLS to protect the information table
1840+ GRANT ALL ON information TO public;
1841+ </programlisting>
1842+
1843+ <para>
1844+ Now suppose that <literal>alice</> wishes to change the <quote>slightly
1845+ secret</> information, but decides that <literal>mallory</> should not
1846+ be trusted with the new content of that row, so she does:
1847+ </para>
1848+
1849+ <programlisting>
1850+ BEGIN;
1851+ UPDATE users SET group_id = 1 WHERE user_name = 'mallory';
1852+ UPDATE information SET info = 'secret from mallory' WHERE group_id = 2;
1853+ COMMIT;
1854+ </programlisting>
1855+
1856+ <para>
1857+ That looks safe; there is no window wherein <literal>mallory</> should be
1858+ able to see the <quote>secret from mallory</> string. However, there is
1859+ a race condition here. If <literal>mallory</> is concurrently doing,
1860+ say,
1861+ <programlisting>
1862+ SELECT * FROM information WHERE group_id = 2 FOR UPDATE;
1863+ </programlisting>
1864+ and her transaction is in <literal>READ COMMITTED</> mode, it is possible
1865+ for her to see <quote>secret from mallory</>. That happens if her
1866+ transaction reaches the <structname>information</> row just
1867+ after <literal>alice</>'s does. It blocks waiting
1868+ for <literal>alice</>'s transaction to commit, then fetches the updated
1869+ row contents thanks to the <literal>FOR UPDATE</> clause. However, it
1870+ does <emphasis>not</> fetch an updated row for the
1871+ implicit <command>SELECT</> from <structname>users</>, because that
1872+ sub-<command>SELECT</> did not have <literal>FOR UPDATE</>; instead
1873+ the <structname>users</> row is read with the snapshot taken at the start
1874+ of the query. Therefore, the policy expression tests the old value
1875+ of <literal>mallory</>'s privilege level and allows her to see the
1876+ updated row.
1877+ </para>
1878+
1879+ <para>
1880+ There are several ways around this problem. One simple answer is to use
1881+ <literal>SELECT ... FOR SHARE</> in sub-<command>SELECT</>s in row
1882+ security policies. However, that requires granting <literal>UPDATE</>
1883+ privilege on the referenced table (here <structname>users</>) to the
1884+ affected users, which might be undesirable. (But another row security
1885+ policy could be applied to prevent them from actually exercising that
1886+ privilege; or the sub-<command>SELECT</> could be embedded into a security
1887+ definer function.) Also, heavy concurrent use of row share locks on the
1888+ referenced table could pose a performance problem, especially if updates
1889+ of it are frequent. Another solution, practical if updates of the
1890+ referenced table are infrequent, is to take an exclusive lock on the
1891+ referenced table when updating it, so that no concurrent transactions
1892+ could be examining old row values. Or one could just wait for all
1893+ concurrent transactions to end after committing an update of the
1894+ referenced table and before making changes that rely on the new security
1895+ situation.
1896+ </para>
1897+
17841898 <para>
17851899 For additional details see <xref linkend="sql-createpolicy">
17861900 and <xref linkend="sql-altertable">.