SQL Universal report

This report is a part of the reports module. Since 3.0.0 version.

Report name: sql-universal-report

This report will report an entire database table/view or data based on a custom SQL query.

Only a user with superadmin permission can execute this report

You have two options for running this report:

Table/view name - reports all data from a table/view as it is in the database
SQL query - reports data based on a custom SQL query

You have to use only one method. Table/view OR an SQL query

You need to configure an additional application.properties file. This report is using a separate datasource for safety reasons.

For this report, create a new user with only select permissions for the tables and views you need. Never use the same user you use for the standard IdM database!

For example read only user for whole IdM database:

Turn off IdM and then execute:
CREATE USER czechidm_report PASSWORD 'keepass';
GRANT CONNECT ON DATABASE czechidm TO czechidm_report;
\c czechidm;
REVOKE ALL ON SCHEMA public FROM public;
GRANT USAGE ON SCHEMA public TO public;
GRANT ALL on SCHEMA public to czechidm;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO czechidm_report;

The properties you need to configure are specified below.

# name of the database, update this
spring.reports-datasource.jdbcUrl=jdbc:postgresql://localhost:5432/hr
# name of the user, update this
spring.reports-datasource.username=czechidm_report
# password for the user, update this
spring.reports-datasource.password=keepass
spring.reports-datasource.driver-class-name=org.postgresql.Driver
spring.reports-datasource.testOnBorrow=true
spring.reports-datasource.validationQuery=SELECT 1
spring.reports-datasource.maximum-pool-size=5
spring.reports-datasource.read-only=true

SQL Universal report

Mandatory config