Re: Postgres not using correct indices for views.

"Thomas Rosenstein" <thomas(dot)rosenstein(at)creamfinance(dot)com> writes:
> we have created restricted view for our tables, so that we can allow
> access to non-gdpr relevant data but hide everything else.
> For exactly those views, the Query Planner uses the wrong indices, when
> executing exactly the same query, once it takes 0.1 s and on the views
> it takes nearly 18 sec (it does a full table scan, or uses the wrong
> indices).
> Do we have to GRANT additional rights? I see it's using some indices,
> just not the correct ones!

Does EXPLAIN show reasonable rowcount estimates when you query
directly, but bad ones when you query via the views?

If so, a likely guess is that you're falling foul of the restrictions
added for CVE-2017-7484:

Author: Peter Eisentraut <peter_e(at)gmx(dot)net>
Branch: master Release: REL_10_BR [e2d4ef8de] 2017-05-08 09:26:32 -0400
Branch: REL9_6_STABLE Release: REL9_6_3 [c33c42362] 2017-05-08 09:18:57 -0400
Branch: REL9_5_STABLE Release: REL9_5_7 [d45cd7c0e] 2017-05-08 09:19:07 -0400
Branch: REL9_4_STABLE Release: REL9_4_12 [3e5ea1f9b] 2017-05-08 09:19:15 -0400
Branch: REL9_3_STABLE Release: REL9_3_17 [4f1b2089a] 2017-05-08 09:19:23 -0400
Branch: REL9_2_STABLE Release: REL9_2_21 [d035c1b97] 2017-05-08 09:19:42 -0400

Add security checks to selectivity estimation functions

Some selectivity estimation functions run user-supplied operators over
data obtained from pg_statistic without security checks, which allows
those operators to leak pg_statistic data without having privileges on
the underlying tables. Fix by checking that one of the following is
satisfied: (1) the user has table or column privileges on the table
underlying the pg_statistic data, or (2) the function implementing the
user-supplied operator is leak-proof. If neither is satisfied, planning
will proceed as if there are no statistics available.

At least one of these is satisfied in most cases in practice. The only
situations that are negatively impacted are user-defined or
not-leak-proof operators on a security-barrier view.

Reported-by: Robert Haas <robertmhaas(at)gmail(dot)com>
Author: Peter Eisentraut <peter_e(at)gmx(dot)net>
Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>

Security: CVE-2017-7484

However, if you're not on the latest minor releases, you might
find that updating would fix this for you, because of

Author: Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
Branch: master Release: REL_12_BR [a0905056f] 2019-05-06 11:54:32 +0100
Branch: REL_11_STABLE Release: REL_11_3 [98dad4cd4] 2019-05-06 11:56:37 +0100
Branch: REL_10_STABLE Release: REL_10_8 [ca74e3e0f] 2019-05-06 11:58:32 +0100
Branch: REL9_6_STABLE Release: REL9_6_13 [71185228c] 2019-05-06 12:00:00 +0100
Branch: REL9_5_STABLE Release: REL9_5_17 [01256815a] 2019-05-06 12:01:44 +0100
Branch: REL9_4_STABLE Release: REL9_4_22 [3c0999909] 2019-05-06 12:05:05 +0100

Use checkAsUser for selectivity estimator checks, if it's set.

In examine_variable() and examine_simple_variable(), when checking the
user's table and column privileges to determine whether to grant
access to the pg_statistic data, use checkAsUser for the privilege
checks, if it's set. This will be the case if we're accessing the
table via a view, to indicate that we should perform privilege checks
as the view owner rather than the current user.

This change makes this planner check consistent with the check in the
executor, so the planner will be able to make use of statistics if the
table is accessible via the view. This fixes a performance regression
introduced by commit e2d4ef8de8, which affects queries against
non-security barrier views in the case where the user doesn't have
privileges on the underlying table, but the view owner does.

Note that it continues to provide the same safeguards controlling
access to pg_statistic for direct table access (in which case
checkAsUser won't be set) and for security barrier views, because of
the nearby checks on rte->security_barrier and rte->securityQuals.

Back-patch to all supported branches because e2d4ef8de8 was.

Dean Rasheed, reviewed by Jonathan Katz and Stephen Frost.

regards, tom lane