Re: Overcoming SELECT ... FOR UPDATE permission restrictions

On Mon, Apr 16, 2018 at 08:12:45PM +0300, Alexander Lakhin wrote:
> The worst scenario (with the current system views) I could think of is:
> user=> CREATE VIEW pgg AS SELECT * FROM pg_group;
> BEGIN TRANSACTION; SELECT * FROM pgg FOR UPDATE; SELECT pg_sleep(60);
> ROLLBACK;
> and the parallel operation:
> admin=> DROP ROLE testrole;
> hangs for one minute.
> But admin can observer the locks and kill the offending backend so it's
> hardly a critical issue.

No need to use pg_sleep(), you could just as well start a full
transaction block and let the wait happen forever.

The main point is that row-level locks are not strong enough to prevent
read-only operations, so critical operations like authentication can
still be triggered. This can be used though to disrupt the activity of
all DDL operations if you take a lock on them, which sucks. So while
that's a nuisance, it is always possible to counter it.

I also don't quite understand the argument of application relying on
this behavior. If they do, that's wrong anyway, so the risk of
operation disruptions for shared environments would matter more in my
opinion.
--
Michael