diff options
| author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 12:15:05 +0000 |
|---|---|---|
| committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 12:15:05 +0000 |
| commit | 46651ce6fe013220ed397add242004d764fc0153 (patch) | |
| tree | 6e5299f990f88e60174a1d3ae6e48eedd2688b2b /doc/src/sgml/html/rules-privileges.html | |
| parent | Initial commit. (diff) | |
| download | postgresql-14-upstream.tar.xz postgresql-14-upstream.zip | |
Adding upstream version 14.5.upstream/14.5upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to '')
| -rw-r--r-- | doc/src/sgml/html/rules-privileges.html | 157 |
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..b94b0ac --- /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>41.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 Vsnapshot" /><link rel="prev" href="rules-update.html" title="41.4. Rules on INSERT, UPDATE, and DELETE" /><link rel="next" href="rules-status.html" title="41.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">41.5. Rules and Privileges</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="rules-update.html" title="41.4. Rules on INSERT, UPDATE, and DELETE">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="rules.html" title="Chapter 41. The Rule System">Up</a></td><th width="60%" align="center">Chapter 41. The Rule System</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="rules-status.html" title="41.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">41.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 '% => %', $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="41.4. Rules on INSERT, UPDATE, and DELETE">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="rules.html" title="Chapter 41. The Rule System">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="rules-status.html" title="41.6. Rules and Command Status">Next</a></td></tr><tr><td width="40%" align="left" valign="top">41.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 14.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 41.6. Rules and Command Status</td></tr></table></div></body></html>
\ No newline at end of file |