Postgresql 中文操作指南

76.3. Planner Statistics and Security #

对表 pg_statistic 的访问仅限于超级用户，以便普通用户无法从中了解到其他用户的表的内容。一些选择性估计函数将使用用户提供的运算符（查询中出现的运算符或相关运算符）来分析存储的统计信息。例如，为了确定存储的最常见值是否适用，选择性估计器必须运行适当的 = 运算符将查询中的常量与存储的值进行比较。因此，pg_statistic 中的数据可能会传递给用户定义的运算符。精心设计的运算符可以故意泄露传递的操作数（例如，通过记录它们或将它们写入另一个表），或者通过在错误消息中显示它们的值不小心泄露它们，在这两种情况下都可能将 pg_statistic 中的数据泄露给不应该看到它的用户。

Access to the table pg_statistic is restricted to superusers, so that ordinary users cannot learn about the contents of the tables of other users from it. Some selectivity estimation functions will use a user-provided operator (either the operator appearing in the query or a related operator) to analyze the stored statistics. For example, in order to determine whether a stored most common value is applicable, the selectivity estimator will have to run the appropriate = operator to compare the constant in the query to the stored value. Thus the data in pg_statistic is potentially passed to user-defined operators. An appropriately crafted operator can intentionally leak the passed operands (for example, by logging them or writing them to a different table), or accidentally leak them by showing their values in error messages, in either case possibly exposing data from pg_statistic to a user who should not be able to see it.

为了防止这种情况，以下内容适用于所有内置选择性估计函数。在规划查询时，为了能够使用存储的统计信息，当前用户必须对表或所涉及的列具有 SELECT 权限，或者所使用的运算符必须是 LEAKPROOF （更准确地说，是运算符所基于的函数）。如果不是这样，则选择性估计器将表现得好像没有可用统计信息一样，规划器将继续使用默认假设或备用假设。

In order to prevent this, the following applies to all built-in selectivity estimation functions. When planning a query, in order to be able to use stored statistics, the current user must either have SELECT privilege on the table or the involved columns, or the operator used must be LEAKPROOF (more accurately, the function that the operator is based on). If not, then the selectivity estimator will behave as if no statistics are available, and the planner will proceed with default or fall-back assumptions.

如果用户对表或列没有所需的权限，则在许多情况下，查询最终会收到一个权限被拒绝的错误，在这种情况下，此机制在实践中是不可见的。但是，如果用户正在从安全屏障视图中读取数据，则规划器可能希望检查对于用户来说无法访问的基本表的统计信息。在这种情况下，该运算符应该是不泄漏的，否则不会使用统计信息。除了计划可能不是最优以外，对此没有任何直接反馈。如果有人怀疑这是这种情况，他们可以尝试以更高级别的用户身份运行查询，以查看是否会导致不同的计划。

If a user does not have the required privilege on the table or columns, then in many cases the query will ultimately receive a permission-denied error, in which case this mechanism is invisible in practice. But if the user is reading from a security-barrier view, then the planner might wish to check the statistics of an underlying table that is otherwise inaccessible to the user. In that case, the operator should be leak-proof or the statistics will not be used. There is no direct feedback about that, except that the plan might be suboptimal. If one suspects that this is the case, one could try running the query as a more privileged user, to see if a different plan results.

此限制仅适用于规划器需要对来自 pg_statistic 的一个或多个值执行用户定义运算符的情况。因此，规划器被允许使用通用统计信息，例如空值的分数或列中不同值的数量，无论访问权限如何。

This restriction applies only to cases where the planner would need to execute a user-defined operator on one or more values from pg_statistic. Thus the planner is permitted to use generic statistical information, such as the fraction of null values or the number of distinct values in a column, regardless of access privileges.

在可能使用用户定义运算符处理统计信息的第三方扩展中包含的选择性估计函数应遵循相同的安全规则。有关指导，请查阅 PostgreSQL 源代码。

Selectivity estimation functions contained in third-party extensions that potentially operate on statistics with user-defined operators should follow the same security rules. Consult the PostgreSQL source code for guidance.