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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>, Josh Kupershmidt <schmiddy(at)gmail(dot)com>, KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [GENERAL] column-level update privs + lock table
Date: 2010-11-30 18:48:39
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general pgsql-hackers

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> Well, a user with full-table UPDATE privileges can trash the whole
> thing, so, from a security perspective, letting them lock is only
> allowing them to deny access to data they could have just as easily
> trashed. A user with only single-column UPDATE privileges cannot
> trash the whole table, though, so you are allowing them to deny read
> access to data they may not themselves have permission either to read
> or to update.

> Admittedly, this seems a bit more rickety in light of your point that
> they can still lock all the rows... but then that only stops writes,
> not reads. I'm less convinced that I'm right about this than I was 3
> days ago. But I'm still not convinced that the above argument is
> wrong, either.

I think what your complaint really boils down to is that LOCK TABLE
is granting excessive permissions to someone who only has table-level
UPDATE privilege. If we were designing that from scratch today, I am
sure we'd have made it tighter, say that UPDATE alone wouldn't give you
more than RowExclusive lock. However, given the lack of complaints
about this from the field, I can't get very excited about a
non-backward-compatible change to tighten LOCK's privilege checking.

I find the argument that column-level update should give weaker locking
privileges than full-table update to be pretty thin. That isn't the
case at the row level; why should it be true at the table level?

However, the other side of the "lack of complaints" argument is that
few people seem to care about whether LOCK TABLE responds to column
level privileges, either. AFAICS this patch is not in response to any
user request but just because Josh thought things were inconsistent.
Which they are, but at a deeper level than this. If we apply this
patch, then we'll be expanding the set of cases where LOCK is granting
privilege too freely, and thus creating more not less
backwards-compatibility problem if we are ever to make it saner.

On the whole I agree with Robert --- let's just adjust the
documentation, and not enlarge privileges in a way we might regret

regards, tom lane

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Radosław Smogura 2010-11-30 18:49:03 Improved JDBC driver part 2
Previous Message Cédric Villemain 2010-11-30 18:31:29 Re: Instrument checkpoint sync calls

Browse pgsql-general by date

  From Date Subject
Next Message Jasen Betts 2010-11-30 19:08:47 Re: finding rows with invalid characters
Previous Message Jasen Betts 2010-11-30 18:33:13 Re: Comparing first 3 numbers of a IPv4 address?