Stephen Frost wrote: > * Craig Ringer (craig@xxxxxxxxxxxxxxxxxxxxx) wrote: >> The issue with column privs is that Hibernate lists all columns, even >> ones it hasn't set or altered, in the INSERT and UPDATE statements it >> issues. Column privileges are checked based on the INSERT or UPDATE >> column list, not the actual values being changed, so even: > [excellent description cut] > > This begs the question of if this is something PG should just allow > rather than denying the update. Can you clarify exactly what hibernate > does? Does it do: > > #1: update x set col1 = col1 where pk = 'a'; > > Or does it do: > > #2: update x set col1 = 'abc' where pk = 'a'; > > (where 'abc' happens to be the value of col1 in the database for > pk = 'a')? Closer to #2. What it's really doing is equivalent to: PREPARE update_x(col1type, pktype) AS UPDATE x SET col1 = $1 WHERE pk = $2; EXECUTE update_X ( 'abc', 'a' ); though it's done via the JDBC driver's prepared statement interfaces and so happens at the protocol level and may not involve a true prepared query, only protocol-level parameter binding. The JDBC driver is likely to shift up to a server-side prepared statement if the query is repeatedly executed, though. If it was doing #1, it'd (a) need to know how the record in the DB differed from the object representation of it in memory, and (b) need to generate a new query since the set of bind parameters would've changed. So it might as well just omit the unchanged columns ... which is exactly what it does if selectBeforeUpdate, dynamicInsert and dynamicUpdate are set. Otherwise it'll build one query for insert and one for update at startup, and cache the query text. I think it also caches JDBC PreparedStatement instances for connections - not sure. > It might be possible to ignore/optimize/whatever #1, perhaps, but > there's really nothing we could do about #2. #2 could *theoretically* be handled by doing ... IS DISTINCT FROM ... comparisons between old and new records, and treating fields where the old and new values are the same as absent from the insert or update for purposes of privilege checking. That seems like an ugly hack, though, and I don't like database systems having to work around bad ORM behavior. As there's a sensible way to get Hibernate to do the right thing, and it's not even an issue for most Hibernate users anyway, I don't see the point in worrying about it. If a Hibernate user really needs col privs that vary per role, and doesn't want to select-before-update, they can always write their own extension to the persistence engine that caches original copies of their objects client-side to avoid the need to query the server for them. If they want to use prepared statements, they can modify their mapping in memory (as I described earlier) based on ACL queries before building the persistence unit. So this can be taken care of perfectly reasonably in the ORM and Pg shouldn't have to care about it. > If it's #1, do other > databases which support column-level privs ignore those, or do they deny > the update like PG does today? No idea, but I scarcely think it worth a special case for this when it'd be such a dumb thing to do (as noted above). -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
