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

On Mon, Nov 29, 2010 at 10:50 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> 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.

Those limited privileges wouldn't be very useful for my purposes, at
least. I'll try to explain my use-case below.

> 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.

I ended up just assigning full-table UPDATE privileges to this user,
despite knowing that it only needed to update a single column. I would
have liked to make this privilege restriction explicit in the database
schema, but I can't.

> 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.

> 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.

I still see little reason to make LOCK TABLE permissions different for
column-level vs. table-level UPDATE privileges, but oh well.

Josh