diff options
Diffstat (limited to 'doc/src/sgml/html/ddl-rowsecurity.html')
| -rw-r--r-- | doc/src/sgml/html/ddl-rowsecurity.html | 382 |
1 files changed, 382 insertions, 0 deletions
diff --git a/doc/src/sgml/html/ddl-rowsecurity.html b/doc/src/sgml/html/ddl-rowsecurity.html new file mode 100644 index 0000000..ec0be3b --- /dev/null +++ b/doc/src/sgml/html/ddl-rowsecurity.html @@ -0,0 +1,382 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!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.2 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> + In addition to the SQL-standard <a class="link" href="ddl-priv.html" title="5.7. Privileges">privilege + system</a> available through <a class="xref" href="sql-grant.html" title="GRANT"><span class="refentrytitle">GRANT</span></a>, + tables can have <em class="firstterm">row security policies</em> that restrict, + on a per-user basis, which rows can be returned by normal queries + or inserted, updated, or deleted by data modification commands. + This feature is also known as <em class="firstterm">Row-Level Security</em>. + By default, tables do not have any policies, so that if a user has + access privileges to a table according to the SQL privilege system, + all rows within it are equally available for querying or updating. + </p><p> + When row security is enabled on a table (with + <a class="link" href="sql-altertable.html" title="ALTER TABLE">ALTER TABLE ... ENABLE ROW LEVEL + SECURITY</a>), all normal access to the table for selecting rows or + modifying rows must be allowed by a row security policy. (However, the + table's owner is typically not subject to row security policies.) If no + policy exists for the table, a default-deny policy is used, meaning that + no rows are visible or can be modified. Operations that apply to the + whole table, such as <code class="command">TRUNCATE</code> and <code class="literal">REFERENCES</code>, + are not subject to row security. + </p><p> + Row security policies can be specific to commands, or to roles, or to + both. A policy can be specified to apply to <code class="literal">ALL</code> + commands, or to <code class="literal">SELECT</code>, <code class="literal">INSERT</code>, <code class="literal">UPDATE</code>, + or <code class="literal">DELETE</code>. Multiple roles can be assigned to a given + policy, and normal role membership and inheritance rules apply. + </p><p> + To specify which rows are visible or modifiable according to a policy, + an expression is required that returns a Boolean result. This + expression will be evaluated for each row prior to any conditions or + functions coming from the user's query. (The only exceptions to this + rule are <code class="literal">leakproof</code> functions, which are guaranteed to + not leak information; the optimizer may choose to apply such functions + ahead of the row-security check.) Rows for which the expression does + not return <code class="literal">true</code> will not be processed. Separate expressions + may be specified to provide independent control over the rows which are + visible and the rows which are allowed to be modified. Policy + expressions are run as part of the query and with the privileges of the + user running the query, although security-definer functions can be used + to access data not available to the calling user. + </p><p> + Superusers and roles with the <code class="literal">BYPASSRLS</code> attribute always + bypass the row security system when accessing a table. Table owners + normally bypass row security as well, though a table owner can choose to + be subject to row security with <a class="link" href="sql-altertable.html" title="ALTER TABLE">ALTER + TABLE ... FORCE ROW LEVEL SECURITY</a>. + </p><p> + Enabling and disabling row security, as well as adding policies to a + table, is always the privilege of the table owner only. + </p><p> + Policies are created using the <a class="xref" href="sql-createpolicy.html" title="CREATE POLICY"><span class="refentrytitle">CREATE POLICY</span></a> + command, altered using the <a class="xref" href="sql-alterpolicy.html" title="ALTER POLICY"><span class="refentrytitle">ALTER POLICY</span></a> command, + and dropped using the <a class="xref" href="sql-droppolicy.html" title="DROP POLICY"><span class="refentrytitle">DROP POLICY</span></a> command. To + enable and disable row security for a given table, use the + <a class="xref" href="sql-altertable.html" title="ALTER TABLE"><span class="refentrytitle">ALTER TABLE</span></a> command. + </p><p> + Each policy has a name and multiple policies can be defined for a + table. As policies are table-specific, each policy for a table must + have a unique name. Different tables may have policies with the + same name. + </p><p> + When multiple policies apply to a given query, they are combined using + either <code class="literal">OR</code> (for permissive policies, which are the + default) or using <code class="literal">AND</code> (for restrictive policies). + This is similar to the rule that a given role has the privileges + of all roles that they are a member of. Permissive vs. restrictive + policies are discussed further below. + </p><p> + As a simple example, here is how to create a policy on + the <code class="literal">account</code> relation to allow only members of + the <code class="literal">managers</code> role to access rows, and only rows of their + accounts: + </p><pre class="programlisting"> +CREATE TABLE accounts (manager text, company text, contact_email text); + +ALTER TABLE accounts ENABLE ROW LEVEL SECURITY; + +CREATE POLICY account_managers ON accounts TO managers + USING (manager = current_user); +</pre><p> + The policy above implicitly provides a <code class="literal">WITH CHECK</code> + clause identical to its <code class="literal">USING</code> clause, so that the + constraint applies both to rows selected by a command (so a manager + cannot <code class="command">SELECT</code>, <code class="command">UPDATE</code>, + or <code class="command">DELETE</code> existing rows belonging to a different + manager) and to rows modified by a command (so rows belonging to a + different manager cannot be created via <code class="command">INSERT</code> + or <code class="command">UPDATE</code>). + </p><p> + If no role is specified, or the special user name + <code class="literal">PUBLIC</code> is used, then the policy applies to all + users on the system. To allow all users to access only their own row in + a <code class="literal">users</code> table, a simple policy can be used: + </p><pre class="programlisting"> +CREATE POLICY user_policy ON users + USING (user_name = current_user); +</pre><p> + This works similarly to the previous example. + </p><p> + To use a different policy for rows that are being added to the table + compared to those rows that are visible, multiple policies can be + combined. This pair of policies would allow all users to view all rows + in the <code class="literal">users</code> table, but only modify their own: + </p><pre class="programlisting"> +CREATE POLICY user_sel_policy ON users + FOR SELECT + USING (true); +CREATE POLICY user_mod_policy ON users + USING (user_name = current_user); +</pre><p> + In a <code class="command">SELECT</code> command, these two policies are combined + using <code class="literal">OR</code>, with the net effect being that all rows + can be selected. In other command types, only the second policy applies, + so that the effects are the same as before. + </p><p> + Row security can also be disabled with the <code class="command">ALTER TABLE</code> + command. Disabling row security does not remove any policies that are + defined on the table; they are simply ignored. Then all rows in the + table are visible and modifiable, subject to the standard SQL privileges + system. + </p><p> + Below is a larger example of how this feature can be used in production + environments. The table <code class="literal">passwd</code> emulates a Unix password + file: + </p><pre class="programlisting"> +-- Simple passwd-file based example +CREATE TABLE passwd ( + user_name text UNIQUE NOT NULL, + pwhash text, + uid int PRIMARY KEY, + gid int NOT NULL, + real_name text NOT NULL, + home_phone text, + extra_info text, + home_dir text NOT NULL, + shell text NOT NULL +); + +CREATE ROLE admin; -- Administrator +CREATE ROLE bob; -- Normal user +CREATE ROLE alice; -- Normal user + +-- Populate the table +INSERT INTO passwd VALUES + ('admin','xxx',0,0,'Admin','111-222-3333',null,'/root','/bin/dash'); +INSERT INTO passwd VALUES + ('bob','xxx',1,1,'Bob','123-456-7890',null,'/home/bob','/bin/zsh'); +INSERT INTO passwd VALUES + ('alice','xxx',2,1,'Alice','098-765-4321',null,'/home/alice','/bin/zsh'); + +-- Be sure to enable row-level security on the table +ALTER TABLE passwd ENABLE ROW LEVEL SECURITY; + +-- Create policies +-- Administrator can see all rows and add any rows +CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true); +-- Normal users can view all rows +CREATE POLICY all_view ON passwd FOR SELECT USING (true); +-- Normal users can update their own records, but +-- limit which shells a normal user is allowed to set +CREATE POLICY user_mod ON passwd FOR UPDATE + USING (current_user = user_name) + WITH CHECK ( + current_user = user_name AND + shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh') + ); + +-- Allow admin all normal rights +GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin; +-- Users only get select access on public columns +GRANT SELECT + (user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell) + ON passwd TO public; +-- Allow users to update certain columns +GRANT UPDATE + (pwhash, real_name, home_phone, extra_info, shell) + ON passwd TO public; +</pre><p> + As with any security settings, it's important to test and ensure that + the system is behaving as expected. Using the example above, this + demonstrates that the permission system is working properly. + </p><pre class="programlisting"> +-- admin can view all rows and fields +postgres=> set role admin; +SET +postgres=> table passwd; + user_name | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell +-----------+--------+-----+-----+-----------+--------------+------------+-------------+----------- + admin | xxx | 0 | 0 | Admin | 111-222-3333 | | /root | /bin/dash + bob | xxx | 1 | 1 | Bob | 123-456-7890 | | /home/bob | /bin/zsh + alice | xxx | 2 | 1 | Alice | 098-765-4321 | | /home/alice | /bin/zsh +(3 rows) + +-- Test what Alice is able to do +postgres=> set role alice; +SET +postgres=> table passwd; +ERROR: permission denied for table passwd +postgres=> select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd; + user_name | real_name | home_phone | extra_info | home_dir | shell +-----------+-----------+--------------+------------+-------------+----------- + admin | Admin | 111-222-3333 | | /root | /bin/dash + bob | Bob | 123-456-7890 | | /home/bob | /bin/zsh + alice | Alice | 098-765-4321 | | /home/alice | /bin/zsh +(3 rows) + +postgres=> update passwd set user_name = 'joe'; +ERROR: permission denied for table passwd +-- Alice is allowed to change her own real_name, but no others +postgres=> update passwd set real_name = 'Alice Doe'; +UPDATE 1 +postgres=> update passwd set real_name = 'John Doe' where user_name = 'admin'; +UPDATE 0 +postgres=> update passwd set shell = '/bin/xx'; +ERROR: new row violates WITH CHECK OPTION for "passwd" +postgres=> delete from passwd; +ERROR: permission denied for table passwd +postgres=> insert into passwd (user_name) values ('xxx'); +ERROR: permission denied for table passwd +-- Alice can change her own password; RLS silently prevents updating other rows +postgres=> update passwd set pwhash = 'abc'; +UPDATE 1 +</pre><p> + All of the policies constructed thus far have been permissive policies, + meaning that when multiple policies are applied they are combined using + the <span class="quote">“<span class="quote">OR</span>”</span> Boolean operator. While permissive policies can be constructed + to only allow access to rows in the intended cases, it can be simpler to + combine permissive policies with restrictive policies (which the records + must pass and which are combined using the <span class="quote">“<span class="quote">AND</span>”</span> Boolean operator). + Building on the example above, we add a restrictive policy to require + the administrator to be connected over a local Unix socket to access the + records of the <code class="literal">passwd</code> table: + </p><pre class="programlisting"> +CREATE POLICY admin_local_only ON passwd AS RESTRICTIVE TO admin + USING (pg_catalog.inet_client_addr() IS NULL); +</pre><p> + We can then see that an administrator connecting over a network will not + see any records, due to the restrictive policy: + </p><pre class="programlisting"> +=> SELECT current_user; + current_user +-------------- + admin +(1 row) + +=> select inet_client_addr(); + inet_client_addr +------------------ + 127.0.0.1 +(1 row) + +=> TABLE passwd; + user_name | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell +-----------+--------+-----+-----+-----------+------------+------------+----------+------- +(0 rows) + +=> UPDATE passwd set pwhash = NULL; +UPDATE 0 +</pre><p> + Referential integrity checks, such as unique or primary key constraints + and foreign key references, always bypass row security to ensure that + data integrity is maintained. Care must be taken when developing + schemas and row level policies to avoid <span class="quote">“<span class="quote">covert channel</span>”</span> leaks of + information through such referential integrity checks. + </p><p> + In some contexts it is important to be sure that row security is + not being applied. For example, when taking a backup, it could be + disastrous if row security silently caused some rows to be omitted + from the backup. In such a situation, you can set the + <a class="xref" href="runtime-config-client.html#GUC-ROW-SECURITY">row_security</a> configuration parameter + to <code class="literal">off</code>. This does not in itself bypass row security; + what it does is throw an error if any query's results would get filtered + by a policy. The reason for the error can then be investigated and + fixed. + </p><p> + In the examples above, the policy expressions consider only the current + values in the row to be accessed or updated. This is the simplest and + best-performing case; when possible, it's best to design row security + applications to work this way. If it is necessary to consult other rows + or other tables to make a policy decision, that can be accomplished using + sub-<code class="command">SELECT</code>s, or functions that contain <code class="command">SELECT</code>s, + in the policy expressions. Be aware however that such accesses can + create race conditions that could allow information leakage if care is + not taken. As an example, consider the following table design: + </p><pre class="programlisting"> +-- definition of privilege groups +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; +</pre><p> + Now suppose that <code class="literal">alice</code> wishes to change the <span class="quote">“<span class="quote">slightly + secret</span>”</span> information, but decides that <code class="literal">mallory</code> should not + be trusted with the new content of that row, so she does: + </p><pre class="programlisting"> +BEGIN; +UPDATE users SET group_id = 1 WHERE user_name = 'mallory'; +UPDATE information SET info = 'secret from mallory' WHERE group_id = 2; +COMMIT; +</pre><p> + That looks safe; there is no window wherein <code class="literal">mallory</code> should be + able to see the <span class="quote">“<span class="quote">secret from mallory</span>”</span> string. However, there is + a race condition here. If <code class="literal">mallory</code> is concurrently doing, + say, +</p><pre class="programlisting"> +SELECT * FROM information WHERE group_id = 2 FOR UPDATE; +</pre><p> + and her transaction is in <code class="literal">READ COMMITTED</code> mode, it is possible + for her to see <span class="quote">“<span class="quote">secret from mallory</span>”</span>. That happens if her + transaction reaches the <code class="structname">information</code> row just + after <code class="literal">alice</code>'s does. It blocks waiting + for <code class="literal">alice</code>'s transaction to commit, then fetches the updated + row contents thanks to the <code class="literal">FOR UPDATE</code> clause. However, it + does <span class="emphasis"><em>not</em></span> fetch an updated row for the + implicit <code class="command">SELECT</code> from <code class="structname">users</code>, because that + sub-<code class="command">SELECT</code> did not have <code class="literal">FOR UPDATE</code>; instead + the <code class="structname">users</code> row is read with the snapshot taken at the start + of the query. Therefore, the policy expression tests the old value + of <code class="literal">mallory</code>'s privilege level and allows her to see the + updated row. + </p><p> + There are several ways around this problem. One simple answer is to use + <code class="literal">SELECT ... FOR SHARE</code> in sub-<code class="command">SELECT</code>s in row + security policies. However, that requires granting <code class="literal">UPDATE</code> + privilege on the referenced table (here <code class="structname">users</code>) 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-<code class="command">SELECT</code> 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 + <code class="literal">ACCESS EXCLUSIVE</code> lock on the + referenced table when updating it, so that no concurrent transactions + could be examining old row values. Or one could just wait for all + concurrent transactions to end after committing an update of the + referenced table and before making changes that rely on the new security + situation. + </p><p> + For additional details see <a class="xref" href="sql-createpolicy.html" title="CREATE POLICY"><span class="refentrytitle">CREATE POLICY</span></a> + and <a class="xref" href="sql-altertable.html" title="ALTER TABLE"><span class="refentrytitle">ALTER TABLE</span></a>. + </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.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 5.9. Schemas</td></tr></table></div></body></html>
\ No newline at end of file |