Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit56ccff5

Browse files
committed
Warn that views can be safely used to hide columns, but not rows.
1 parent9cc8c84 commit56ccff5

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
@@ -143,10 +143,12 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
143143

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

152154
<para>

‎doc/src/sgml/rules.sgml

Lines changed: 45 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1729,18 +1729,18 @@ SELECT * FROM shoelace;
17291729
<programlisting>
17301730
CREATE TABLE phone_data (person text, phone text, private boolean);
17311731
CREATE VIEW phone_number AS
1732-
SELECT person, phone FROM phone_data WHERE NOT private;
1732+
SELECT person, CASE WHEN NOT private THEN phone END AS phone
1733+
FROM phone_data;
17331734
GRANT SELECT ON phone_number TO secretary;
17341735
</programlisting>
1735-
1736+
17361737
Nobody except him (and the database superusers) can access the
17371738
<literal>phone_data</> table. But because of the <command>GRANT</>,
17381739
the secretary can run a <command>SELECT</command> on the
17391740
<literal>phone_number</> view. The rule system will rewrite the
17401741
<command>SELECT</command> from <literal>phone_number</> into a
1741-
<command>SELECT</command> from <literal>phone_data</> and add the
1742-
qualification that only entries where <literal>private</> is false
1743-
are wanted. Since the user is the owner of
1742+
<command>SELECT</command> from <literal>phone_data</>.
1743+
Since the user is the owner of
17441744
<literal>phone_number</> and therefore the owner of the rule, the
17451745
read access to <literal>phone_data</> is now checked against his
17461746
privileges and the query is permitted. The check for accessing
@@ -1774,15 +1774,53 @@ GRANT SELECT ON phone_number TO secretary;
17741774
</para>
17751775

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

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp