Re: advisory locks and permissions

On 9/21/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> One good thing about advisory locks having been in contrib was that they
> didn't affect anyone who didn't actually install the module.  Now that
> we've put those functions in core, I wonder whether we don't need to
> face up to the possibility of malicious use.  For instance, it's not
> very hard to create a DoS situation by running the system out of shared
> lock table space:

It's much more likely to accidentally bork yourself.

> regression=# select pg_advisory_lock(x) from generate_series(1,1000000) x;
> WARNING:  out of shared memory
> ERROR:  out of shared memory
> HINT:  You may need to increase max_locks_per_transaction.
> regression=#
>
> and once you've done that about the only fix is to quit your backend :-(
>
> The brute force answer is to make those functions superuser-only, but I
> wonder if there is a better way.  Perhaps we could just deny public
> execute access on them by default, and let admins grant the privilege to
> whom they trust.
>
> Or we could try to do something about limiting the number of such locks
> that can be granted, but that seems nontrivial to tackle at such a late
> stage of the devel cycle.

I vote for locking down to superuser access (lets be frank here: I
would estimate 90%+ database installatons run with the application as
root) so we are not losing much.
I honestly think exhausting the lock space should raise a fatal, not
an error.  This gives a chance for recovery without a hard reset.  Any
other limitation would be fine, now or later, just please don't slow
them down! (advisory locks are extremely fast).

merlin