All posts in “ba-server”

Setup Pentaho BA Server to use SSL Certificates

SSL Certificate Java Truststore Setup

SSL, or Secure Socket Layer, is a technology which allows web browsers and web servers to communicate over a secured connection. This means that during our initial attempt to communicate with a web server it will present our web browser with a set of credentials, in the form of a “Certificate”, as proof the site is who and what it claims to be. In certain cases, the server may also request a Certificate from our web browser, asking for proof that we are who we claim to be.

This post will go through the process required to have our Pentaho Server using SSL certificates.

Create your SSL key/certificate

First thing to do is to create the SSL certificate key for our tomcat server to use. And then we will need to tell our tomcat server to allow HTTPS connections. To do this in the Pentaho Server we edit a file named “server.xml”, referenced here:

And here :

For this exercise we will be using the Java KeyStore (JKS). A keystore manages the provision of the client private keys/certificates we use when we try to access a server, and a truststore (cacerts in Java) manages the verification of those keys/certificates. 

In order to do create the key we need to create a new JKS keystore, containing a single self-signed key/certificate.  Execute the following from a terminal command line:

$ keygen -genkey -alias tomcat -keyalg RSA

This command will create a new file, in the home directory of our user named “.keystore”. To specify a different location or filename, add the -keystore parameter, followed by the complete path to our keystore file. We will also need to reflect this new location in the server.xml configuration file.

        1. First we are prompted for a password for the keystore and later for a password for the actual key. (Strangely in older tomcat versions they need to be the same) This keystore password will have to be given to our server application so it can access our server side keys. Note – The default password used by Tomcat is “changeit” (all lower case).
        2. After deciding on a password, a number of questions need to be answered as to certify the authenticity of our server.
        3. Finally we are prompted for the password for the actual tomcat alias key. If any issues come up in this step, check if you need to have the same password as for the datastore.

Ok, we have a keystore where Java can find certificate keys to show servers when prompted, with one key with the alias tomcat. Now we need to tell Java to use this key in their trustore. We export the key to a *.cer file and go tell Java that he should incorporate this key in cacerts, Javas’ truststore.

$ keytool -export -alias tomcat -file tomcat.cer -storepass <password> -keypass <password> -keystore .keystore
Certificate stored in file <tomcat.cer>
$ cd $PENTAHO_JAVA_HOME/jre/lib/security/
$ keytool -import -alias tomcat -file ~/tomcat.cer -keystore cacerts -storepass changeit

Configure Tomcat for HTTPS

Now we need to tell Pentaho 8 we are ready to use HTTPS using our key. To do so we need to change our server.xml file in the tomcat folder: tomcat/conf/server.xml.

There we need to uncomment the connector for HTTPS SSL protocol in port 8443 and add the credentials  for the keystore location and password:

<Connector URIEncoding="UTF-8" port="8443" protocol="org.apache.coyote.http11.Http11NioProtocol"
maxThreads="150" SSLEnabled="true" scheme="https" secure="true"
keystoreFile="<pentaho_user_home>/.keystore" keystorePass=<password>
clientAuth="false" sslProtocol="TLS" />

This configures a new connector at port 8443 using https, however we do not close the http connector at port 8080 yet. This allows both to be used, at the same time (we may close it later if we want to prevent HTTP connections). Do be careful as if you try to login on both and your browser executes cached code it can create a login error. Using a private browsing window helps avoid this issue.

Also we need to tell Pentaho 8 we now have a new port in the server.properties file: pentaho-solutions/system/server.properties

fully-qualified-server-url=http://localhost:8443/pentaho/

And this should be it. We should have gotten our server to respond in both port 8080 using HTTP and 8443 using HTTPS. Keep in mind what I mentioned regarding the browser cache, so if we simply use a private browsing window all should be ok.

Securing a Pentaho Server

How to customise security and access

This post will show you how you can restrict the access to your Pentaho BA Server, essentially creating a “guest” user role, and cherry picking what that user has access to.

Spring Security – Becoming very selective

We want to restrict the access of visitors to only specific demos, rather than the default permissions granted to the Authenticated users.

In order to do this we first need to adjust our BA Server’s Administration->User/Roles settings. What we want to achieve is a specific role for all company users, and a set of separate roles for visitors.

First we create a role for company users with almost all privileges, and remove all other user roles. After this is done we will need to restrict the privileges of Authenticated users. We restrict their access to a minimum so we only allow them to read and execute content.

Now we add a guest user, without a role associated, which means it will behave as an Authenticated user. Once this is done we need to hard code how access to web services are given to internal roles. Our specific goal in this post will be to allow a guest user access only to a single demo dashboard.

Opening Spring Framework’s “pentaho-solutions/system/applicationContext-spring-security.xml”, you will see that this file contains the configuration of the security filters for various services. Specifically we will be working on the “filterInvocationInterceptorForWS” and “filterInvocationInterceptor” beans, which grant access to specific user roles to webservices which are accessed through URI’s. Each one of these beans lists a set of patterns and the roles that have access to them. The first step is to restrict all access only for company users. Then add patterns that will be regex-matched to cherry picked dashboards and allow any Authenticated user access. 

