8000 Warn that views can be safely used to hide columns, but not rows. · intobs/postgres@04daad2 · GitHub
[go: up one dir, main page]
Skip to content

Commit 04daad2

Browse files
robertmhaasrobertmhaas
committed
Warn that views can be safely used to hide columns, but not rows.
1 parent d6d7926 commit 04daad2

File tree

2 files changed

+51
-11
lines changed

2 files changed

+51
-11
lines changed

‎doc/src/sgml/ref/create_view.sgml

Lines changed: 6 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -144,10 +144,12 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
144144

145145
<para>
146146
Access to tables referenced in the view is determined by permissions of
147-
the view owner. However, functions called in the view are treated the
148-
same as if they had been called directly from the query using the view.
149-
Therefore the user of a view must have permissions to call all functions
150-
used by the view.
147+
the view owner. In some cases, this can be used to provide secure but
148+
restricted access to the underlying tables. However, not all views are
149+
secure against tampering; see <xref linkend="rules-privileges"> for
150+
details. Functions called in the view are treated the same as if they had
151+
been called directly from the query using the view. Therefore the user of
152+
a view must have permissions to call all functions used by the view.
151153
</para>
152154

153155
<para>

‎doc/src/sgml/rules.sgml

Lines changed: 45 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1725,18 +1725,18 @@ SELECT * FROM shoelace;
17251725
<programlisting>
17261726
CREATE TABLE phone_data (person text, phone text, private boolean);
17271727
CREATE VIEW phone_number AS
1728-
SELECT person, phone FROM phone_data WHERE NOT private;
1728+
SELECT person, CASE WHEN NOT private THEN phone END AS phone
1729+
FROM phone_data;
17291730
GRANT SELECT ON phone_number TO secretary;
17301731
</programlisting>
1731-
1732+
17321733
Nobody except him (and the database superusers) can access the
17331734
<literal>phone_data</> table. But because of the <command>GRANT</>,
17341735
the secretary can run a <command>SELECT</command> on the
17351736
<literal>phone_number</> view. The rule system will rewrite the
17361737
<command>SELECT</command> from <literal>phone_number</> into a
1737-
<command>SELECT</command> from <literal>phone_data</> and add the
1738-
qualification that only entries where <literal>private</> is false
1739-
are wanted. Since the user is the owner of
1738+
<command>SELECT</command> from <literal>phone_data</>.
1739+
Since the user is the owner of
17401740
<literal>phone_number</> and therefore the owner of the rule, the
17411741
read access to <literal>phone_data</> is now checked against his
17421742
privileges and the query is permitted. The check for accessing
@@ -1770,15 +1770,53 @@ GRANT SELECT ON phone_number TO secretary;
17701770
</para>
17711771

17721772
<para>
1773-
This mechanism also works for update rules. In the examples of
1773+
Note that while views can be used to hide the contents of certain
1774+
columns using the technique shown above, they cannot be used to reliably
1775+
conceal the data in unseen rows. For example, the following view is
1776+
insecure:
1777+
<programlisting>
1778+
CREATE VIEW phone_number AS
1779+
SELECT person, phone FROM phone_data WHERE phone NOT LIKE '412%';
1780+
</programlisting>
1781+
This view might seem secure, since the rule system will rewrite any
1782+
<command>SELECT</command> from <literal>phone_number</> into a
1783+
<command>SELECT</command> from <literal>phone_data</> and add the
1784+
qualification that only entries where <literal>phone</> does not begin
1785+
with 412 are wanted. But if the user can create his or her own functions,
1786+
it is not difficult to convince the planner to execute the user-defined
1787+
function prior to the <function>NOT LIKE</function> expression.
1788+
<programlisting>
1789+
CREATE FUNCTION tricky(text, text) RETURNS bool AS $$
1790+
BEGIN
1791+
RAISE NOTICE '% => %', $1, $2;
1792+
RETURN true;
1793+
END
1794+
$$ LANGUAGE plpgsql COST 0.0000000000000000000001;
1795+
SELECT * FROM phone_number WHERE tricky(person, phone);
1796+
</programlisting>
1797+
Every person and phone number in the <literal>phone_data</> table will be
1798+
printed as a <literal>NOTICE</literal>, because the planner will choose to
1799+
execute the inexpensive <function>tricky</function> function before the
1800+
more expensive <function>NOT LIKE</function>. Even if the user is
1801+
prevented from defining new functions, built-in functions can be used in
1802+
similar attacks. (For example, casting functions include their inputs in
1803+
the error messages they produce.)
1804+
</para>
1805+
1806+
<para>
1807+
Similar considerations apply to update rules. In the examples of
17741808
the previous section, the owner of the tables in the example
17751809
database could grant the privileges <literal>SELECT</>,
17761810
<literal>INSERT</>, <literal>UPDATE</>, and <literal>DELETE</> on
17771811
the <literal>shoelace</> view to someone else, but only
17781812
<literal>SELECT</> on <literal>shoelace_log</>. The rule action to
17791813
write log entries will still be executed successfully, and that
17801814
other user could see the log entries. But he cannot create fake
1781-
entries, nor could he manipulate or remove existing ones.
1815+
entries, nor could he manipulate or remove existing ones. In this
1816+
case, there is no possibility of subverting the rules by convincing
1817+
the planner to alter the order of operations, because the only rule
1818+
which references <literal>shoelace_log</> is an unqualified
1819+
<literal>INSERT</>. This might not be true in more complex scenarios.
17821820
</para>
17831821
</sect1>
17841822

0 commit comments

Comments
 (0)
0