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

On Mon, Nov 29, 2010 at 9:37 PM, Josh Kupershmidt <schmiddy(at)gmail(dot)com> wrote:
>> It seems to me that if we're really worried about which locks users
>> are allowed to take (and so far all of the worrying seems to lack a
>> solid basis in any sort of usability argument) we'd need to invent
>> some special-purpose permissions, perhaps one for each lock level.
>
> OK, so here's why I wanted column-level update + lock table
> privileges. I put together a database application related to
> table-synchronization -- basically performing remote table
> comparisons. This application needed to update only a single column in
> the source table (an updated timestamp), but it needed to be certain
> that the source table wasn't changing underneath it.

Reasonable... but it doesn't seem unimaginable that someone could want
the opposite behavior, either, for the reasons I stated upthread.

>> And we might also want custom permissions for ANALYZE and VACUUM and
>> each subcommand of ALTER TABLE.  The question is, how much of that has
>> any real benefit?  It's probably uncommon to want to dole out such
>> fine-grained permissions, and our current permissions-granting
>> infrastructure tops out at 16 individual permissions, so it would need
>> some rework - particularly, to minimize slowdown of the common case
>> where you DON'T care about any of these fiddly ultra-fine-grained user
>> rights.
>>
>> For LOCK TABLE (or ANALYZE), it appears to be simple to allow users to
>> lock the table in any mode you like by providing an appropriate
>> SECURITY DEFINER function.  So I think if people want a user who can
>> update a single column of the table and also take an
>> AccessExclusiveLock we can just recommend that they do it that way.
>
> I actually hadn't thought of that, for some reason.
>
> We used to similarly recommend that people handle TRUNCATE privileges
> with a security definer function. That doesn't mean GRANT TRUNCATE
> wasn't a sweet addition to 8.4.

Hmm, glad you like it (I wrote that). I'm just asking how far we
should go before we decide we catering to use cases that are too
narrow to warrant an extension of the permissions system.

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