Re: Column privileges and Hibernate - Mailing list pgsql-general

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