Re: LOCK TABLE permission requirements

From: Florian Weimer <fweimer(at)bfk(dot)de>
To: Josh Kupershmidt <schmiddy(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: LOCK TABLE permission requirements
Date: 2011-06-30 07:32:52
Message-ID: 82y60jhizf.fsf@mid.bfk.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

* Josh Kupershmidt:

> On Wed, Jun 29, 2011 at 7:48 AM, Florian Weimer <fweimer(at)bfk(dot)de> wrote:
>> I've been looking around in the 9.0 documentation, but couldn't find the
>> permission requirements for LOCK TABLE (in particular, LOCK TABLE IN
>> SHARE MODE).  From the source, you need at least one of UPDATE, DELETE
>> or TRUNCATE.
>>
>> Is there a reason why the INSERT privilege is not sufficient for LOCK
>> TABLE, or is this just an oversight?
>
> The comments on this thread outline some reasons the permissions for
> LOCK TABLE are setup the way they are:
> http://archives.postgresql.org/pgsql-hackers/2010-11/msg01819.php
>
> Basically, if you have UPDATE, DELETE, or TRUNCATE privileges you can
> potentially lock out competing sessions on a table, similar to what
> some forms of LOCK TABLE would do; just having INSERT privileges
> doesn't necessarily give you that power.

This makes sense, thanks. Doesn't REFERENCES have the same potential?
Then it could be added to the list.

In my pre-9.1 world, I need to acquire a table lock to avoid incorrectly
serialized transactions. The application is only doing SELECTs and
INSERTs, so I don't want to grant it UPDATE privileges, but REFERENCES
would be fine. Right now, I'm using a separate, empty table and lock
that, but that's a bit of a kludge.

--
Florian Weimer <fweimer(at)bfk(dot)de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Guillaume Lelarge 2011-06-30 08:16:27 Re: How did I get 8 Exclusive locks on the same table? And how many locks is too many?
Previous Message Casey Havenor 2011-06-30 05:19:07 Re: Inheritence issue scheme advice?