Skip site navigation (1) Skip section navigation (2)

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
Message-ID: 1016.1291142919@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-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
later.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group