On 30 September 2014 20:17, Stephen Frost <sfr...@snowman.net> wrote: > * Dean Rasheed (dean.a.rash...@gmail.com) wrote: >> On 30 September 2014 16:52, Stephen Frost <sfr...@snowman.net> wrote: >> > If the user only has column-level privileges on the table then I'm not >> > really sure how useful the detail will be. >> >> One of the main things that detail is useful for is identifying the >> failing row in a multi-row update. In most real-world cases, I would >> expect the column-level privileges to include the table's PK, so the >> detail would meet that requirement. In fact the column-level >> privileges would pretty much have to include sufficient columns to >> usefully identify rows, otherwise updates wouldn't be practical. > > That may or may not be true- the user needs sufficient information to > identify a row, but that doesn't mean they have access to all columns > make up a unique constraint. It's not uncommon to have a surrogate key > for identifying the rows and then an independent uniqueness constraint > on some natural key for the table, which the user may not have access > to. >
True, but then the surrogate key would be included in the error details which would allow the failing row to be identified. >> > What do you think about returning just what the user provided in the >> > first place in both of these cases..? I'm not quite sure what that >> > would look like in the UPDATE case but for INSERT (and COPY) it would be >> > the subset of columns being inserted and the values originally provided. >> > That may not be what the actual conflict is due to, as there could be >> > before triggers changing things in the middle, or the conflict could be >> > on default values, but at least the input row could be identified and >> > there wouldn't be this information leak risk. Not sure how difficult >> > that would be to implement though. >> >> I could see that working for INSERTs, but for UPDATEs I don't think >> that would be very useful in practice, because the columns most likely >> to be useful for identifying the failing row (e.g., key columns) are >> also the columns least likely to have been updated. > > I'm not sure that I follow this- if you're not updating the key columns > then you're not likely to be having a conflict due to them... > The constraint violation could well be due to updating a non-key column such as a column with a NOT NULL constraint on it, in which case only including that column in the error detail wouldn't do much good -- you'd want to include the key columns if possible. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
