1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>69.3. Planner Statistics and Security</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="multivariate-statistics-examples.html" title="69.2. Multivariate Statistics Examples" /><link rel="next" href="backup-manifest-format.html" title="Chapter 70. Backup Manifest Format" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">69.3. Planner Statistics and Security</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="multivariate-statistics-examples.html" title="69.2. Multivariate Statistics Examples">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="planner-stats-details.html" title="Chapter 69. How the Planner Uses Statistics">Up</a></td><th width="60%" align="center">Chapter 69. How the Planner Uses Statistics</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="backup-manifest-format.html" title="Chapter 70. Backup Manifest Format">Next</a></td></tr></table><hr /></div><div class="sect1" id="PLANNER-STATS-SECURITY"><div class="titlepage"><div><div><h2 class="title" style="clear: both">69.3. Planner Statistics and Security <a href="#PLANNER-STATS-SECURITY" class="id_link">#</a></h2></div></div></div><p>
3 Access to the table <code class="structname">pg_statistic</code> is restricted to
4 superusers, so that ordinary users cannot learn about the contents of the
5 tables of other users from it. Some selectivity estimation functions will
6 use a user-provided operator (either the operator appearing in the query or
7 a related operator) to analyze the stored statistics. For example, in order
8 to determine whether a stored most common value is applicable, the
9 selectivity estimator will have to run the appropriate <code class="literal">=</code>
10 operator to compare the constant in the query to the stored value.
11 Thus the data in <code class="structname">pg_statistic</code> is potentially
12 passed to user-defined operators. An appropriately crafted operator can
13 intentionally leak the passed operands (for example, by logging them
14 or writing them to a different table), or accidentally leak them by showing
15 their values in error messages, in either case possibly exposing data from
16 <code class="structname">pg_statistic</code> to a user who should not be able to
19 In order to prevent this, the following applies to all built-in selectivity
20 estimation functions. When planning a query, in order to be able to use
21 stored statistics, the current user must either
22 have <code class="literal">SELECT</code> privilege on the table or the involved
23 columns, or the operator used must be <code class="literal">LEAKPROOF</code> (more
24 accurately, the function that the operator is based on). If not, then the
25 selectivity estimator will behave as if no statistics are available, and
26 the planner will proceed with default or fall-back assumptions.
27 The <a class="xref" href="app-psql.html" title="psql"><span class="refentrytitle"><span class="application">psql</span></span></a> program's
28 <code class="command"><a class="link" href="app-psql.html#APP-PSQL-META-COMMAND-DO-LC">\do+</a></code>
29 meta-command is useful to determine which operators are marked as leakproof.
31 If a user does not have the required privilege on the table or columns,
32 then in many cases the query will ultimately receive a permission-denied
33 error, in which case this mechanism is invisible in practice. But if the
34 user is reading from a security-barrier view, then the planner might wish
35 to check the statistics of an underlying table that is otherwise
36 inaccessible to the user. In that case, the operator should be leakproof
37 or the statistics will not be used. There is no direct feedback about
38 that, except that the plan might be suboptimal. If one suspects that this
39 is the case, one could try running the query as a more privileged user,
40 to see if a different plan results.
42 This restriction applies only to cases where the planner would need to
43 execute a user-defined operator on one or more values
44 from <code class="structname">pg_statistic</code>. Thus the planner is permitted
45 to use generic statistical information, such as the fraction of null values
46 or the number of distinct values in a column, regardless of access
49 Selectivity estimation functions contained in third-party extensions that
50 potentially operate on statistics with user-defined operators should follow
51 the same security rules. Consult the PostgreSQL source code for guidance.
52 </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="multivariate-statistics-examples.html" title="69.2. Multivariate Statistics Examples">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="planner-stats-details.html" title="Chapter 69. How the Planner Uses Statistics">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="backup-manifest-format.html" title="Chapter 70. Backup Manifest Format">Next</a></td></tr><tr><td width="40%" align="left" valign="top">69.2. Multivariate Statistics Examples </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> Chapter 70. Backup Manifest Format</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob