VACUUM as a denial-of-service attack - Mailing list pgsql-hackers

Whilst playing around with concurrent VACUUMs (see previous message),
I looked at some other issues that were on my TODO list.  I think
these deserve a separate thread:

1. VACUUM does internal start/commit-transaction calls around the
processing of each table it handles.  That's good, because it ensures
that the results of vacuuming each table are committed down to disk
before we move on to vacuuming the next one (and take the ever-present
chance of failure).  But if VACUUM is invoked inside a BEGIN/END
transaction block, those start/commit-transaction calls don't actually
commit anything; they reduce to plain statement boundaries within
the transaction.  This is Bad, Very Bad, on two grounds: (a) abort while vacuuming a later table imperils the results
forall     prior tables, since they won't get committed; (b) by the time we approach the end of vacuuming a whole
database,    we will hold exclusive locks on just about everything, which will     not be good for the progress of any
actualwork being done by     other backends.  Not to mention the very strong possibility of     hitting a deadlock
againstlocks held by other backends.
 

I had previously suggested banning VACUUM inside a transaction block
to forestall these problems.  I now see that the problems really only
apply to the case of VACUUMing a whole database --- the variant of
VACUUM that processes only a single table could be allowed inside
a BEGIN/END block, and it wouldn't create any problems worse than
any other command that grabs exclusive access on a table.  (OK,
you could BEGIN and then VACUUM a lot of tables one by one, but
then you deserve any problems you get...)

So: should VACUUM refuse to run inside BEGIN at all, or should it
refuse only the whole-database variant?  The former would be more
consistent and easier to explain, but the latter might be more useful.

2. It is a serious security breach that any random user can VACUUM
any table.  Particularly in the current code, where VACUUM can be
done inside a transaction, because that means the user can sit on
the locks acquired by VACUUM.  I can't do "lock table pg_shadow"
as an unprivileged user --- but I can do "begin; vacuum pg_shadow;
<twiddle thumbs>".  Guess what happens when subsequent users try
to connect.

Even if we disallow all forms of VACUUM inside transactions, one
could still mount a moderately effective denial-of-service attack
by issuing a continuous stream of "vacuum pg_shadow" commands,
or perhaps repeated vacuums of some large-and-mission-critical
user table.

I think a reasonable answer to this is to restrict VACUUM on any
table to be allowed only to the table owner and Postgres superuser.
Does anyone have an objection or better idea?
        regards, tom lane