## 41.5. Rules and Privileges

[](<>)[](<>)

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 users only need the required privileges for the tables/views that are explicitly named in their queries.

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:

```
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;
```

Nobody except that user (and the database superusers) can access the`phone_data`table. But because of the`GRANT`, the assistant 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 the user's privileges and the query is permitted. The check for accessing`phone_number`也会执行，但这是针对调用用户执行的，因此除了用户和助手之外没有人可以使用它。

权限逐条检查。所以助理是目前唯一可以看到公共电话号码的人。但是助手可以设置另一个视图并向公众授予对该视图的访问权限。然后，任何人都可以看到`电话号码`数据通过助手的视图。助手不能做的是创建一个直接访问的视图`电话数据`.（实际上助手可以，但它不会工作，因为在权限检查期间每次访问都将被拒绝。）一旦用户注意到助手打开了他们的`电话号码`查看，用户可以撤销助手的访问权限。立即，对助手视图的任何访问都将失败。

有人可能认为这种逐条检查是一个安全漏洞，但实际上并非如此。但如果它不这样工作，助手可以设置一个具有相同列的表`电话号码`并每天将数据复制到那里一次。然后是助手自己的数据，助手可以授予他们想要的每个人的访问权限。一种`授予`command 的意思是“我相信你”。如果您信任的人做了上述事情，是时候考虑一​​下然后使用`撤销`.

请注意，虽然视图可用于使用上面显示的技术隐藏某些列的内容，但它们不能用于可靠地隐藏看不见的行中的数据，除非`安全屏障`标志已设置。例如，以下视图是不安全的：

```
CREATE VIEW phone_number AS
    SELECT person, phone FROM phone_data WHERE phone NOT LIKE '412%';
```

这个视图可能看起来很安全，因为规则系统将重写任何`选择`从`电话号码`成一个`选择`从`电话数据`并添加只有条目的资格`电话`不以 412 开头的都是通缉犯。但是如果用户可以创建自己的函数，那么说服规划者在执行用户自定义函数之前并不难。`不喜欢`表达。例如：

```
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);
```

中的每个人和电话号码`电话数据`表格将打印为`注意`，因为计划者会选择执行廉价的`棘手`功能前更贵`不喜欢`.即使阻止用户定义新函数，内置函数也可以用于类似的攻击。（例如，大多数转换函数在它们产生的错误消息中包含它们的输入值。）

类似的考虑适用于更新规则。在上一节的示例中，示例数据库中表的所有者可以授予权限`选择`,`插入`,`更新`， 和`删除`在`鞋带`给别人看，但只是`选择`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 they could not create fake entries, nor could they 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.

When it is necessary for a view to provide row-level security, the`security_barrier`attribute should be applied to the view. This prevents maliciously-chosen functions and operators from being passed values from rows until after the view has done its work. For example, if the view shown above had been created like this, it would be secure:

```
CREATE VIEW phone_number WITH (security_barrier) AS
    SELECT person, phone FROM phone_data WHERE phone NOT LIKE '412%';
```

Views created with the`security_barrier`may perform far worse than views created without this option. In general, there is no way to avoid this: the fastest possible plan must be rejected if it may compromise security. For this reason, this option is not enabled by default.

The query planner has more flexibility when dealing with functions that have no side effects. Such functions are referred to as`LEAKPROOF`, and include many simple, commonly used operators, such as many equality operators. The query planner can safely allow such functions to be evaluated at any point in the query execution process, since invoking them on rows invisible to the user will not leak any information about the unseen rows. Further, functions which do not take arguments or which are not passed any arguments from the security barrier view do not have to be marked as`LEAKPROOF`to be pushed down, as they never receive data from the view. In contrast, a function that might throw an error depending on the values received as arguments (such as one that throws an error in the event of overflow or division by zero) is not leak-proof, and could provide significant information about the unseen rows if applied before the security view's row filters.

It is important to understand that even a view created with the`security_barrier`option is intended to be secure only in the limited sense that the contents of the invisible tuples will not be passed to possibly-insecure functions. The user may well have other means of making inferences about the unseen data; for example, they can see the query plan using`EXPLAIN`, or measure the run time of queries against the view. A malicious attacker might be able to infer something about the amount of unseen data, or even gain some information about the data distribution or most common values (since these things may affect the run time of the plan; or even, since they are also reflected in the optimizer statistics, the choice of plan). If these types of "covert channel" attacks are of concern, it is probably unwise to grant any access to the data at all.