From: Andrea A. <and...@ge...> - 2011-11-28 15:36:40
|
Hi, in a project we're working on we are facing a intersting request: the ability to run arbitrary sql commands before a connection is used by the code, and right before the connection is returned to the pool. The intent of such request is to allow do some custom environmental setup in that connection before it is used, in particular to setup impersonation (the ability to run commands with the credentials of another user) using some in-house grant and account package (e.g., not the standard impersonation commands). These sql commands would be parametric, using the usual cql variable expansion we already see in dynamic symbolizers (that, is, ${cql_expression}). The code in GeoServer would stick the current user in the "env" map, using a reserved key that cannot be used in normal request parameters, so that the session setup code can run the impersonation. However, being open ended, the code could be used for other purposes, such as for example switching workspace in oracle workspace manager, or setting some variables that stored procedure calls could use later, etc etc. The two commands would be specified as factory parameters. We already have an hook to initialize a connection, in order to perform the cleanup part we could simply use a wrapper that runs the other script when "close" is called. What do you think? Cheers Andrea -- ------------------------------------------------------- Ing. Andrea Aime GeoSolutions S.A.S. Tech lead Via Poggio alle Viti 1187 55054 Massarosa (LU) Italy phone: +39 0584 962313 fax: +39 0584 962313 http://www.geo-solutions.it http://geo-solutions.blogspot.com/ http://www.youtube.com/user/GeoSolutionsIT http://www.linkedin.com/in/andreaaime http://twitter.com/geowolf ------------------------------------------------------- |
From: Justin D. <jde...@op...> - 2011-11-29 05:09:18
|
+1 on the idea in general. Quite similar to a use case I had with teradata, and query banding. So if I understand correctly... we will see two new jdbc factory parameters. The first being "pre" connection... and would be executed from SQLDialect.initializeConnection() (or some wrapper of it)? In order to execute the "post" sql do we need a new dialect callback method? Rather than use a wrapper. An alternative idea might be rather than using factory parameters directly to come up with a "ConnectionLifecycleListener" interface or something like that. Would be a bit more general and you could always use that to easily come up with an implementation that would simply execute sql stored in some factory parameters. Also would cater to cases where maybe some other stuff needs to happen pre or post connection event, not just some sql statements being executed. Just an idea. -Justin On Mon, Nov 28, 2011 at 7:36 AM, Andrea Aime <and...@ge...>wrote: > Hi, > in a project we're working on we are facing a intersting request: the > ability to run > arbitrary sql commands before a connection is used by the code, and right > before > the connection is returned to the pool. > > The intent of such request is to allow do some custom environmental > setup in that > connection before it is used, in particular to setup impersonation > (the ability to run > commands with the credentials of another user) using some in-house grant > and > account package (e.g., not the standard impersonation commands). > > These sql commands would be parametric, using the usual cql variable > expansion > we already see in dynamic symbolizers (that, is, ${cql_expression}). > > The code in GeoServer would stick the current user in the "env" map, > using a reserved > key that cannot be used in normal request parameters, so that the session > setup > code can run the impersonation. > > However, being open ended, the code could be used for other purposes, such > as > for example switching workspace in oracle workspace manager, or setting > some > variables that stored procedure calls could use later, etc etc. > > The two commands would be specified as factory parameters. We already have > an hook to initialize a connection, in order to perform the cleanup > part we could > simply use a wrapper that runs the other script when "close" is called. > > What do you think? > > Cheers > Andrea > > -- > ------------------------------------------------------- > Ing. Andrea Aime > GeoSolutions S.A.S. > Tech lead > > Via Poggio alle Viti 1187 > 55054 Massarosa (LU) > Italy > > phone: +39 0584 962313 > fax: +39 0584 962313 > > http://www.geo-solutions.it > http://geo-solutions.blogspot.com/ > http://www.youtube.com/user/GeoSolutionsIT > http://www.linkedin.com/in/andreaaime > http://twitter.com/geowolf > > ------------------------------------------------------- > > > ------------------------------------------------------------------------------ > All the data continuously generated in your IT infrastructure > contains a definitive record of customers, application performance, > security threats, fraudulent activity, and more. Splunk takes this > data and makes sense of it. IT sense. And common sense. > http://p.sf.net/sfu/splunk-novd2d > _______________________________________________ > Geotools-devel mailing list > Geo...@li... > https://lists.sourceforge.net/lists/listinfo/geotools-devel > -- Justin Deoliveira OpenGeo - http://opengeo.org Enterprise support for open source geospatial. |
From: Andrea A. <and...@ge...> - 2011-11-29 10:03:26
|
On Tue, Nov 29, 2011 at 6:09 AM, Justin Deoliveira <jde...@op...>wrote: > +1 on the idea in general. Quite similar to a use case I had with > teradata, and query banding. > > So if I understand correctly... we will see two new jdbc factory > parameters. The first being "pre" connection... and would be executed from > SQLDialect.initializeConnection() (or some wrapper of it)? In order to > execute the "post" sql do we need a new dialect callback method? Rather > than use a wrapper. > I'm not sure about the usage of the dialect. Let me try to elaborate: - the dialect takes care or something that is database specific, but always equal as long as the db is the same - these init and release commands are likely to be related to the database type (but not necesssarily), what makes them vary is the particular instance. For example, I might have one database and two Oracle stores, one hitting a schema that contains the public data which does not need the scripts, and another hitting some sensitive data that needs the impersonation and accounting scripts As for having a new dialect callback, if a dialect pops up that needs it I see no problem in adding it. Regarding the wrapper vs no wrapper, I thought about the wrapper because anybody is free to call Connection.close() wherever they want, a wrapper would ensure that the closing down stament is executed. Now, theoretically JDBCDataStore provides a closeSafe(connection) method that could be used as the central closing point where the session closing script could be executed, but implementations are not using it uniformly, a search with find and grep shows the following results (just a sampler, I'm not listing them all): ./jdbc-db2/src/main/java/org/geotools/data/db2/DB2SQLDialect.java: try {if (con!=null) con.close();} catch (SQLException ex1) {}; ./jdbc-spatialite/src/main/java/org/geotools/data/spatialite/SpatiaLiteDialect.java: st.close(); ./jdbc-teradata/src/main/java/org/geotools/data/teradata/TeradataDialect.java: st.close(); ./jdbc-teradata/src/main/java/org/geotools/data/teradata/TeradataPrimaryKeyFinder.java: st.close(); Since we cannot rely on implementors to always remember to call closeSafe(cx) I think it's better to use a connection wrapper instead. > > An alternative idea might be rather than using factory parameters directly > to come up with a "ConnectionLifecycleListener" interface or something like > that. Would be a bit more general and you could always use that to easily > come up with an implementation that would simply execute sql stored in some > factory parameters. Also would cater to cases where maybe some other stuff > needs to happen pre or post connection event, not just some sql statements > being executed. > So we would have a default implementation taking the two factory parameters with the sql scripts (which would be part of the base factory class) and then have the JDBCDataStore have a getLifecycleListeners() returning a live collection that the caller can use to customize it? I could also go without the factory parameters, but then I'd have to roll something custom in the GeoServer configuration panels (like the sql views, so it would be code percolating down into the ResourcePool) since the actual requirement I have is to be able to configure those two scripts from the GeoServer GUI (without any fork or custom code). Cheers Andrea -- ------------------------------------------------------- Ing. Andrea Aime GeoSolutions S.A.S. Tech lead Via Poggio alle Viti 1187 55054 Massarosa (LU) Italy phone: +39 0584 962313 fax: +39 0584 962313 http://www.geo-solutions.it http://geo-solutions.blogspot.com/ http://www.youtube.com/user/GeoSolutionsIT http://www.linkedin.com/in/andreaaime http://twitter.com/geowolf ------------------------------------------------------- |
From: Justin D. <jde...@op...> - 2011-11-29 16:52:56
|
On Tue, Nov 29, 2011 at 2:03 AM, Andrea Aime <and...@ge...>wrote: > On Tue, Nov 29, 2011 at 6:09 AM, Justin Deoliveira <jde...@op...>wrote: > >> +1 on the idea in general. Quite similar to a use case I had with >> teradata, and query banding. >> >> So if I understand correctly... we will see two new jdbc factory >> parameters. The first being "pre" connection... and would be executed from >> SQLDialect.initializeConnection() (or some wrapper of it)? In order to >> execute the "post" sql do we need a new dialect callback method? Rather >> than use a wrapper. >> > > I'm not sure about the usage of the dialect. Let me try to elaborate: > - the dialect takes care or something that is database specific, but > always equal as long as the db is the same > - these init and release commands are likely to be related to the database > type (but not necesssarily), what > makes them vary is the particular instance. For example, I might have > one database and two Oracle stores, > one hitting a schema that contains the public data which does not need > the scripts, and another hitting some > sensitive data that needs the impersonation and accounting scripts > > As for having a new dialect callback, if a dialect pops up that needs it I > see no problem in adding it. > > Regarding the wrapper vs no wrapper, I thought about the wrapper because > anybody is free to call > Connection.close() wherever they want, a wrapper would ensure that the > closing down stament > is executed. > > Now, theoretically JDBCDataStore provides a closeSafe(connection) method > that could be used > as the central closing point where the session closing script could be > executed, but implementations > are not using it uniformly, a search with find and grep shows the > following results (just a sampler, > I'm not listing them all): > > ./jdbc-db2/src/main/java/org/geotools/data/db2/DB2SQLDialect.java: > try {if (con!=null) con.close();} catch (SQLException ex1) {}; > ./jdbc-spatialite/src/main/java/org/geotools/data/spatialite/SpatiaLiteDialect.java: > st.close(); > ./jdbc-teradata/src/main/java/org/geotools/data/teradata/TeradataDialect.java: > st.close(); > ./jdbc-teradata/src/main/java/org/geotools/data/teradata/TeradataPrimaryKeyFinder.java: > st.close(); > > Since we cannot rely on implementors to always remember to call > closeSafe(cx) I think it's better > to use a connection wrapper instead. > Right, makes sense. Thanks for clarifying. > > >> >> An alternative idea might be rather than using factory parameters >> directly to come up with a "ConnectionLifecycleListener" interface or >> something like that. Would be a bit more general and you could always use >> that to easily come up with an implementation that would simply execute sql >> stored in some factory parameters. Also would cater to cases where maybe >> some other stuff needs to happen pre or post connection event, not just >> some sql statements being executed. >> > > So we would have a default implementation taking the two factory > parameters with the sql scripts > (which would be part of the base factory class) and then have the > JDBCDataStore have a > getLifecycleListeners() returning a live collection that the caller can > use to customize it? > > I could also go without the factory parameters, but then I'd have to roll > something custom in the GeoServer > configuration panels (like the sql views, so it would be code percolating > down into the ResourcePool) > since the actual requirement I have is to be able to configure those two > scripts from the GeoServer GUI > (without any fork or custom code). > Well my thought is that we would just add one parameter (that would be a classname, well collection of them) pointing to a set of ConnectionLifeCycle instances, and then any other factory parameters or anything needed would be implementation details, and not necessarily declared by the factory. But I guess that doesn't work so well if you have a requirement not to make any of this code custom to your app... just a bit worried this might be a bit specific to this particular application. Not a strong opinion, just first impression. > > Cheers > Andrea > > > -- > ------------------------------------------------------- > Ing. Andrea Aime > GeoSolutions S.A.S. > Tech lead > > Via Poggio alle Viti 1187 > 55054 Massarosa (LU) > Italy > > phone: +39 0584 962313 > fax: +39 0584 962313 > > http://www.geo-solutions.it > http://geo-solutions.blogspot.com/ > http://www.youtube.com/user/GeoSolutionsIT > http://www.linkedin.com/in/andreaaime > http://twitter.com/geowolf > > ------------------------------------------------------- > -- Justin Deoliveira OpenGeo - http://opengeo.org Enterprise support for open source geospatial. |
From: Andrea A. <and...@ge...> - 2011-11-29 17:23:17
|
On Tue, Nov 29, 2011 at 5:52 PM, Justin Deoliveira <jde...@op...>wrote: > Well my thought is that we would just add one parameter (that would be a > classname, well collection of them) pointing to a set of > ConnectionLifeCycle instances, and then any other factory parameters or > anything needed would be implementation details, and not necessarily > declared by the factory. But I guess that doesn't work so well if you have > a requirement not to make any of this code custom to your app... just a bit > worried this might be a bit specific to this particular application. Not a > strong opinion, just first impression. > My first proposed approach was to add a flag to allow impersonation and use the standard impersonation tools that every database provides. This would have been relatively to the point, and I though it would have been received as is (but I may be wrong). However the people I'm talking to have their own impersonation and accounting package, so we moved to customizable and templatable SQL, which is also more generic than the above, allowing other types of setup other than impersonation. Rolling a customizable interface along with it makes it even more generic. If the opinion is not strong I'll move along with this approach, otherwise I fear I'll have to turn down this opportunity. Cheers Andrea -- ------------------------------------------------------- Ing. Andrea Aime GeoSolutions S.A.S. Tech lead Via Poggio alle Viti 1187 55054 Massarosa (LU) Italy phone: +39 0584 962313 fax: +39 0584 962313 http://www.geo-solutions.it http://geo-solutions.blogspot.com/ http://www.youtube.com/user/GeoSolutionsIT http://www.linkedin.com/in/andreaaime http://twitter.com/geowolf ------------------------------------------------------- |
From: Jody G. <jod...@gm...> - 2011-11-30 14:57:17
|
The approach sounds okay; but a pain to document the complexity. Sounds like you should move along with this approach. -- Jody Garnett On Wednesday, 30 November 2011 at 3:23 AM, Andrea Aime wrote: > On Tue, Nov 29, 2011 at 5:52 PM, Justin Deoliveira <jde...@op... (mailto:jde...@op...)> wrote: > > Well my thought is that we would just add one parameter (that would be a classname, well collection of them) pointing to a set of ConnectionLifeCycle instances, and then any other factory parameters or anything needed would be implementation details, and not necessarily declared by the factory. But I guess that doesn't work so well if you have a requirement not to make any of this code custom to your app... just a bit worried this might be a bit specific to this particular application. Not a strong opinion, just first impression. > > My first proposed approach was to add a flag to allow impersonation and use the standard impersonation tools > that every database provides. This would have been relatively to the point, and I though it would have been > received as is (but I may be wrong). > > However the people I'm talking to have their own impersonation and accounting package, so we moved > to customizable and templatable SQL, which is also more generic than the above, allowing other types > of setup other than impersonation. > Rolling a customizable interface along with it makes it even more generic. > If the opinion is not strong I'll move along with this approach, otherwise I fear I'll have to turn down this > opportunity. > > Cheers > Andrea > > > -- > ------------------------------------------------------- > Ing. Andrea Aime > GeoSolutions S.A.S. > Tech lead > > Via Poggio alle Viti 1187 > 55054 Massarosa (LU) > Italy > > phone: +39 0584 962313 > fax: +39 0584 962313 > > http://www.geo-solutions.it > http://geo-solutions.blogspot.com/ > http://www.youtube.com/user/GeoSolutionsIT > http://www.linkedin.com/in/andreaaime > http://twitter.com/geowolf > > ------------------------------------------------------- > ------------------------------------------------------------------------------ > All the data continuously generated in your IT infrastructure > contains a definitive record of customers, application performance, > security threats, fraudulent activity, and more. Splunk takes this > data and makes sense of it. IT sense. And common sense. > http://p.sf.net/sfu/splunk-novd2d > > _______________________________________________ > Geotools-devel mailing list > Geo...@li... (mailto:Geo...@li...) > https://lists.sourceforge.net/lists/listinfo/geotools-devel > > |