<title>Rules and Privileges</title>

<indexterm zone="rules-privileges">
 <primary>privilege</primary>
 <secondary sortas="Regeln">with rules</secondary>
</indexterm>

<indexterm zone="rules-privileges">
 <primary>privilege</primary>
 <secondary sortas="Sichten">with views</secondary>
</indexterm>

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

<para>
    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 <productname>PostgreSQL</productname> rule system changes the
    behavior of the default access control system. With the exception of
    <literal>SELECT</literal> rules associated with security invoker views
    (see <link linkend="sql-createview"><command>CREATE VIEW</command></link>),
    all 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, except for security invoker views, users only need the
    required privileges for the tables/views that are explicitly named in
    their queries.
</para>

<para>
    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:

<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;
</programlisting>

    Nobody except that user (and the database superusers) can access the
    <literal>phone_data</literal> table. But because of the <command>GRANT</command>,
    the assistant can run a <command>SELECT</command> on the
    <literal>phone_number</literal> view. The rule system will rewrite the
    <command>SELECT</command> from <literal>phone_number</literal> into a
    <command>SELECT</command> from <literal>phone_data</literal>.
    Since the user is the owner of
    <literal>phone_number</literal> and therefore the owner of the rule, the
    read access to <literal>phone_data</literal> is now checked against the user's
    privileges and the query is permitted. The check for accessing
    <literal>phone_number</literal> is also performed, but this is done
    against the invoking user, so nobody but the user and the
    assistant can use it.
</para>

<para>
    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 <literal>phone_number</literal> data through the assistant's view.
    What the assistant cannot do is to create a view that directly
    accesses <literal>phone_data</literal>.  (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 <literal>phone_number</literal> view, the user can revoke the assistant's access. Immediately, any
    access to the assistant's view would fail.
</para>

<para>
    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 <literal>phone_number</literal> 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
    <command>GRANT</command> command means, <quote>I trust you</quote>.
    If someone you trust does the thing above, it's time to
    think it over and then use <command>REVOKE</command>.