Chapter 7. Administration

7.1. License

The pullreports.license file is obtained after software purchase. Pull Reports™ reads the file from the root of the Java classpath during WAR initialization and validates the license term and number of allowable <report>s.

The most common location for the pullreports.license file is within the WAR's WEB-INF/classes directory. However, other root classpath locations are permitted such as $CATALINA_HOME/lib in a Tomcat Servlet Container.

License verification

Pull Reports™ performs the following license checks on initialization:

  • If the current date is after the license end date, the software will log an error message and exit. The license end date is set at time of purchase and is either one or two years after the date of license receipt.

  • If the total number of <report> elements configured within the Pull Reports™ XML Catalog files listed by the catalogs initialization property exceeds the number allowed by the license, the software will log an error message and exit. The total number of <report>s is set at time of purchase and can vary between five and unlimited.

If these checks pass, Pull Reports™ writes the license term and maximum number of allowed <report>s to the ADMIN logger.

7.2. Security

7.2.1. Securing catalog and report URLs

Since all Pull Reports™ are available via regular RESTful URLs, securing a single report, all reports in a catalog, or all Pull Reports™ within a WAR only requires securing the appropriate URL pattern.

For instance, this example Pull Reports™ XML Catalog file contains two reports. The first report should be available to all users but the second only available to power users.

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="user" name="User Reports">
    <report id="user-report" name="Report available to all users">
        ...
    </report>
    <report id="power-user-report" name="Report available to power users">
        ...
    </report>
</catalog>

Additionally, this example Pull Reports™ XML Catalog file served by the same Pull Reports™ installation contains one report which should only be available to administrators.

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="admin" name="Admin Reports">
    <report id="admin-report" name="Report available to just administrators">
        ...
    </report>
</catalog>

