Re: [GENERAL] column-level update privs + lock table

On Sun, Nov 28, 2010 at 11:35 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Fri, 2010-11-26 at 19:11 -0500, Robert Haas wrote:
>> 2010/11/25 KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>:
>> > (2010/10/16 4:49), Josh Kupershmidt wrote:
>> >> [Moving to -hackers]
>> >>
>> >> On Fri, Oct 15, 2010 at 3:43 AM, Simon Riggs<simon(at)2ndquadrant(dot)com>  wrote:
>> >>> On Mon, 2010-10-11 at 09:41 -0400, Josh Kupershmidt wrote:
>> >>>> On Thu, Oct 7, 2010 at 7:43 PM, Josh Kupershmidt<schmiddy(at)gmail(dot)com>  wrote:
>> >>>>
>> >>>>> I noticed that granting a user column-level update privileges doesn't
>> >>>>> allow that user to issue LOCK TABLE with any mode other than Access
>> >>>>> Share.
>> >>>>
>> >>>> Anyone think this could be added as a TODO?
>> >>>
>> >>> Seems so to me, but you raise on Hackers.
>> >>
>> >> Thanks, Simon. Attached is a simple patch to let column-level UPDATE
>> >> privileges allow a user to LOCK TABLE in a mode higher than Access
>> >> Share. Small doc. update and regression test update are included as
>> >> well. Feedback is welcome.
>> >>
>> >
>> > I checked your patch, then I'd like to mark it as "ready for committer".
>> >
>> > The point of this patch is trying to solve an incompatible behavior
>> > between SELECT ... FOR SHARE/UPDATE and LOCK command.
>> >
>> > On ExecCheckRTEPerms(), it allows the required accesses when no columns
>> > are explicitly specified in the query and the current user has necessary
>> > privilege on one of columns within the target relation.
>> > If we stand on the perspective that LOCK command should take same
>> > privileges with the case when we use SELECT ... FOR SHARE/UPDATE without
>> > specifying explicit columns, like COUNT(*), the existing LOCK command
>> > seems to me odd.
>> >
>> > I think this patch fixes the behavior as we expected.
>>
>> I'm not totally convinced that this is the correct behavior.  It seems
>> a bit surprising that UPDATE privilege on a single column is enough to
>> lock out all SELECT activity from the table.  It's actually a bit
>> surprising that even full-table UPDATE privileges are enough to do
>> this, but this change would allow people to block access to data they
>> can neither see nor modify.  That seems counterintuitive, if not a
>> security hole.
>
> This comment misses the point. A user can already lock every row of a
> table, if they choose, by issuing SELECT ... FOR SHARE/UPDATE, if they
> have update rights on a single column. So the patch does not increase
> the rights of the user, it merely allows it to happen in a rational way
> and in a way that makes SELECT and LOCK work the same.

Locking every row of the table allows a user with UPDATE privileges to
block all current UPDATE and DELETE statements, but it won't
necessarily block INSERT statements (depending on unique indices) and
it certainly won't block SELECT statements. This patch proposes to
allow a user with update privileges on a single column to lock out ALL
concurrent activity, reads and writes. So it is not by any definition
making SELECT and LOCK work the same.

What it IS doing is making column-level update permissions and
table-level update permissions work the same way. After all, one
might argue, if full-table update permissions allow a user to take an
access exclusive lock, why not single-column update permissions? I
think, though, that there is a reasonable argument to be made that a
user who has been given UPDATE privileges on the entire table contents
is more trusted than one who has privileges only on certain columns.
The first user can presumably trash the entire table contents if he so
desires; the second one can't.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company