Re: our checks for read-only queries are not great

On Thu, Jan 9, 2020 at 2:24 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
> > I'd be really interested to hear if anyone knows the history behind
> > allowing CLUSTER, REINDEX, VACUUM, and some operations on temp tables.
> > It seems to have been that way for a long time. I wonder if it was a
> > deliberate choice or something that just happened semi-accidentally.
>
> Within a "read-only" xact you mean?  I believe that allowing DML writes
> was intentional.  As for the utility commands, I suspect that it was in
> part accidental (error of omission?), and then if anyone thought hard
> about it they decided that allowing DML writes to temp tables justifies
> those operations too.
>
> Have you tried excavating in our git history to see when the relevant
> permission tests originated?

check_xact_readonly() with a long list of command tags originated in
the same commit that added read-only transactions. CLUSTER, REINDEX,
and VACUUM weren't included in the list of prohibited operations then,
either, but it's unclear whether that was a deliberate omission or an
oversight. That commit also thought that COPY FROM - and queries -
should allow temp tables. But there's nothing in the commit that seems
to explain why, unless the commit message itself is a hint:

commit b65cd562402ed9d3206d501cc74dc38bc421b2ce
Author: Peter Eisentraut <peter_e@gmx.net>
Date:   Fri Jan 10 22:03:30 2003 +0000

    Read-only transactions, as defined in SQL.

Maybe the SQL standard has something to say about this?

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