On Mon, Nov 29, 2010 at 10:50 AM, Robert Haas <robertmh...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