By default this is what filterInvocationInterceptor looks like:

<bean id="filterInvocationInterceptor" class="org.springframework.security.web.access.intercept.FilterSecurityInterceptor">
<sec:filter-security-metadata-source request-matcher="ciRegex" use-expressions="false">
<sec:intercept-url pattern="\A/content/common-ui/resources/web/(.+/)*.+\.js.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/.*require-cfg.js.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/.*require-js-cfg.js.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/content/common-ui/resources/web/require.js.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/content/common-ui/resources/web/require-cfg.js.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/content/data-access/resources/gwt/.*css\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/webcontext.js.*\Z" access="Anonymous,Authenticated" />
(...)

A service granted to both Authenticated and Anonymous will refer to a service required for any access (including prior to logging in), we must allow those to remain open as they are essential. We therefore focus on patterns that are granted only to Authenticated users and edit the “access” to list only our role “User” and “Admin”.

<sec:intercept-url pattern="\A/mantle/.*\Z" access="User,Admin" />
<sec:intercept-url pattern="\A/.*\Z" access="User,Admin" />

This security filter manages only basic BA Server components, like for instance the login services. However does not filter access to the home screen. Which means a user that is Authenticated has no access to mantle, however will be shown an empty homepage. In order to avoid this we add a pattern for /home, also restricted to our role “User” and “Admin”. It is important to note that these security beans are applied from top to bottom, which means order matters very much. We add our new pattern immediately after the mantle one. A snippet should look like the one below:

<sec:intercept-url pattern="\A/mantlelogin/.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/mantle/mantleloginservice/*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/mantle/.*\Z" access="User,Admin" />
<sec:intercept-url pattern="\A/home.*\Z" access="User,Admin"/>

<sec:intercept-url pattern="\A/welcome/.*\Z" access="Anonymous, Authenticated" />
<sec:intercept-url pattern="\A/public/.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/login.*\Z" access="Anonymous,Authenticated" />

Now we turn to the filterInvocationInterceptorForWS and follow the same first step, restrict the access to all patterns granted to Authenticated changing them to only to “User, Admin”.

<sec:filter-security-metadata-source request-matcher="ciRegex" use-expressions="false">
<sec:intercept-url pattern="\A/webservices/unifiedrepository\?wsdl.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/webservices/userrolelistservice\?wsdl.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/webservices/userroleservice\?wsdl.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/webservices/authorizationpolicy\?wsdl.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/webservices/rolebindingdao\?wsdl.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/webservices/scheduler\?wsdl.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/webservices/repositorysync\?wsdl.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/webservices/datasourcemgmtservice\?wsdl.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/webservices/.*\Z" access="User,Admin" />
<sec:intercept-url pattern="\A/plugin/reporting/api/jobs/.*\Z" access="Anonymous,Authenticated" method="OPTIONS" />
<sec:intercept-url pattern="\A/plugin/reporting/api/jobs/.*\Z" access="Anonymous,Authenticated" method="HEAD" />
<sec:intercept-url pattern="\A/api/repos/.*\Z" access="Anonymous,Authenticated" method="OPTIONS" />
<sec:intercept-url pattern="\A/api/repos/.*\Z" access="Anonymous,Authenticated" method="HEAD" />
<sec:intercept-url pattern="\A/api/.*require-cfg.js.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/api/.*require-js-cfg.js.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/api/.*\Z" access="User,Admin" />
<sec:intercept-url pattern="\A/plugin/.*\Z" access="User,Admin" />

So at this stage we have restricted all access to almost all components and services to our company users using the role “User”. And we are now able to cherry pick the exact services we want to open. In order to allow access by visitors to our dashboard and nothing else, we add a URI pattern and allow access to “Authenticated”. We then add it on the TOP, which means it is the first applied filter and therefore the most constricting.

<sec:intercept-url pattern="\A/api/repos/:public:ubiquis:world_population:world_population.wcdf/generatedContent.*\Z" access="Authenticated"/>

At this stage if you try to open the URI the dashboard will fail to render. This happens because the visitor is trying to render the dashboard without access to any other services, so through trial and error use the browser console and check which other services are required to render that particular dashboard. Once you identify which ones are required you open those services too.

