Thread: Column privileges and Hibernate

Hi folks

Now that column privileges are supported (fantastic!), I've been looking
at replacing some of my unpleasantly verbose (and probably error-prone)
trigger code that's responsible for limiting which roles can modify
which columns in some tables.

I've run into a bit of a roadblock, and I'm wondering if anybody else
here has any experience with it.

One of the apps using the database uses the Hibernate ORM system for
Java. While excellent in most ways, it's giving me some trouble when it
comes to inserts/updates on tables with column privileges.

It's easy enough to tell Hibernate that certain columns are not
insertable or not updatable, and it'll honour that, so there's no issue
if all users have the same rights on a column. However, if rights vary
depending on the roles and grants of the logged-in user, it's not clear
how to inform it of that.

I can always fall back on using col. privs only for columns _nobody_
should ever insert and/or update (ie trigger managed columns) but that
wouldn't let me cut the bulk of the ugly trigger code. Alternately, I
could let the Hibernate classes model only the common attributes that
all users have the rights to, and use direct JDBC calls to update
special-privilege attributes. The latter isn't actually too bad an
option given the relatively few places in which this is an issue... but
I'd really prefer to be able to stay within the otherwise very smooth
Hibernate model if possible.

Anybody have related experience / ideas? I'm thinking of braving the
Hibernate web forums too, but I thought I'd drop a note here first given
the way it's come up as part of adopting new Pg features.

--
Craig Ringer

Hi Craig,

Do you mean that you use the postgresql role system as authentication
and authorization mechanism in your app through hibernate?
I don't understand how that should work. How do you authenticate?

As far as i know, the way to achieve what you want is through an
authorization layer on top of your database, using ldap for example.
Hibernate should then access the database with a single, relatively
highly privileged role.
Alternatively, if you don't trust your application layer, you could
define a limited number of authorization levels and create database
roles for each. These are not the roles that your end-users connect to
directly. It is what hibernate would use to connect. You would
probably have to do the object-database mapping separate for each
level.
For example, you could have a read-only authorization level and an edit level.
You would assign select privileges to the "read-only" db role and
insert, update, delete (but no ddl) to the "edit" database role.
Then when a end-user connects to your app, you check the authorization
system if this user has read-only or edit privileges. Then hibernate
would connect to the database with the appropriate mapping.

I might have gotten some details wrong here, i don't have much
experience with hibernate implementation.
I would be interested in other ways to do this. How do you go about
it? Any corrections of my statements are welcome too.

Cheers,

WBL


On Mon, Dec 14, 2009 at 5:25 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> Hi folks
>
> Now that column privileges are supported (fantastic!), I've been looking
> at replacing some of my unpleasantly verbose (and probably error-prone)
> trigger code that's responsible for limiting which roles can modify
> which columns in some tables.
>
> I've run into a bit of a roadblock, and I'm wondering if anybody else
> here has any experience with it.
>
> One of the apps using the database uses the Hibernate ORM system for
> Java. While excellent in most ways, it's giving me some trouble when it
> comes to inserts/updates on tables with column privileges.
>
> It's easy enough to tell Hibernate that certain columns are not
> insertable or not updatable, and it'll honour that, so there's no issue
> if all users have the same rights on a column. However, if rights vary
> depending on the roles and grants of the logged-in user, it's not clear
> how to inform it of that.
>
> I can always fall back on using col. privs only for columns _nobody_
> should ever insert and/or update (ie trigger managed columns) but that
> wouldn't let me cut the bulk of the ugly trigger code. Alternately, I
> could let the Hibernate classes model only the common attributes that
> all users have the rights to, and use direct JDBC calls to update
> special-privilege attributes. The latter isn't actually too bad an
> option given the relatively few places in which this is an issue... but
> I'd really prefer to be able to stay within the otherwise very smooth
> Hibernate model if possible.
>
> Anybody have related experience / ideas? I'm thinking of braving the
> Hibernate web forums too, but I thought I'd drop a note here first given
> the way it's come up as part of adopting new Pg features.
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



--
"Patriotism is the conviction that your country is superior to all
others because you were born in it." -- George Bernard Shaw

On 16/12/2009 5:06 PM, Willy-Bas Loos wrote:
>
> Do you mean that you use the postgresql role system as authentication
> and authorization mechanism in your app through hibernate?

Correct.

Actually I make a plain 'ol JDBC connection with the user-supplied
credentials to test the user's auth and do some initial work. I then
hand those credentials to the EntityManagerFactory ( a JPA2 wrapper
around SessionManagerFactory ) to create the persistence unit.

