Install and configure

Requirements

Some modules are recommended rather than required, so the application installs, starts and tests without them. HTML::OSM (0.14 or later) draws maps, with a scale bar, a key on the map that follows the view, and a first view that fits every marker. A place column is turned into coordinates by Geo::Coder::List (0.39 or later), which asks Geo::Coder::Free (0.48 or later) and its local database first, then OpenStreetMap's Nominatim service through Geo::Coder::OSM (0.03 or later) for the places the local data does not know. LWP::UserAgent::Throttled (0.14 or later) keeps those requests to Nominatim's limit of one a second. Nominatim is asked over https, and what it finds is kept for a month in <model_dir>/geocode-cache/. Without Geo::Coder::OSM, or with map_geocode_online => 0, every lookup stays on this machine. Without HTML::OSM the map pages are hidden, and the rest of the application is unaffected. Redis (or Redis::Fast) is optional too, and switches on the redis: data source when installed.

DBD::MariaDB (or DBD::mysql) is optional too, and is what a mariadb: source connects with. t/mariadb.t runs without a server; t/mariadb_live.t runs when MARIADB_SERVER is set, and creates its own tables and drops them.

MARIADB_SERVER=mariadb://user:pass@host/test prove -lv t/mariadb_live.t

Install

# from CPAN
cpanm Database::BI

# or from a checkout
cpanm --installdeps .
perl Makefile.PL
make
make test

Run it

# development: restarts when a source file is edited
script/database-bi

# the same thing, spelled out
morbo script/database-bi

# the same, showing one database at the home address
script/database-bi data/store.db

# production: a preforking server; start, or restart with no downtime
hypnotoad script/database-bi

# stop it
hypnotoad -s script/database-bi

With no arguments the launcher starts the development server itself; any other Mojolicious command word (routes, get, …) is passed through to the application. The preforking server is the one to put behind a reverse proxy for long-running use; give it proxy => 1 in its hypnotoad settings so it knows which requests came over https. It listens on port 8080 of this machine only and ignores MOJO_LISTEN; set the port in database_bi.conf, as hypnotoad => { listen => ['http://*:8080'] }, where * opens it to every machine. It keeps its process id in script/hypnotoad.pid unless pid_file says otherwise.

Given the name of a database, the launcher starts the same development server, and the home address shows that database. The Home link in its breadcrumb leads to the usual list. It takes a data file, a remote path, a Redis spec or a MariaDB spec, as /open does.

Only one can be named, because a join cannot be given on the command line: open one and use Merge or Combine in the browser. Naming more than one, or a file that is missing, cannot be read or is of an unsupported type, prints the reason and exits with status 2 before any server starts.

The development server and script/database-bi daemon listen on http://127.0.0.1:3000, so only this machine can reach them: unless you set a password, there is no login. To change the port, or let other machines in, set listen in database_bi.conf:

{ listen => 'http://127.0.0.1:8000' }   # another port, this machine only
{ listen => 'http://*:3000' }           # every machine that can reach this one
{ listen => ['http://127.0.0.1:3000', 'http://[::1]:3000'] }   # IPv6 loopback too

MOJO_LISTEN and daemon -l still win over it. morbo script/database-bi opens its port before it reads the configuration, so give it -l, or run the launcher instead. A listen that is not an http:// or https:// location stops the start.

