2010/11/28 KaiGai Kohei <kai...@ak.jp.nec.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
