Re: REINDEX and shared catalogs

On Mon, Aug 6, 2018 at 2:40 AM, Michael Paquier <michael(at)paquier(dot)xyz> wrote:
> In the case of REINDEX, we *allow* shared catalogs to be reindexed.
> Hence, if a user is a database owner, he would also be able to reindex
> critical indexes on shared catalogs, where blocking authentication is
> possible just with sessions connected to the database reindexed. For a
> schema, the situation is basically worse since 9.5 as a schema owner can
> do the same with lighter permissions. One can just run "SELECT * FROM
> pg_stat_activity" in a transaction block in session 1, run REINDEX in
> session 2, and cause the system to refuse new connections. This is
> documented as well.

In my opinion, the behavior change is probably OK, but not back-patchable.

I think that the documentation could be phrased more clearly. If I
understand the proposed semantics, something like this might be about
right:

Reindexing a single index or table requires being the owner of that
index or table. Reindexing a schema or database requires being the
owner of that schema or database. Note that is therefore sometimes
possible for non-superusers to rebuild indexes of tables owner by other
users; however, as a special exception, when <command>REINDEX
DATABASE</command> or <command>REINDEX SCHEMA</> is
issued by a non-superuser, indexes on shared catalogs will be skipped
unless the user owns the catalog (which typically won't be the case).
Of course, superusers can always reindex anything.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company