<sec:intercept-url pattern="\A/api/repos/:public:ubiquis:world_population:world_population.wcdf/generatedContent.*\Z" access="Authenticated"/>
<sec:intercept-url pattern="\A/api/repos/:public:ubiquis:world_population:.*\.png*\Z" access="Authenticated"/>
<sec:intercept-url pattern="\A/plugin/cda/api/doQuery\?.*\Z" access="Authenticated"/>
<sec:intercept-url pattern="\A/api/repos/pentaho-cdf/.*\Z" access="Authenticated"/>
<sec:intercept-url pattern="\A/api/repos/pentaho-cdf-dd/.*\Z" access="Authenticated"/>
<sec:intercept-url pattern="\A/plugin/pentaho-cdf-dd/api/resources/public/ubiquis/.*\Z" access="Authenticated"/>
<sec:intercept-url pattern="\A/webservices/unifiedrepository\?wsdl.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/webservices/userrolelistservice\?wsdl.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/webservices/userroleservice\?wsdl.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/webservices/authorizationpolicy\?wsdl.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/webservices/rolebindingdao\?wsdl.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/webservices/scheduler\?wsdl.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/webservices/repositorysync\?wsdl.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/webservices/datasourcemgmtservice\?wsdl.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/webservices/.*\Z" access="User,Admin" />
<sec:intercept-url pattern="\A/plugin/reporting/api/jobs/.*\Z" access="Anonymous,Authenticated" method="OPTIONS" />
<sec:intercept-url pattern="\A/plugin/reporting/api/jobs/.*\Z" access="Anonymous,Authenticated" method="HEAD" />
<sec:intercept-url pattern="\A/api/repos/.*\Z" access="Anonymous,Authenticated" method="OPTIONS" />
<sec:intercept-url pattern="\A/api/repos/.*\Z" access="Anonymous,Authenticated" method="HEAD" />
<sec:intercept-url pattern="\A/api/.*require-cfg.js.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/api/.*require-js-cfg.js.*\Z" access="Anonymous,Authenticated" />
<sec:intercept-url pattern="\A/api/.*\Z" access="User,Admin" />
<sec:intercept-url pattern="\A/plugin/.*\Z" access="User,Admin" />

As you can see there were images that needed to be accessed in our repository, and we also needed to allow the user to run CDA queries. There are a number of javascript and css files also required for CDF to render our dashboard correctly. Notice that although we allowed access to CDA, we did not grant access to the CDA previewer or editor.

The resulting dashboard can be found clicking this link (you can see that username and password are being passed in the URL).

This principle can be applied to different guest user roles, where a subset of visitors will have access to one part of the server, and others another, allowing for a much more controlled approach to how to grant access to your server.

 

 

Reading/Writing files from a Kettle CDA datasource in Pentaho 5.x

Kettle datasources in CDA are the most versatile way of getting data from disparate systems into a dashboard or report. Both C-tools and PRD expose Kettle as a datasource, so we can call a transformation that queries several different systems (databases, web services, etc.), do some Kettle magic on the results and then ship the final resultset back to the server.

However… there’s no way out of the box to reference sub-transformations in Pentaho 5.x. Solution files are now hosted on Jackrabbit and not on the file system and ${Internal.Transformation.Filename.Directory} just doesn’t work. If you have a transformation calling a sub-transformation and upload both to the repository, when you run it you get an error message saying that it cannot find the file.

Although this usually isn’t a problem, as most Kettle transformations used by dashboards are relatively simple, for more complex tasks you may want to use mappings (sub-transformations), call a job or transformation executor or, and perhaps the best use case, use metadata injection. In these scenarios you _really_ need to be able to call ktr files from the BA server context, and as it happens you can’t.

Well, sort of. As it turns out, you can.

The problem: lets say you have your server installed in /opt/pentaho/biserver-ce (or biserver-ee, we at Ubiquis don’t discriminate), and your kettle file is in your repository in /public/myDashboard/files/kettle/myTransformation.ktr. You want to use Metadata injection on a transformation called myTemplate.ktr and so you reference it by ${Internal.Transformation.Filename.Directory}/myTemplate.ktr in your metadata injection step. When you run your datasource, your error message will say something like

Cannot read from file /opt/pentaho/biserver-ce/pentaho-solutions/public/myDashboard/files/kettle/myTemplate.ktr because it’s not a file.

So what seems to be happening is that Kettle is using your pentaho-solutions folder as the root folder and it just appends the JCR path to it. Which obviously fails, as there’s no physical counterpart in your filesystem for the JCR files.

But, if you use the Pentaho Repository Synchronizer plugin to copy the contents of JCR to the filesystem and vice-versa (which is perhaps the simplest way to have your solution files under GIT or SVN, at least until the Ivy GS plugin becomes fully functional), then the synchronisation process will create a folder repositorySynchronizer inside your pentaho-solutions folder and use it to create the copies of every JCR file.

So, after synchronising JCR with the file system, your template transformation can be found in /opt/pentaho/biserver-ce/pentaho-solutions/repositorySynchronizer/public/myDashboard/files/kettle.

And now, the funny bit: to get it working, just create a symbolic link to remove the extra repositorySynchronizer element from your path: go to your pentaho-solutions folder and just type ln -s repositorySynchronizer/public public.

Now the template transformation is found and you can reference it from a transformation called by a dashboard.

Credit for the discovery goes to Miguel Cunhal, our junior consultant, that had the tough task of getting our metadata injection transformation running inside a demo dashboard. But more on that in another post.