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

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