Re: Column privileges and Hibernate (SOLVED)

Stephen Frost wrote:
> * Craig Ringer (craig(at)postnewspapers(dot)com(dot)au) 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