Anywhere but this machine needs a password. A listen (or MOJO_LISTEN, -l, or hypnotoad's own) that is not 127.0.0.1, [::1] or localhost stops the start unless password_hash is set, and says what to change. So does a bare morbo script/database-bi, which listens everywhere. Behind a reverse proxy, without a password, a request the proxy brings from another machine is refused (403).

Logging in

With password_hash set, every page asks for a password first. Make the line with

script/database-bi password

It asks for the password twice, without showing it, and prints a line such as password_hash => 'pbkdf2-sha256$200000$...', to put in database_bi.conf. Only the hash is kept (PBKDF2-HMAC-SHA256, with a random salt); a plain password there stops the start. A new line logs every session out.

{
    password_hash    => 'pbkdf2-sha256$200000$...',
    export_dirs      => ['/srv/reports/exports'],  # required with a password
    login_expiration => 8 * 60 * 60,            # seconds idle before logging out (the default)
    secrets          => ['a long random string'], # or each restart logs everyone out
}

There is one password, not one per person. A browser is sent to the Log in page and then back to the page it asked for; a script or download gets 401. Log out is at the top of every page. Share and embed links still work without it, since their signed, expiring token is their permission. They are signed with share_secret if set (16 characters or more), else with a key made from secrets; with neither, a restart ends every link. The session cookie is HttpOnly, SameSite=Lax, and Secure over https. Over plain http the password crosses the network as it is typed, so put a server that other machines reach behind https. Ten wrong passwords from one address in 15 minutes lock it out for the rest of the 15 minutes.

Without a password, whatever reaches the server can read files and save exports wherever it can; with one, a logged-in browser carries the login to every request it sends. Listening on 127.0.0.1 keeps other machines out, but not other sites' pages open in your browser, so each request is checked first and refused (403) when another site made it:

• A save or other change is refused when the browser says another site sent it (Sec-Fetch-Site, or else Origin). Scripts such as curl send neither, and still work.
• Any request is refused when it names the server by a host name other than localhost; an IP address is fine. This stops a site whose name has been pointed at 127.0.0.1 (DNS rebinding). To use a name, list it: { allowed_hosts => ['bi.example.com'] }, or ['*'] for any. Behind a trusted reverse proxy only that list is checked, so set it there too.

Listening on 127.0.0.1 keeps other machines out, not other people on this one. Any account on the same machine, or a container sharing its network, can reach the server and read, list and save files as you. On a machine with other users, set a password even when it listens on this machine only.

Which folders it may use

Browsing, opening files, the Export panel's folder list, a path: source in a table, chart, merge or canvas, and saving an export reach the whole filesystem, as far as the account running the server can. To fence them in:

{ file_roots => ['/srv/reports', 'shared'] }   # 'shared' is under the application home

data_dir and uploads_dir are always included. Browsing starts at your home folder if it is inside, else at the first root, and goes no higher. Anything outside is refused (403), whether it is there or not, so nothing is learnt about it; a link inside that points outside counts as outside. A root that is not an existing folder stops the start. Remote files, Redis and MariaDB are not affected.

Saving replaces a file of the same name, and some .json files are settings other programs obey, such as a project's package.json. A server other people use keeps exports to folders of their own, so with a password set this is required, and the server does not start without it:

{ export_dirs => ['/srv/reports/exports'] }

Exports are then saved only there, and the Export panel opens there. An export never replaces a link or a folder, so a link cannot carry it elsewhere.

Export also refuses a folder or file whose name starts with a dot, such as ~/.config: those hold other programs' settings.

Which addresses it may fetch

Import from a web page and a url: source make the server fetch a URL, so it refuses one that leads to this machine, a private network, or a cloud's metadata service on 169.254.169.254. What counts is the address the name resolves to: localtest.me, whose DNS answers 127.0.0.1, is refused, and so is http://2130706433/, which is 127.0.0.1 written another way. The server connects to the address it checked, and checks every redirect too. To read from an intranet site, name it:

{ url_private_hosts => ['reports.corp.example', '*.corp.example'] }

This is not a firewall: if the server's machine can reach something it should not, block that with rules on outgoing connections.

script/database-bi daemon ignores a file named after it, and so does hypnotoad; to show a database from either, set BI_OPEN:

BI_OPEN=/full/path/store.db script/database-bi daemon
BI_OPEN=/full/path/store.db hypnotoad script/database-bi

Configuration

This is for whoever runs the application. A reader's own choices, such as light or dark, are not set here: see Preferences.

Create database_bi.conf in the application root next to script/. It is optional, but if it exists it must be valid Perl and must return a hashref:

{
    data_dir  => 'data',          # scanned by the home page
    model_dir => '.bi',           # analyses.json, models.json
    language  => 'en',            # template directory under templates/web/
    platform  => 'web',
}

Serving your own D3 and Leaflet

Chart pages load D3 from a CDN and map pages load Leaflet from unpkg by default. A deployment with no outbound network access, or one that prefers its own assets, points the keys at local copies under public/:

{
    d3_url                 => '/static/d3/d3.min.js',
    leaflet_js_url         => '/static/leaflet/leaflet.js',
    leaflet_css_url        => '/static/leaflet/leaflet.css',
    leaflet_cluster_js_url => '/static/leaflet/leaflet.markercluster.js',
    tile_url               => 'https://tiles.internal/{z}/{x}/{y}.png',
}

That is public/static/d3/d3.min.js and so on. The versions to copy are the ones the defaults name: D3 7.9.0, Leaflet 1.9.4 and leaflet.markercluster 1.5.3. With tile_url set, every request needed to draw a map can stay on the local network.

Content-Security-Policy

Every HTML page is sent with a Content-Security-Policy header. The pages show cells from files this application did not write, so the policy backs up the escaping. Scripts, stylesheets and images may come only from this site and from the hosts named by d3_url, the leaflet_*_url keys, tile_url and tile_sources. With the defaults those are cdnjs.cloudflare.com, unpkg.com and OpenStreetMap's tile servers, so moving a library with the keys above moves the policy with it.

A page's scripts may talk only to this site (connect-src 'self'), and no other site may show a page in a frame (frame-ancestors 'self'). JSON replies and downloads carry no policy. Every page also carries X-Frame-Options: SAMEORIGIN, whatever the keys below say, so framing by another site is refused even with a report-only policy, a policy of your own, or none, and in browsers that read only that header.

Every reply also carries Referrer-Policy: strict-origin-when-cross-origin. Table specs, filter values and share tokens travel in the address, so another site, a CDN or the tile server is sent only this server's origin, never the whole address. It is not no-referrer, because OpenStreetMap asks pages that use its tiles to send one.

Every reply also carries X-Content-Type-Options: nosniff, so a browser takes it for the type it names and never guesses that a CSV export is a web page. Libraries you serve yourself from public/ keep working, because .js and .css files are sent as what they are. One served some other way must be sent as text/javascript or text/css, or the browser will not load it.

Every page carries Cross-Origin-Opener-Policy: same-origin, so a window another site opened, or one a page opened on another site, gets no handle on the page. cross_origin_opener_policy sets another value, such as same-origin-allow-popups, or turns it off with '' or 0. No Cross-Origin-Embedder-Policy is sent: it would refuse the libraries and map tiles that other servers send.

Every reply also carries a Permissions-Policy that turns off the browser features no page uses, such as the camera, the microphone, location, payment and reading the clipboard, so a script that got in cannot ask for them. Writing to the clipboard stays, for the Copy link buttons. permissions_policy replaces the value, as 'camera=(), geolocation=(self)', or turns it off with '' or 0.

A reply to an https request carries Strict-Transport-Security: max-age=31536000, so a browser that reached the server over https keeps to https for a year. It binds this host only. strict_transport_security sets another value, such as 'max-age=63072000; includeSubDomains', or turns it off with '' or 0; a value a browser would not read stops the application at startup. Nothing is sent over http, to localhost or to an IP address: the header binds a host on every port, so it would move every other server on the machine to https. Behind a proxy that ends TLS, start the server with MOJO_REVERSE_PROXY=1 (or hypnotoad => { proxy => 1 }) and have the proxy send X-Forwarded-Proto: https, or have the proxy send the header itself.

An inline script runs only if it carries the request's nonce, a random value made for each request and named in the policy. Every <script> the pages write carries it, and so do those HTML::D3 and HTML::OSM write. A script that came in with a cell's text cannot know it, so it does not run, and neither does an onclick or onerror attribute. Style attributes are still allowed. Two keys change the policy:

{
    content_security_policy_report_only => 1,   # report breaches, block nothing
    content_security_policy => "default-src 'self'; script-src 'self' 'nonce-{nonce}'",
}

content_security_policy is the whole policy, sent as written except that {nonce} becomes the request's nonce; without it no inline script runs. Set to '' or 0 it sends no header. A policy written out in full is not updated when the library keys change.

Embedding a canvas on another site

By default only pages of this site may show a canvas's /embed/ page in a frame. embed_frame_ancestors names other sites that may:

{ embed_frame_ancestors => [ 'https://intranet.example.com', 'https://*.corp.example' ] }

Each entry is an origin: http or https, a host (a * may stand for its first label only) and an optional port, with no path. Anything else stops the application at startup. The sites are allowed on /embed/ and no other page, and however the policy above is configured, an enforced frame-ancestors names them, so naming one site never lets every site in. A policy written out with its own frame-ancestors is left to say who may. Browsers that know only X-Frame-Options still refuse the frame. The canvas's Share panel names the sites its embed code works on.

Command-line tools

The scripts are installed with the distribution, each with a manual page (man bi-geocode, man bi-extract, man bi-alert, man database-bi). In a source folder they are under script/.

script/bi-geocode

Warms the shared geocode cache for one column, so later map requests make no lookups. Places the local data does not know are asked of OpenStreetMap's Nominatim, one a second and with no limit on how many: a map page asks it about at most map_geocode_online_max places, and this is how the rest are found. --col may be given twice for a place written across columns (--col Address --col Country), joined with a comma as loc= joins them on the map. --offline keeps every lookup local, and --online-max N sets a limit:

./script/bi-geocode --spec table:customers --col city \
                    [--data-dir DIR] [--model-dir DIR] \
                    [--openaddr PATH] [--region CC] \
                    [--offline] [--online-max N] [--dry-run] [--verbose]

It is idempotent: a second run against a warm cache resolves nothing and says so. Exit status is 0 on success, 1 when the column is missing or no rows load, and 2 on bad arguments or an unopenable source.

script/bi-extract

Accelerates a large file: builds a SQLite copy of it, an extract, which the pages read for as long as the file is unchanged. Once the file changes, the file itself is read and the table page says the extract is stale.

./script/bi-extract --spec table:sales [--spec ...]   # accelerate, and build
./script/bi-extract --all                             # rebuild every one
./script/bi-extract --remove --spec table:sales       # stop, and remove its extract
./script/bi-extract --list
                    [--data-dir DIR] [--model-dir DIR]

Extracts live in <model_dir>/extracts/, never under data_dir, and deleting that directory loses nothing. Exit status is 0 on success, 1 when an extract could not be built, and 2 on bad arguments. Run --all from cron after the files are refreshed.

script/bi-alert

Reports saved analyses and turns a crossed threshold into a non-zero exit status, so cron can act on it:

./script/bi-alert --analysis ID [--analysis ID ...] \
                  [--threshold MEASURE:OP:VALUE] \
                  [--suppress 1h] [--recovery] \
                  [--notify-cmd 'mail -s "$BI_ALERT_SUBJECT" oncall@example.com']

Exit status is 0 when nothing is announced, 1 when a threshold is crossed and announced, 2 on error, and 3 when a crossing that was announced has ended and --recovery asked for that to be announced. --suppress takes a duration and stops a condition that is still true from being reported as news again; the throttle is one small JSON file in the model directory. The digest goes to standard output and nothing leaves the machine unless --notify-cmd says where.

Staying up to date

The distribution ships a test suite that covers the routes, the pipeline, the renderer invariants and the templates; run it before upgrading:

prove -l t/

The release notes in Changes list every behaviour change, and the test dashboard is published at nigelhorne.github.io/Database-BI/coverage.