From 56ccff59806793198020a6a3d154bbce6dfb7a06 Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Fri, 8 Oct 2010 09:15:17 -0400 Subject: [PATCH] Warn that views can be safely used to hide columns, but not rows. --- doc/src/sgml/ref/create_view.sgml | 10 +++--- doc/src/sgml/rules.sgml | 52 ++++++++++++++++++++++++++----- 2 files changed, 51 insertions(+), 11 deletions(-) diff --git a/doc/src/sgml/ref/create_view.sgml b/doc/src/sgml/ref/create_view.sgml index 48e2e0b963..6676383ab0 100644 --- a/doc/src/sgml/ref/create_view.sgml +++ b/doc/src/sgml/ref/create_view.sgml @@ -143,10 +143,12 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello; Access to tables referenced in the view is determined by permissions of - the view owner. However, functions called in the view are treated the - same as if they had been called directly from the query using the view. - Therefore the user of a view must have permissions to call all functions - used by the view. + the view owner. In some cases, this can be used to provide secure but + restricted access to the underlying tables. However, not all views are + secure against tampering; see for + details. Functions called in the view are treated the same as if they had + been called directly from the query using the view. Therefore the user of + a view must have permissions to call all functions used by the view. diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml index c42ff2937b..17c92bdf13 100644 --- a/doc/src/sgml/rules.sgml +++ b/doc/src/sgml/rules.sgml @@ -1729,18 +1729,18 @@ SELECT * FROM shoelace; CREATE TABLE phone_data (person text, phone text, private boolean); CREATE VIEW phone_number AS - SELECT person, phone FROM phone_data WHERE NOT private; + SELECT person, CASE WHEN NOT private THEN phone END AS phone + FROM phone_data; GRANT SELECT ON phone_number TO secretary; - + Nobody except him (and the database superusers) can access the phone_data table. But because of the GRANT, the secretary can run a SELECT on the phone_number view. The rule system will rewrite the SELECT from phone_number into a - SELECT from phone_data and add the - qualification that only entries where private is false - are wanted. Since the user is the owner of + SELECT from phone_data. + Since the user is the owner of phone_number and therefore the owner of the rule, the read access to phone_data is now checked against his privileges and the query is permitted. The check for accessing @@ -1774,7 +1774,41 @@ GRANT SELECT ON phone_number TO secretary; - This mechanism also works for update rules. In the examples of + 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. For example, the following view is + insecure: + +CREATE VIEW phone_number AS + SELECT person, phone FROM phone_data WHERE phone NOT LIKE '412%'; + + This view might seem secure, since the rule system will rewrite any + SELECT from phone_number into a + SELECT from phone_data and add the + qualification that only entries where phone does not begin + with 412 are wanted. But if the user can create his or her own functions, + it is not difficult to convince the planner to execute the user-defined + function prior to the NOT LIKE expression. + +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); + + Every person and phone number in the phone_data table will be + printed as a NOTICE, because the planner will choose to + execute the inexpensive tricky function before the + more expensive NOT LIKE. Even if the user is + prevented from defining new functions, built-in functions can be used in + similar attacks. (For example, casting functions include their inputs in + the error messages they produce.) + + + + 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 SELECT, INSERT, UPDATE, and DELETE on @@ -1782,7 +1816,11 @@ GRANT SELECT ON phone_number TO secretary; SELECT on shoelace_log. The rule action to write log entries will still be executed successfully, and that other user could see the log entries. But he cannot create fake - entries, nor could he manipulate or remove existing ones. + entries, nor could he 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 shoelace_log is an unqualified + INSERT. This might not be true in more complex scenarios. -- 2.40.0