Обсуждение: 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