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@xxxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) > 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
