Re: advisory locks and permissions

On 9/21/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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