So yes, Hibernate does its work under the login of the app user. The db
enforces permissions, and if the app (via JPA2/Hibernate) tries to do
something it's not allowed to do an appropriate PersistenceException is
thrown from the JPA interface. The app responds appropriately. It's fuss
free, and ensures that authorative rules about access rights and
priveleges need be maintained in only one place - the database.

Usually the app will prevent the user from trying to perform actions
they're not allowed to, since it's nicer to have something
hidden/disabled than to try it and get a permission denied error.
However, in the end it's the DB that's authorative and the DB that's the
central respository of security and rights knowledge.

> I don't understand how that should work. How do you authenticate?

By setting the the username and password in the
hibernate.connection.username and hibernate.connection.password
properties in the Properties map passed to the EntityManagerFactory when
creating it via the Persistence entry point. Something similar is
possible when using Hibernate's APIs directly.

Alternately, you could give Hibernate an extended/wrapped
ConnectionProvider.

> As far as i know, the way to achieve what you want is through an
> authorization layer on top of your database, using ldap for example.

If I was building "Enterprise Software (TM)" in an "N Teir
Architecture(TM)", all embedded into an app server context with JNDI for
resources, sure. But I'm not - this is a plain 'ol J2SE app that needs
to talk to PostgreSQL to manipulate the data stored therein, for which
Hibernate is an extremely useful mechanism with which to do so.

> Hibernate should then access the database with a single, relatively
> highly privileged role.

In a web app context where everything goes though a midlayer, I'd agree
with that. If nothing else, you have to aggressively pool connections
and it's painful to do that if each has different credentials (though
pools like C3P0 still handle it). But in that sort of design, the
database is more likely to be a dumb data store with relatively little
knowledge of the data, and the Java midlayer in the app server controls
the business logic.

In this case, the (existing) database is the authoriative
authentication/authorization point. It has in-depth knowledge of the
meaning of the data and the rules about its manipulation. The database
embeds the business logic, and clients talk to the database to do their
work. There is no Java application server midlayer.

It's already that way, and I'm writing a Java rich GUI client for that
database. In all other ways Hibernate has proved an ideal choice for
this, and I'm somewhat frustrated by the column-privs issue.

> Alternatively, if you don't trust your application layer

It's not so much lack of trust, as putting authoriative rights in one
central place.

There are other applications that also use the database. Those must
respect the same rules and priveleges. The database enforces this. While
the Java/Hibernate UI generally avoids doing things it's not allowed to,
in the end it's the database that enforces the rules.

> you could
> define a limited number of authorization levels and create database
> roles for each.

Of course. That's how it works already - and users are GRANTEd those
roles, as you'd normally expect.

> These are not the roles that your end-users connect to
> directly. It is what hibernate would use to connect. You would
> probably have to do the object-database mapping separate for each
> level.

Now, it's that last bit I have a problem with.

In _every_ other way, Hibernate is happy working within the limits the
database sets for it. Not allowed to update that column? Fine, set
updatable=false. Etc. It's only column privs that it can't cope with,
and only because it has no apparent way to configure the entity mapping
attributes per-persistence-unit.

> For example, you could have a read-only authorization level and an edit level.
> You would assign select privileges to the "read-only" db role and
> insert, update, delete (but no ddl) to the "edit" database role.
> Then when a end-user connects to your app, you check the authorization
> system if this user has read-only or edit privileges. Then hibernate
> would connect to the database with the appropriate mapping.

