Due to rewriting of queries by the PostgreSQL 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.

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 PostgreSQL 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 a user only needs
the required privileges for the tables/views that he names
explicitly in his queries.

For example: A user has a list of phone numbers where some of
them are private, the others are of interest for the secretary of
the office. He can construct the following:

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.
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 phone_number is also
performed, but this is done against the invoking user, so nobody
but the user and the secretary can use it.

The privileges are checked rule by rule. So the secretary is
for now the only one who can see the public phone numbers. But
the secretary can setup another view and grant access to that to
the public. Then, anyone can see the phone_number data through the secretary's view.
What the secretary cannot do is to create a view that directly
accesses phone_data. (Actually he can,
but it will not work since every access will be denied during the
permission checks.) And as soon as the user will notice, that the
secretary opened his phone_number view,
he can revoke his access. Immediately, any access to the
secretary's view would fail.

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
secretary could set up a table with the same columns as
phone_number and copy the data to there
once per day. Then it's his own data and he can grant access to
everyone he wants. A GRANT command
means, "I trust you". If someone you
trust does the thing above, it's time to think it over and then
use REVOKE.

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.

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 the shoelace
view to someone else, but only 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. 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.

Submit correction

If you see anything in the documentation that is not correct, does not match
your experience with the particular feature or requires further clarification,
please use
this form
to report a documentation issue.