[Geoserver-devel] JDBC session startup and teardown in GeoServer

GeoServer GeoServer Developer
1

Hi all,
in this mail I'm following up a discussion that was started on the
GeoTools list,
here discussing the GeoServer bits:
http://osgeo-org.1803224.n2.nabble.com/JDBC-store-allowing-a-customizable-sql-commands-before-and-after-jdbc-connection-use-td7039138.html#a7043676

Executive summary:
- some users with a strong db centric approach to application and
security management
  want that all the database access runs with the credentials of the
current GeoServer
  user instead of the the generic connection pool user
- databases often offer this functionality in different ways, but
almost always this is available
  as some sql command to run that switches the current user, and
another to run to get
  the connection back to the standard pool user
- sometimes more sophisticated functionality is built on top of that,
which allows for
  accounting and resource usage control, which requires to run custom
sql scripts instead
  of the normal database commands

The GeoTools patch at
http://jira.codehaus.org/browse/GEOT-3994
adds the support for the above by introducing two new params, "sql on
borrow" and "sql on release".
The sql commands are parametric, an enviroment variable from EnvFunction can be
expanded when running the sql commands (along with a default value).

The GeoServer side of the patch is at
http://jira.codehaus.org/browse/GEOS-4918
it does two things:
- adds some GUI support for these longer than usual parameters (using text areas
  instead of normal text fields. This is actually affects the largest
number of files
  only because I noticed most param panels had a getFormComponent() method,
  which I uniformed into a interface so that I could treat text field
and text area
  param panels the same way
- adds a dispatcher callback that automatically injects the GSUSER
parameter into
  the enviroment map, and while I was at it, also centralizes the management of
  the env map into a single place (before it was sprinkled in
  GetMap/GetLegendGraphics/GetFeatureInfo runtime code and a dispatcher
  callback for the sole cleanup, so this change is also a nice cleanup)

Of course while the patch is built to support calling impersonation
commands, any
parametric sql can be run, which I believe can have the same kind of usefulness
as parametri sql views, e.g., allows more flexibility and native
database enviroment
usage for those that have such needs.

Opinions?

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
mob: +39 339 8844549

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

Please take note that GeoSolutions will be closed for Christmas
holidays from 27/12 to 30/12

-------------------------------------------------------

2

Sounds awesome. I’ve definitely heard a user desire for this - it’s particularly important for ‘big enterprise’ style users. And the generic approach here sounds really nice.

Key to getting this widely used seems to be good docs on the most common case. So I hope you guys have the time to document it decently, but of course if you don’t have the time it shouldn’t be a blocker at all, will be great to have the functionality there.

On Tue, Jan 3, 2012 at 10:14 AM, Andrea Aime <andrea.aime@anonymised.com> wrote:

Hi all,
in this mail I’m following up a discussion that was started on the
GeoTools list,
here discussing the GeoServer bits:
http://osgeo-org.1803224.n2.nabble.com/JDBC-store-allowing-a-customizable-sql-commands-before-and-after-jdbc-connection-use-td7039138.html#a7043676

Executive summary:

  • some users with a strong db centric approach to application and
    security management
    want that all the database access runs with the credentials of the
    current GeoServer
    user instead of the the generic connection pool user
  • databases often offer this functionality in different ways, but
    almost always this is available
    as some sql command to run that switches the current user, and
    another to run to get
    the connection back to the standard pool user
  • sometimes more sophisticated functionality is built on top of that,
    which allows for
    accounting and resource usage control, which requires to run custom
    sql scripts instead
    of the normal database commands

The GeoTools patch at
http://jira.codehaus.org/browse/GEOT-3994
adds the support for the above by introducing two new params, “sql on
borrow” and “sql on release”.
The sql commands are parametric, an enviroment variable from EnvFunction can be
expanded when running the sql commands (along with a default value).

The GeoServer side of the patch is at
http://jira.codehaus.org/browse/GEOS-4918
it does two things:

  • adds some GUI support for these longer than usual parameters (using text areas
    instead of normal text fields. This is actually affects the largest
    number of files
    only because I noticed most param panels had a getFormComponent() method,
    which I uniformed into a interface so that I could treat text field
    and text area
    param panels the same way
  • adds a dispatcher callback that automatically injects the GSUSER
    parameter into
    the enviroment map, and while I was at it, also centralizes the management of
    the env map into a single place (before it was sprinkled in
    GetMap/GetLegendGraphics/GetFeatureInfo runtime code and a dispatcher
    callback for the sole cleanup, so this change is also a nice cleanup)

Of course while the patch is built to support calling impersonation
commands, any
parametric sql can be run, which I believe can have the same kind of usefulness
as parametri sql views, e.g., allows more flexibility and native
database enviroment
usage for those that have such needs.

Opinions?

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
mob: +39 339 8844549

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

Please take note that GeoSolutions will be closed for Christmas
holidays from 27/12 to 30/12

Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create
new or port existing apps to sell to consumers worldwide. Explore the
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev

Geoserver-devel mailing list
Geoserver-devel@anonymised.comsts.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

3

Hi Andrea, good stuff, and a +1.

Just a little note:

- adds some GUI support for these longer than usual parameters (using text areas
instead of normal text fields. This is actually affects the largest
number of files
only because I noticed most param panels had a getFormComponent() method,
which I uniformed into a interface so that I could treat text field
and text area
param panels the same way

That is because a couple years ago, the guy (ehem...) that wrote it
didn't know about FormComponentPanel. I think all those param panels
should be updated to extend FormComponentPanel instead and get rid of
the weird getFormComponent() method.

So not a requirement, but if you want to go for it... I'd be extremely thankful.

Cheers!
Gabriel

4

On Tue, Jan 3, 2012 at 4:56 PM, Chris Holmes <cholmes@anonymised.com> wrote:

Sounds awesome. I've definitely heard a user desire for this - it's
particularly important for 'big enterprise' style users. And the generic
approach here sounds really nice.

Key to getting this widely used seems to be good docs on the most common
case. So I hope you guys have the time to document it decently, but of
course if you don't have the time it shouldn't be a blocker at all, will be
great to have the functionality there.

I definitely want to provide some documentation, but there will be
room for improvement.

For sure a screenshot with the two parameters, an explanation, syntax for
the parameter substitution and an example usage for one
database (maybe PostgreSQL), however it would be nice to have ready to use
scripts to perform impersonation on other database, or examples outside of
the impersonation use case. Contributions on this topic would be most welcomed

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
mob: +39 339 8844549

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

Please take note that GeoSolutions will be closed for Christmas
holidays from 27/12 to 30/12

-------------------------------------------------------

5

Looked over the patches and they look ok to me. +1

On Tue, Jan 3, 2012 at 4:38 PM, Andrea Aime <andrea.aime@anonymised.com> wrote:

On Tue, Jan 3, 2012 at 4:56 PM, Chris Holmes <cholmes@anonymised.com1…> wrote:

Sounds awesome. I’ve definitely heard a user desire for this - it’s
particularly important for ‘big enterprise’ style users. And the generic
approach here sounds really nice.

Key to getting this widely used seems to be good docs on the most common
case. So I hope you guys have the time to document it decently, but of
course if you don’t have the time it shouldn’t be a blocker at all, will be
great to have the functionality there.

I definitely want to provide some documentation, but there will be
room for improvement.

For sure a screenshot with the two parameters, an explanation, syntax for
the parameter substitution and an example usage for one
database (maybe PostgreSQL), however it would be nice to have ready to use
scripts to perform impersonation on other database, or examples outside of
the impersonation use case. Contributions on this topic would be most welcomed

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
mob: +39 339 8844549

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

Please take note that GeoSolutions will be closed for Christmas
holidays from 27/12 to 30/12

Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create
new or port existing apps to sell to consumers worldwide. Explore the
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev

Geoserver-devel mailing list
Geoserver-devel@anonymised.comsts.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

–
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.

6

On Tue, Jan 3, 2012 at 4:56 PM, Chris Holmes <cholmes@anonymised.com> wrote:

Sounds awesome. I’ve definitely heard a user desire for this - it’s particularly important for ‘big enterprise’ style users. And the generic approach here sounds really nice.

Key to getting this widely used seems to be good docs on the most common case. So I hope you guys have the time to document it decently, but of course if you don’t have the time it shouldn’t be a blocker at all, will be great to have the functionality there.

For those that are interested, the work has been committed on both GeoTools and GeoServer,
and I’ve rolled out a documentation page for it here:

http://docs.geoserver.org/latest/en/user/data/data/images/postgresqlSession.png

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
mob: +39 339 8844549

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

7

On Tue, Jan 24, 2012 at 11:49 AM, Andrea Aime <andrea.aime@anonymised.com…1268…> wrote:

On Tue, Jan 3, 2012 at 4:56 PM, Chris Holmes <cholmes@anonymised.com> wrote:

Sounds awesome. I’ve definitely heard a user desire for this - it’s particularly important for ‘big enterprise’ style users. And the generic approach here sounds really nice.

Key to getting this widely used seems to be good docs on the most common case. So I hope you guys have the time to document it decently, but of course if you don’t have the time it shouldn’t be a blocker at all, will be great to have the functionality there.

For those that are interested, the work has been committed on both GeoTools and GeoServer,
and I’ve rolled out a documentation page for it here:

http://docs.geoserver.org/latest/en/user/data/data/images/postgresqlSession.png

Ah hem, wrong link, here is the good one:

http://docs.geoserver.org/latest/en/user/data/sqlsession.html

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
mob: +39 339 8844549

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