Assuming JEE container based security configured within web.xml, the following configuration would secure the reports. The paths suffixed by .json secure the GET Catalog Information and GET Report Information endpoints.

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
    http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
    version="3.0">
     <security-constraint>
         <display-name>User</display-name>
            <web-resource-collection>
                 <web-resource-name>UserCollection</web-resource-name>
                 <url-pattern>/pullreports/catalog/user/*</url-pattern>
                 <url-pattern>/pullreports/catalog/user.json</url-pattern>
         </web-resource-collection>
         <auth-constraint>
             <role-name>USER</role-name>
             <role-name>POWERUSER</role-name>
             <role-name>ADMINISTRATOR</role-name>
         </auth-constraint>
     </security-constraint>
     <security-constraint>
         <display-name>PowerUser</display-name>
            <web-resource-collection>
                 <web-resource-name>PowerUserCollection</web-resource-name>
                 <url-pattern>/pullreports/catalog/user/report/power-user-report/*</url-pattern>
                 <url-pattern>/pullreports/catalog/user/report/power-user-report.json</url-pattern>
         </web-resource-collection>
         <auth-constraint>
             <role-name>POWERUSER</role-name>
             <role-name>ADMINISTRATOR</role-name>
         </auth-constraint>
     </security-constraint>
     <security-constraint>
         <display-name>Administrator</display-name>
            <web-resource-collection>
                 <web-resource-name>AdministratorCollection</web-resource-name>
                 <url-pattern>/pullreports/catalog/admin/*</url-pattern>
                 <url-pattern>/pullreports/catalog/admin.json</url-pattern>
         </web-resource-collection>
         <auth-constraint>
             <role-name>ADMINISTRATOR</role-name>
         </auth-constraint>
     </security-constraint>
     <security-role>
        <role-name>USER</role-name>
        <role-name>POWERUSER</role-name>
        <role-name>ADMINISTRATOR</role-name>
    </security-role>
</web-app>

7.2.2. Securing data rows

Use a <subquery> element to restrict access to rows within a <table>. Similar to securing catalog and report URLs, securing Pull Reports™ via a <subquery> depends on the security configuration of the installation WAR.

For example, if using JEE security or a Spring Security installation which employs the SecurityContextHolderAwareRequestWrapper (e.g. Spring Security 3.1 or higher), the following Pull Reports™ XML Catalog file customizes the export result based on user role. All users can export records from the cust_order table which have not yet shipped, but users with the role SUPERUSER may export all records.

See the <subquery> element for more configuration options.

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="customer" name="Customer Reports">
    <report id="order" name="Customer Orders">
        <table id="order" displayName="Order"> 
            <subquery>
            select o.item_number,o.item_name 
            from cust_order o 
            where 
            ${(request.isUserInRole('SUPERUSER')?'1=1':'o.shipped = false'}
            </subquery>
            <column id="item_number" name="item_number" displayName="Item Number" paramType="java.lang.Long"/>
            <column id="item_name" name="item_name" displayName="Item Name"/>
        </table>
    </report>
</catalog>

7.2.3. SQL Injection

In order to prevent SQL injection, Pull Reports™ passes all filter values as JDBC query parameters. This guards against the most common form of SQL injection in which user provided values are directly concatenated with a SQL statement and passed to the database unaltered.

However, Pull Reports™ does use the name attribute of <column> and <table> Pull Reports™ XML Catalog file elements as-is within SQL queries and thus malicious SQL script in these attributes poses a SQL injection risk. In order to protect against such malicious alteration of Pull Reports™ XML Catalog files, the report parser prohibits the semicolon (;) within these two name attributes.

7.2.4. Cross-Site Scripting (XSS)

The Export Report REST API is vulnerable to XSS attacks from malicious HTTP parameter values since it reflects those values back in the HTTP response of several REST API endpoints. In order to mitigate XSS attacks, the API strips suspect input patterns from all request parameters and headers. (Reference and thanks to: Ricardo Zuasti)

However, since the Export Report REST API returns data unaltered from the database, clients should take care to properly encode all output before including it in a web page if concerned about XSS.

7.3. pullreports.properties reference

Pull Reports™ reads the pullreports.properties file from the root of the JVM classpath on startup. Any property documented below with a [.catalogId][.reportId] suffix may be overridden for all <report>s within a <catalog> or a specific <report> by (.) appending the catalog and, optionally, report id to the property name.

For example, the following configuration sets the global export.report.maxResults to 10000 rows, overrides the global value for all reports in <catalog> myCatalog to 2000, and overrides the catalog value for <report> myReport in myCatalog to 3000.

export.report.maxResults=10000
export.report.maxResults.myCatalog=2000
export.report.maxResults.myCatalog.myReport=3000

The following configuration properties are available.

catalogs (Required)

White space separated list of Pull Reports™ XML Catalog files references to be parsed on Servlet initialization. Each Pull Reports™ XML Catalog file reference is either:

  • Proceeded with classpath: to indicate a file to be loaded from the classpath. With this method, the Pull Reports™ XML Catalog files would commonly be placed within the WAR's WEB-INF/classes directory.

  • Any valid java.net.URI. Common uses of this method are to load a Pull Reports™ XML Catalog file from a file external to the WAR or from a URL.

  • The fully qualified name of a Java class proceeded with factory: to indicate a factory class to produce the Pull Reports™ XML Catalog file. Use this method to dynamically create Pull Reports™ XML Catalog files from a remote source such as a database or content management system.

    The requirements of the factory class are:

    1. The class must have a constructor which takes a javax.servlet.ServletContext as its single argument.

    2. The class must have a public, no-argument method called makeCatalog which returns a java.io.Reader. Pull Reports™ will parse the Pull Reports™ XML Catalog file document from the Reader.

jndiDataSource[.catalogId][.reportId] (Required)

JNDI location (e.g. java:comp/env/myDatasource) of a javax.sql.DataSource to be used by the Pull Reports REST API.

This property must be specified for each <report> and <catalog> which requires a DataSource. All <report>s require a DataSource. Only <catalog>s with global <label_value_query>(s) require a DataSource.

To use one DataSource for all reports, simply specify a single jndiDataSource property. To customize the value per-<catalog> or per-<report>, (.) append the catalog id and, optionally, the report id to the property name respectively.

export.report.include.(...) (Optional)

Allows decoration of the Export Report REST API html and map formats via Servlet includes. See the export decoration documentation for more details.

export.report.maxResults[.catalogId][.reportId] (Optional)

The maximum allowed number of results to be returned from the Export Report REST API. Must be an Integer value. A value less than 0 indicates no maximum. If not set, the default value is 200000.

export.report.postgresql.queryCostMaximum[.catalogId][.reportId] (Optional)

The maximum permitted value of the PostgreSQL explain plan's total cost for database queries formulated by the Export Report REST API. A value of less than 0 indicates no maximum. If not set, the default value is 50000. A query which exceeds the maximum will terminate the request and return an HTTP response status code of 400.

This property is only applicable for connections to a PostgreSQL database.

export.sql.active[.catalogId][.reportId] (Default: false)

Boolean (true|false) value which controls the availability of the GET Export SQL API. If set to false, the default, a request to the API will return an HTTP status code of 404, Not Found.

Security warning!

Only set export.sql.active to true after properly securing the GET Export SQL API. Since the API returns raw SQL, it may aid attackers in mounting SQL based attacks.

static.resource.prefix (Optional)

The resource path prefix used by the map and html export formats when creating paths to Pull Reports™ JavaScript and CSS resources. The default value is the empty String. The value must be set to /static for a Grails 3 installation.

Grails 3 and static resources

In a basic WAR installation, the Servlet Container serves static resources within the pullreports-x.y.z.jar!META-INF/resources directory from at the root of the WAR's context path. Links to these resources are used within Export Report REST API formats which write HTML documents.

Within a Grails 3 installation, the same resources are served under the /static path. Setting static.resource.path=/static correctly prepends /static to the resource URLs

Example 7.1. pullreports.properties

catalogs=classpath:reports/class-information-catalog.xml \
classpath:reports/student-information-catalog.xml \
http://www.yourcompany.com/common-pullreports-catalogs/enrollment-information-catalog.xml


7.4. Export Decoration

Two Export Report REST API formats, html and map, return complete HTML documents to be displayed in a web browser. By default, the HTML is undecorated such that the Pull Reports™ export content fills the entire <body> element.

Pull Reports™ provides two techniques to decorate the exported HTML with additional HTML content: servlet includes and SiteMesh.

7.4.1. Servlet includes

Configure the inclusion of html or jsp content via export.report.include-prefixed configuration properties. The value of these properties is a resource path within the deployed WAR to be include via RequestDispatcher#include into one of three places within the HTML DOM

  • before the close of the <head> element

  • after the start of the <body> element

  • before the close of the <body> element

The export.report.include-prefixed configuration properties may be specified multiple times within pullreports.properties to specify or override behavior for specific formats, HTTP response codes, <catalog>s, or <report>s.

7.4.1.1. export.report.include property name structure

The structure of export.report.include-prefixed configuration property names is:

export.report.include[.html|.map].(head.end|body.start|body.end)[.400|.500][.catalogId][.reportId]

Where (...) demarcated terms are required and [...] demarcated terms are optional. The | character separates the permitted values for a particular term.

When resolving an inclusion value for a particular report, Pull Reports™ follows these rules to determine precedence:

  • Properties specified for a particular report via [.catalogId][.reportId] take precedence over values specified for a particular catalog via [.catalogId] which take precedence over values specified without reference to a catalog or report.

  • Properties specified for either the html or map format via [.html|.map] take precedence over values specified without a format.

  • In the case of a 400 or 500 HTTP response, properties specified with a [.400|.500] status code take precedence over values specified without status.

Simple example:  Here is a simple example which includes a navigation.html header at the top of all html and map export formats:

export.report.include.body.start=/WEB-INF/content/navigation.html

To override the property for a all <report>s in <catalog id='my-catalog'>, add a second property suffixed with the catalog id:

export.report.include.body.start=/WEB-INF/content/navigation.html
export.report.include.body.start.my-catalog=/WEB-INF/content/my-catalog-navigation.html

See additional configuration examples below.

7.4.1.2. export.report.include property name terms

[.html|.map]

Specify either the html or map term to apply the property to the html or map format respectively. If not specified, the value of the property applies to both formats.

(head.end|body.start|body.end) (Required)

Specify the inclusion location with the HTML document via one of the three following terms.

  • head.end: before the close of the <head> element

  • body.start: after the start of the <body> element

  • body.end: before the close of the <body> element

[.400|.500]

Specify either the 400 or 500 term to apply the property to the respective HTML response code. If not specified, the value of the property applies to all response codes.

[.catalogId][.reportId]

Specify a <catalog> id and, optionally, <report> id to limit the property to a specific catalog or report.

7.4.1.3. export.report.include property examples

The following example pullreports.properties contains several permutations of the export.report.include-prefixed properties with explanatory comments.

# Configures default body.start and body.end includes for all responses 
# of 500 (error) status
export.report.include.body.start.500=/WEB-INF/error-header.html
export.report.include.body.end.500=/WEB-INF/error-footer.html

# Configures default body.start and body.end includes for all responses 
# of 400 (bad request / validation error) status
export.report.include.body.start.400=/WEB-INF/validation-error-header.html
export.report.include.body.end.400=/WEB-INF/validation-error-footer.html

# Configures default head.end include for all export results of format type 
# map. The included file might contain CSS or JavaScript code to customize 
# the map export.
export.report.include.map.head.end=/WEB-INF/map-head-include.jsp

# Configures default body.start and body.end includes for all responses. 
# These includes could contain common header navigation and common footer 
# elements respectively.
export.report.include.body.start=/WEB-INF/page-header.jsp
export.report.include.body.end=/WEB-INF/page-footer.jsp

# Override the includes for <catalog id='my-catalog'>.
export.report.include.body.start.my-catalog=/WEB-INF/my-catalog-page-header.jsp
export.report.include.body.end.my-catalog=/WEB-INF/my-catalog-page-footer.jsp

# Override the 400 head include for the html format for <report id='my-report'> 
# within <catalog id='my-catalog'>.
export.report.include.html.head.end.400.my-catalog.my-report=\
/WEB-INF/validation-error-html-head-include-my-report.html

7.4.2. SiteMesh

Follow these steps to decorate the exported HTML with the SiteMesh framework. The following steps assume SiteMesh version 2.4.

  1. Make the SiteMesh JAR available to the web application into which Pull Reports™ is installed by placing the JAR within the WEB-INF/lib directory.

    If using Gradle for dependency management, add the following dependency to include SiteMesh:

    runtime 'opensymphony:sitemesh:2.4.2'

  2. Install the SiteMesh filter in web.xml.

    <filter>
      <filter-name>sitemesh</filter-name>
      <filter-class>com.opensymphony.sitemesh.webapp.SiteMeshFilter</filter-class>
    </filter>
    <filter-mapping>
      <filter-name>sitemesh</filter-name>
      <url-pattern>/pullreports/catalog/*</url-pattern>
    </filter-mapping>

  3. Install the sitemesh.xml file within the WEB-INF directory. Configure a ParameterDecoratorMapper within the <decorator-mappers> list. The value attribute must be set to format to correspond to the Export Report REST API format parameter.

    <?xml version="1.0" encoding="UTF-8"?>
    <sitemesh>
        <property name="decorators-file" value="/WEB-INF/decorators.xml" />
        <excludes file="${decorators-file}"/>
        <page-parsers>
            <parser content-type="text/html" class="com.opensymphony.module.sitemesh.parser.HTMLPageParser" />
        </page-parsers>
        <decorator-mappers>
            <mapper class="com.opensymphony.module.sitemesh.mapper.ParameterDecoratorMapper">
                <param name="decorator.parameter" value="format" />
            </mapper>
            <mapper class="com.opensymphony.module.sitemesh.mapper.ConfigDecoratorMapper">
                <param name="config" value="${decorators-file}" />
            </mapper>
        </decorator-mappers>
    </sitemesh>
  4. Install the decorators.xml file within the WEB-INF directory. Configure two <decorators> with the names html and map respectively.

    <?xml version="1.0" encoding="UTF-8"?>
    <decorators defaultdir="/WEB-INF/decorators">
        <decorator name="html" page="my-html-decorator.jsp"></decorator>
        <decorator name="map" page="my-map-decorator.jsp"></decorator>
    </decorators>
  5. Place the decorator JSP files referenced within the previous step into the WEB-INF/decorators directory. Then use the SiteMesh custom tags within these JSP files to apply the decoration.

    Example 7.2. Example my-html-decorator.jsp
    <%@ taglib uri="http://www.opensymphony.com/sitemesh/decorator" prefix="decorator" %>
    <!DOCTYPE html>
    <html>
    <head>
        <title>Demonstration HTML format decorator</title>
        <decorator:head />
        <script>
        // Custom javascript here.
        </script>
    </head>
    <body>
        <header><!-- Custom header here --></header>
        <decorator:body />
        <footer><!-- Custom footer here --></footer>
    </body>
    </html>
                            

    Example 7.3. Example my-map-decorator.jsp

    This decorator demonstrates the use of the mapConfig JavaScript variable to customize the Leaflet Mapper.

    <%@ taglib uri="http://www.opensymphony.com/sitemesh/decorator" prefix="decorator" %>
    <!DOCTYPE html>
    <html>
    <head>
        <title>Demonstration Map format decorator</title>
        <decorator:head />
        <script>
            var baseLayers = [];
            baseLayers['ESRI World Topo'] = 
                L.tileLayer('http://server.arcgisonline.com/ArcGIS/rest/services/World_Topo_Map/MapServer/tile/{z}/{y}/{x}', {
                attribution: 'Tiles &copy; Esri &mdash; Esri, DeLorme, NAVTEQ, TomTom, Intermap, iPC, USGS, FAO, NPS, NRCAN, GeoBase, Kadaster NL, Ordnance Survey, Esri Japan, METI, Esri China (Hong Kong), and the GIS User Community'
                ,maxZoom:18
            });
            baseLayers['Open Street Map'] = 
                L.tileLayer('http://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {
                attribution: 'Map data © <a href="http://openstreetmap.org">OpenStreetMap</a> contributors'
                ,maxZoom: 18
            });
            
            function initializeMap(map,geojsonLayer){
                L.control.layers(baseLayers, {'Student residences':geojsonLayer}).addTo(map);
            }
    
            var mapConfig = {
                options: {
                    layers:[baseLayers['Open Street Map']]
                }
                ,initCallback:initializeMap
                ,geoJsonOptions: {
                    // Customize the Leaflet popup.
                    onEachFeature:function(feature,layer){
                        layer.bindPopup(function(){
                            return '<h3>' + feature.properties['Some Property'] + '</h3>';
                        });
                    } 
                    // Customize the GeoJSON style
                    ,style:{"color": "#FA8072",
                        "weight": 5,
                        "opacity": 0.8
                    }
                }
            };
        </script>
    </head>
    <body>
        <header><!-- Custom header here --></header>
        <decorator:body />
        <footer><!-- Custom footer here --></footer>
    </body>
    </html>
                            

After performing these steps, a request to [/context]/pullreports/catalog/my-catalog/report/my-report/export?format=html will be decorated with the my-html-decorator.jsp decorator and a request to [/context]/pullreports/catalog/my-catalog/report/my-report/export?format=map will be decorated with the my-map-decorator.jsp decorator.

For more information about configuring SiteMesh, see the SiteMesh documentation.

7.5. Logging

Pull Reports™ uses the SLF4J API for logging. To configure logging, see the installation guide which corresponds to your Pull Reports™ deployment environment.

7.5.1. Named Loggers

The following named SLF4J Loggers control log output associated with specific Pull Reports™ functions.

Table 7.1. SLF4J Loggers
Logger NameDescription
com.pullreports.ADMINLogs administration information typically at the info level. For instance, logs license information such as the term of the license and maximum number of report instances upon Pull Reports™ start up.
com.pullreports.SQL

Logs SQL queries sent to the database. Set to debug to see all database queries and parameters.

See below for how to configure the logger for a specific <catalog> or <report>.

com.pullreports.HTTP

Logs problems with HTTP parameters sent to the PullReportsServlet.

See below for how to configure the logger for a specific <catalog> or <report>.


7.5.2. Logging for a specific <catalog> or <report>

To isolate com.pullreports.SQL or com.pullreports.HTTP logger configurations to a specific catalog, (.) append the <catalog> and, optionally, <report> id to the logger.

Example 7.4. SQL logger <catalog> my-catalog
com.pullreports.SQL.my-catalog

Example 7.5. SQL logger <catalog> my-catalog and <report> my-report
com.pullreports.SQL.my-catalog.my-report