You can just as easily connect to the DB with the user's credentials and
check what roles they have in INFORMATION_SCHEMA.enabled_roles to
determine what you'll allow them to do. Why all the effort to move user
management away from the DB? That just makes it harder to grant
additional roles to users ("this user can do <x> special thing"), etc.
It'd force you in the direction of implementing your own user management
system instead of using the DBs perfectly good one, maintaining tables
in the DB full of user rights information, etc. This reinvented
mechanism has to be tested and shown to be secure. It needs some sort of
access channel (you can't just use an SSL JDBC connection) so that's
something else that must be Internet-exposed. It's also available only
to Java code, so what do you do if you have other apps using the DB that
aren't written in Java?

The DB can already do the required user management with roles, grants,
and role assignments to users. I've been happily using that existing,
well tested and robust support, except for this one
headbutting-with-hibernate issue over column privs.

At this point it looks like I either have to:

  - reflectively modify my entity classes at runtime
  - generate XML mappings and modify them at runtime
  - rewrite to stop using col privs and move the logic to verbose and
error prone trigger code
  - or map only the lowest-common set of privs in Hibernate and fall
back on JDBC for the rest

Those options range from "nasty" to "awful".

--
Craig Ringer

Craig Ringer wrote:
> One of the apps using the database uses the Hibernate ORM system for
> Java. While excellent in most ways, it's giving me some trouble when it
> comes to inserts/updates on tables with column privileges.
>
> It's easy enough to tell Hibernate that certain columns are not
> insertable or not updatable, and it'll honour that, so there's no issue
> if all users have the same rights on a column. However, if rights vary
> depending on the roles and grants of the logged-in user, it's not clear
> how to inform it of that.

Hi folks

I thought I'd follow up with a solution to this issue, so that anyone
else looking for it later doesn't go completely mad.



The short answer:
=================

On entities where column privs vary based on role, set:

   selectBeforeUpdate=true, dynamicInsert=true, dynamicUpdate=true

and breathe a sigh of relief.

The explanation:
================

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:

UPDATE test
SET no_update_permission_column = no_update_permission_column;

... will fail, because permissions are checked before values are
evaluated and compared. I didn't clearly explain that earlier, but
that's why I wanted to alter the insertable= or updatable= settings on
entity properties - to prevent the associated columns from being
included in the INSERT or UPDATE list.

The reason Hibernate lists all columns in INSERT and UPDATE statements
is that it pre-generates SQL for these operations and uses the same SQL
for each operation, often as a prepared statement. By setting all fields
each time it doesn't need to record the original state of the entity or
SELECT from the database to compare with the in-memory copy before
UPDATE. Anyway, to do so it must include all fields that may ever change
in its UPDATE and INSERT queries. If you don't have permission to alter
all those fields, they'll still be included in the SQL as Hibernate
doesn't know that, so all operations will fail - even when you're only
actually changing fields you're allowed to - since you're not allowed to
even set columns you don't have permission for to their current values.

Two options exist: Either, (1) at PU init time, rewrite the Hibernate
mappings to include knowledge of user permissions, or (2) dynamically
generate INSERT and UPDATE SQL to only include columns that have
actually been set/changed.

(2) is by far the easiest. Hibernate supports generation of dynamic SQL
for every individual UPDATE and INSERT, and must simply be told to use
it. If you're using JPA2:

import javax.persistence.Entity;
import javax.persistence.Table;
@Entity
@Table(name = "myEntityTableName")
@org.hibernate.annotations.Entity(selectBeforeUpdate=true,
dynamicInsert=true, dynamicUpdate=true)
class MyEntity {
   // ....
};

(1) requires that you use Hibernate's Ejb3Configuration instead of the
usual javax.persistence.Persistence when creating an EntityManagerFactory.

// Assuming you have a Map of connection properties - otherwise use the
1-arg ctor:
Ejb3Configuration cfg = new Ejb3Configuration().configure("PuName",
connectionPropertiesMap);
Iterator it = cfg.getClassMappings();
// Do something with the class mappings or their attributes
// say, walk them and alter their insertable= and updatable= properties
// based on the results of a JDBC query for database column ACLs
// [your code to do that]
// then build the EntityManagerFactory based on the new configuration:
EntityManagerFactory factory = cfg.buildEntityManagerFactory();


I've not tested (1). (2) works fine for my purposes.

If you don't want to use selectBeforeUpdate in (2), you may instead
track the original state of your objects yourself, and provide that to
Hibernate at persist-time so it doesn't have to query the database to
find out which properties are dirty. See, eg:

https://forums.hibernate.org/viewtopic.php?f=1&t=999937

I don't need this myself and haven't bothered testing it; I'm trying to
keep my Hibernate-specific (non JPA standard) code to a minimum.

Anyway, hope this helps someone out. It's really just a reformatting of
what I wrote on the (third) Hibernate Forum question I posted about this:

https://forum.hibernate.org/viewtopic.php?f=1&t=1001854&p=2423099#p2423099

--
Craig Ringer

* Craig Ringer (craig@postnewspapers.com.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')?

It might be possible to ignore/optimize/whatever #1, perhaps, but
there's really nothing we could do about #2.  If it's #1, do other
databases which support column-level privs ignore those, or do they deny
the update like PG does today?

    Thanks,

        Stephen

Stephen Frost wrote:
> * Craig Ringer (craig@postnewspapers.com.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

Stephen Frost <sfrost@snowman.net> writes:
> This begs the question of if this is something PG should just allow
> rather than denying the update.

AFAICT, throwing a permissions error for "UPDATE SET foo = foo" is
required by the SQL standard.  There's nothing in there about "it's
okay depending on what you assign to it".

            regards, tom lane