Adding upstream version 13.4.upstream/13.4 upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
author: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-05-04 12:19:15 +0000
committer: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-05-04 12:19:15 +0000
commit: 6eb9c5a5657d1fe77b55cc261450f3538d35a94d (patch)
tree: 657d8194422a5daccecfd42d654b8a245ef7b4c8 /doc/src/sgml/html/rules-privileges.html
parent: Initial commit. (diff)
download: postgresql-13-upstream.tar.xz
postgresql-13-upstream.zip
1 files changed, 157 insertions, 0 deletions
diff --git a/doc/src/sgml/html/rules-privileges.html b/doc/src/sgml/html/rules-privileges.html
new file mode 100644
index 0000000..41bb854
--- /dev/null
+++ b/doc/src/sgml/html/rules-privileges.html
@@ -0,0 +1,157 @@
+<?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>40.5. Rules and Privileges</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 V1.79.1" /><link rel="prev" href="rules-update.html" title="40.4. Rules on INSERT, UPDATE, and DELETE" /><link rel="next" href="rules-status.html" title="40.6. Rules and Command Status" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">40.5. Rules and Privileges</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="rules-update.html" title="40.4. Rules on INSERT, UPDATE, and DELETE">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="rules.html" title="Chapter 40. The Rule System">Up</a></td><th width="60%" align="center">Chapter 40. The Rule System</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="rules-status.html" title="40.6. Rules and Command Status">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="RULES-PRIVILEGES"><div class="titlepage"><div><div><h2 class="title" style="clear: both">40.5. Rules and Privileges</h2></div></div></div><a id="id-1.8.6.10.2" class="indexterm"></a><a id="id-1.8.6.10.3" class="indexterm"></a><p>
+    Due to rewriting of queries by the <span class="productname">PostgreSQL</span>
+    rule system, other tables/views than those used in the original
+    query get accessed. When update rules are used, this can include write access
+    to tables.
+</p><p>
+    Rewrite rules don't have a separate owner. The owner of
+    a relation (table or view) is automatically the owner of the
+    rewrite rules that are defined for it.
+    The <span class="productname">PostgreSQL</span> rule system changes the
+    behavior of the default access control system. Relations that
+    are used due to rules get checked against the
+    privileges of the rule owner, not the user invoking the rule.
+    This means that users only need the required privileges
+    for the tables/views that are explicitly named in their queries.
+</p><p>
+    For example: A user has a list of phone numbers where some of
+    them are private, the others are of interest for the assistant of the office.
+    The user can construct the following:
+
+</p><pre class="programlisting">
+CREATE TABLE phone_data (person text, phone text, private boolean);
+CREATE VIEW phone_number AS
+    SELECT person, CASE WHEN NOT private THEN phone END AS phone
+    FROM phone_data;
+GRANT SELECT ON phone_number TO assistant;
+</pre><p>
+
+    Nobody except that user (and the database superusers) can access the
+    <code class="literal">phone_data</code> table. But because of the <code class="command">GRANT</code>,
+    the assistant can run a <code class="command">SELECT</code> on the
+    <code class="literal">phone_number</code> view. The rule system will rewrite the
+    <code class="command">SELECT</code> from <code class="literal">phone_number</code> into a
+    <code class="command">SELECT</code> from <code class="literal">phone_data</code>.
+    Since the user is the owner of
+    <code class="literal">phone_number</code> and therefore the owner of the rule, the
+    read access to <code class="literal">phone_data</code> is now checked against the user's
+    privileges and the query is permitted. The check for accessing
+    <code class="literal">phone_number</code> is also performed, but this is done
+    against the invoking user, so nobody but the user and the
+    assistant can use it.
+</p><p>
+    The privileges are checked rule by rule. So the assistant is for now the
+    only one who can see the public phone numbers. But the assistant can set up
+    another view and grant access to that to the public. Then, anyone
+    can see the <code class="literal">phone_number</code> data through the assistant's view.
+    What the assistant cannot do is to create a view that directly
+    accesses <code class="literal">phone_data</code>.  (Actually the assistant can, but it will not work since
+    every access will be denied during the permission checks.)
+    And as soon as the user notices that the assistant opened
+    their <code class="literal">phone_number</code> view, the user can revoke the assistant's access. Immediately, any
+    access to the assistant's view would fail.
+</p><p>
+    One might think that this rule-by-rule checking is a security
+    hole, but in fact it isn't.   But if it did not work this way, the assistant
+    could set up a table with the same columns as <code class="literal">phone_number</code> and
+    copy the data to there once per day. Then it's the assistant's own data and
+    the assistant can grant access to everyone they want. A
+    <code class="command">GRANT</code> command means, <span class="quote">“<span class="quote">I trust you</span>”</span>.
+    If someone you trust does the thing above, it's time to
+    think it over and then use <code class="command">REVOKE</code>.
+</p><p>
+    Note that while views can be used to hide the contents of certain
+    columns using the technique shown above, they cannot be used to reliably
+    conceal the data in unseen rows unless the
+    <code class="literal">security_barrier</code> flag has been set.  For example,
+    the following view is insecure:
+</p><pre class="programlisting">
+CREATE VIEW phone_number AS
+    SELECT person, phone FROM phone_data WHERE phone NOT LIKE '412%';
+</pre><p>
+    This view might seem secure, since the rule system will rewrite any
+    <code class="command">SELECT</code> from <code class="literal">phone_number</code> into a
+    <code class="command">SELECT</code> from <code class="literal">phone_data</code> and add the
+    qualification that only entries where <code class="literal">phone</code> does not begin
+    with 412 are wanted.  But if the user can create their own functions,
+    it is not difficult to convince the planner to execute the user-defined
+    function prior to the <code class="function">NOT LIKE</code> expression.
+    For example:
+</p><pre class="programlisting">
+CREATE FUNCTION tricky(text, text) RETURNS bool AS $$
+BEGIN
+    RAISE NOTICE '% =&gt; %', $1, $2;
+    RETURN true;
+END;
+$$ LANGUAGE plpgsql COST 0.0000000000000000000001;
+
+SELECT * FROM phone_number WHERE tricky(person, phone);
+</pre><p>
+    Every person and phone number in the <code class="literal">phone_data</code> table will be
+    printed as a <code class="literal">NOTICE</code>, because the planner will choose to
+    execute the inexpensive <code class="function">tricky</code> function before the
+    more expensive <code class="function">NOT LIKE</code>.  Even if the user is
+    prevented from defining new functions, built-in functions can be used in
+    similar attacks.  (For example, most casting functions include their
+    input values in the error messages they produce.)
+</p><p>
+    Similar considerations apply to update rules. In the examples of
+    the previous section, the owner of the tables in the example
+    database could grant the privileges <code class="literal">SELECT</code>,
+    <code class="literal">INSERT</code>, <code class="literal">UPDATE</code>, and <code class="literal">DELETE</code> on
+    the <code class="literal">shoelace</code> view to someone else, but only
+    <code class="literal">SELECT</code> on <code class="literal">shoelace_log</code>. The rule action to
+    write log entries will still be executed successfully, and that
+    other user could see the log entries.  But they could not create fake
+    entries, nor could they manipulate or remove existing ones.  In this
+    case, there is no possibility of subverting the rules by convincing
+    the planner to alter the order of operations, because the only rule
+    which references <code class="literal">shoelace_log</code> is an unqualified
+    <code class="literal">INSERT</code>.  This might not be true in more complex scenarios.
+</p><p>
+    When it is necessary for a view to provide row level security, the
+    <code class="literal">security_barrier</code> attribute should be applied to
+    the view.  This prevents maliciously-chosen functions and operators from
+    being passed values from rows until after the view has done its work.  For
+    example, if the view shown above had been created like this, it would
+    be secure:
+</p><pre class="programlisting">
+CREATE VIEW phone_number WITH (security_barrier) AS
+    SELECT person, phone FROM phone_data WHERE phone NOT LIKE '412%';
+</pre><p>
+    Views created with the <code class="literal">security_barrier</code> may perform
+    far worse than views created without this option.  In general, there is
+    no way to avoid this: the fastest possible plan must be rejected
+    if it may compromise security.  For this reason, this option is not
+    enabled by default.
+</p><p>
+    The query planner has more flexibility when dealing with functions that
+    have no side effects.  Such functions are referred to as <code class="literal">LEAKPROOF</code>, and
+    include many simple, commonly used operators, such as many equality
+    operators.  The query planner can safely allow such functions to be evaluated
+    at any point in the query execution process, since invoking them on rows
+    invisible to the user will not leak any information about the unseen rows.
+    Further, functions which do not take arguments or which are not passed any
+    arguments from the security barrier view do not have to be marked as
+    <code class="literal">LEAKPROOF</code> to be pushed down, as they never receive data
+    from the view.  In contrast, a function that might throw an error depending
+    on the values received as arguments (such as one that throws an error in the
+    event of overflow or division by zero) is not leak-proof, and could provide
+    significant information about the unseen rows if applied before the security
+    view's row filters.
+</p><p>
+    It is important to understand that even a view created with the
+    <code class="literal">security_barrier</code> option is intended to be secure only
+    in the limited sense that the contents of the invisible tuples will not be
+    passed to possibly-insecure functions.  The user may well have other means
+    of making inferences about the unseen data; for example, they can see the
+    query plan using <code class="command">EXPLAIN</code>, or measure the run time of
+    queries against the view.  A malicious attacker might be able to infer
+    something about the amount of unseen data, or even gain some information
+    about the data distribution or most common values (since these things may
+    affect the run time of the plan; or even, since they are also reflected in
+    the optimizer statistics, the choice of plan).  If these types of "covert
+    channel" attacks are of concern, it is probably unwise to grant any access
+    to the data at all.
+</p></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="rules-update.html" title="40.4. Rules on INSERT, UPDATE, and DELETE">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="rules.html" title="Chapter 40. The Rule System">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="rules-status.html" title="40.6. Rules and Command Status">Next</a></td></tr><tr><td width="40%" align="left" valign="top">40.4. Rules on <code xmlns="http://www.w3.org/1999/xhtml" class="command">INSERT</code>, <code xmlns="http://www.w3.org/1999/xhtml" class="command">UPDATE</code>, and <code xmlns="http://www.w3.org/1999/xhtml" class="command">DELETE</code> </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 40.6. Rules and Command Status</td></tr></table></div></body></html>
+\ No newline at end of file
author	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-05-04 12:19:15 +0000
committer	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-05-04 12:19:15 +0000
commit	6eb9c5a5657d1fe77b55cc261450f3538d35a94d (patch)
tree	657d8194422a5daccecfd42d654b8a245ef7b4c8 /doc/src/sgml/html/rules-privileges.html
parent	Initial commit. (diff)
download	postgresql-13-upstream.tar.xz postgresql-13-upstream.zip