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

2010/11/28 KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>:
>>> My comment was from a standpoint which wants consistent behavior
>>> between SELECT ... FOR and LOCK command.
>>
>> Again, nothing about this makes those consistent.
>>
>>> If we concerned about this
>>> behavior, ExecCheckRTEPerms() might be a place where we also should fix.
>>
>> I don't understand what you're getting at here.
>>
> I thought the author concerned about inconsistency between them.
> (Perhaps, I might misunderstood his motivation?)

A user with single-column UPDATE privileges could obtain a ROW
EXCLUSIVE lock by issuing an UPDATE statement, but currently cannot
obtain the same lock using LOCK TABLE. It would be reasonable and
consistent to allow such a user to take a ROW SHARE or ROW EXCLUSIVE
lock using LOCK TABLE, but I'm not sure what the use case for that
would be.

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.
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.
This also works for ANALYZE. If you need a user who doesn't own a
table to be able to VACUUM it, that's a bit trickier because VACUUM
can only be invoked as a top-level command, not from within a function
or already-open transaction. Perhaps we can fix this some day if we
implement autonomous transactions, but for now it doesn't really seem
worth losing a lot of sleep over. Just my opinion, of course...

> Do we need to answer: "Yes, it is a specification, so you need to grant
> table level privileges, instead"?

I think that's the most reasonable answer. My vote is to just update
the LOCK TABLE documentation to be more precise about what the rules
are, and move on.

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