Database-BI
view release on metacpan or search on metacpan
lib/Database/BI.pm view on Meta::CPAN
# _time_columns, Calc::date_normalise and the browser's biDetectColType
# each recognise dates differently (the browser takes DD-Mon-YYYY, the
# server's filter does not). Share one definition.
# TODO: Move dashboard.html.tt's inline script into static files (allowed by
# script-src 'self') so it can be unit-tested, not only run in Playwright.
# TODO: Loading Dashboard.pm during the compile phase runs Sub::Protected's
# CHECK, which alone turned the SQL path's sums into JSON strings
# (t/time-bucket.t). The server never loads it that way; find which
# protected sub is responsible.
# TODO: Async work: long joins and geocoding block a worker. Use
# Mojo::IOLoop->subprocess, or the server-sent-events streaming, for
# progress and free workers.
# TODO: Generated CI: the Windows cpanm retry, and the cache that never fills
# on Windows (installs go into Perl's own site folder), belong in App::GHGen's
# template, not in perl-ci.yml, which it overwrites.
# Default config values used by the Config plugin and referenced explicitly
# in startup() so callers always get a resolved value.
Readonly my $DEFAULT_DATA_DIR => 'data';
Readonly my $DEFAULT_PLATFORM => 'web';
Readonly my $DEFAULT_LANGUAGE => 'en';
Readonly my $DEFAULT_CACHE_TTL_URL => '15 min';
# The browser-side chart library is fetched from a CDN unless the deployment
# self-hosts it. This is the pinned version the pages were tested with; the
# URL is a config seam (d3_url), and the Leaflet assets have their own keys
# (leaflet_*_url), so an offline installation can point them at its own copies.
Readonly my $DEFAULT_D3_URL => 'https://cdnjs.cloudflare.com/ajax/libs/d3/7.9.0/d3.min.js';
# Where HTML::OSM fetches Leaflet and its tiles when the config names no
# other. Only their origins matter: they go into the Content-Security-Policy.
Readonly my $DEFAULT_LEAFLET_URL => 'https://unpkg.com/';
Readonly my $DEFAULT_TILE_URL => 'https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png';
# How long a login lasts after the last request: a working day.
Readonly my $DEFAULT_LOGIN_EXPIRATION => 8 * 60 * 60;
# Where a server started without -l or MOJO_LISTEN listens: this machine only.
# Config listen (morbo, daemon, prefork) and hypnotoad => { listen } (hypnotoad)
# change the port, or open it to other machines with 'http://*:3000'.
Readonly my $DEFAULT_LISTEN => 'http://127.0.0.1:3000';
Readonly my $DEFAULT_HYPNOTOAD_LISTEN => 'http://127.0.0.1:8080';
# What a Content-Security-Policy nonce may be made of (base64 or base64url);
# anything else reaching the policy is dropped.
Readonly my $NONCE_RE => qr{\A[A-Za-z0-9+/_=-]+\z};
# The Referer a page sends: the whole URL to this site, only the origin to
# another, and nothing from https to http. Specs, filter values and share
# tokens travel in the query string, and an older browser's default sends all
# of it to the CDNs, the tile servers and any site a reader follows a link to.
# OpenStreetMap's tile policy asks pages for a Referer, so it is not none.
Readonly my $REFERRER_POLICY => 'strict-origin-when-cross-origin';
# Permissions-Policy: every powerful browser feature off, for this site and
# any frame it holds, but writing to the clipboard, which the Copy link
# buttons do. Only names Chromium knows: it logs an error for any other.
Readonly my @PERMISSIONS_DENIED => qw(
accelerometer autoplay browsing-topics camera clipboard-read display-capture
encrypted-media fullscreen gamepad geolocation gyroscope hid
identity-credentials-get idle-detection local-fonts magnetometer microphone
midi otp-credentials payment picture-in-picture publickey-credentials-create
publickey-credentials-get screen-wake-lock serial storage-access usb
window-management xr-spatial-tracking
);
Readonly my $DEFAULT_PERMISSIONS_POLICY =>
join(', ', (map { "$_=()" } @PERMISSIONS_DENIED), 'clipboard-write=(self)');
# Cross-Origin-Opener-Policy for a page: a window another site opened, or a
# window a page opens on another site, gets no handle on the page. No page
# opens a window or reads window.opener; links out are rel="noopener" anyway.
Readonly my $DEFAULT_COOP => 'same-origin';
Readonly my %COOP_VALUES => map { $_ => 1 } qw(same-origin same-origin-allow-popups noopener-allow-popups unsafe-none);
# Strict-Transport-Security, sent on replies to https requests: a year, and
# this host only. includeSubDomains and preload reach other sites of the same
# domain, and are hard to take back, so they are for the configuration to add.
Readonly my $DEFAULT_HSTS => 'max-age=31536000';
Readonly my $HSTS_RE => qr{\Amax-age=\d{1,10}(?:\s*;\s*includeSubDomains)?(?:\s*;\s*preload)?\z}i;
# Transport-layer upload size cap (bytes). Mojolicious enforces this before
# the request body is read into memory, so an oversized upload never reaches
# the controller. Must match $MAX_UPLOAD_BYTES in Dashboard.pm.
Readonly my $MAX_REQUEST_SIZE => 50 * 1_048_576; # 50 MiB
Readonly my $UPLOAD_MAX_AGE_S => 24 * 3600; # evict uploads older than 24 h
=head1 NAME
Database::BI - Web-based Business Intelligence viewer for data files
=head1 VERSION
0.021.0
=head1 DESCRIPTION
C<Database::BI> is a self-contained L<Mojolicious> web application that reads
and views arbitrary data (CSV, PSV, TSV, SQLite, JSON, XML, XLSX, etc.)
and presents them as styled, sortable, reorderable
HTML tables.
It has no persistent database of its own,
it reads your files on every request.
Users navigate to pick a file, open the filesystem browser to navigate anywhere
on disc, drag-and-drop files to upload, join multiple tables in memory, apply
server-side filters, and export results as CSV, SQLite, JSON, or XLSX. Nine
D3.js v7 chart types, a pivot/crosstab table, per-column statistics, and
multi-visual saved canvases are available from the toolbar.
A tutorial in plain English is built into the application at C</tutorial>.
It is written for people who do not know Perl, and it is the place to start.
Key features:
=over 4
=item *
B<File picker> -- the home page scans C<data_dir> and shows a card for
every supported file. Recently opened filesystem files appear in a
"Recently opened" section powered by C<localStorage>.
=item *
lib/Database/BI.pm view on Meta::CPAN
the start. Remote files (C</../host/...>), Redis and MariaDB are not files on
this machine, and are not affected; neither are the command-line tools.
Saving is the riskier half: an export replaces a C<.csv>, C<.json> or C<.sql>
file of the same name, and some such files are settings another program obeys
(a project's F<package.json> names commands that C<npm> runs). A server other
people use keeps exports to folders of their own, so with C<password_hash> set,
C<export_dirs> is required, and the start stops without it:
{ export_dirs => ['/srv/reports/exports'] } # a relative one is under the application home
C<POST /export> then saves only in them (or below them), and the Export panel
opens on the first and lists no other. Reading is unaffected: that is
C<file_roots>. An export folder that does not exist, is outside C<file_roots>
or is a dot-folder stops the start. Whatever is set, an export never replaces
a link, a folder or anything else that is not a plain file, so a link inside
the allowed folders cannot carry it outside them; it is written beside its name
and renamed into place, so nobody reads half a file, and a file it replaces
keeps its permissions.
=head2 Requests from other sites
Unless a password is set (see L</Logging in>), there is no login: whatever can
reach the server can read any file it can (C</open>) and write exports into
any folder it can (C<POST /export>). Even with one, a logged-in reader's
browser carries the login to every request it sends here. Listening
on this machine only keeps other machines out, but not the pages of other
sites open in the reader's browser. So every request is checked first, and
refused with C<403> when another site's page made it:
=over 4
=item * A write (any method but C<GET>, C<HEAD> and C<OPTIONS>) is refused when
the browser's C<Sec-Fetch-Site> says it came from another site, or, without
that header, when its C<Origin> is not this server. A form posted from
another site needs no permission from this one, so this is what stops it. A
script such as C<curl> sends neither header and is let through.
=item * Any request is refused when its C<Host> is a name other than
C<localhost> or C<*.localhost>, and is not an IP address. A page whose own
name has been made to resolve to C<127.0.0.1> (DNS rebinding) counts as this
server to the browser, but it still sends its own name. To reach the server
by a name, list it:
{ allowed_hosts => ['bi.example.com', '*.corp.example'] } # '*' for any name
Behind a trusted reverse proxy, the C<Host> is whatever the proxy sends, so it
is checked only when C<allowed_hosts> is set; set it there too.
=back
C<POST /export> also refuses a folder or file whose name starts with a dot
(F<~/.config>, F<.vscode/settings.json>), since those hold other programs'
settings. The folder list in the Export panel leaves them out already.
=head2 Which addresses it may fetch
C</import> and a C<url:> data source make the server fetch a URL, so a page
could point it at what only the server can reach: a service on this machine, a
database on the office network, or a cloud's metadata service on
C<169.254.169.254>, which hands out its credentials. So the server refuses a
URL that leads to this machine, a private or shared network
(C<10.0.0.0/8>, C<172.16.0.0/12>, C<192.168.0.0/16>, C<100.64.0.0/10>,
C<fc00::/7>), link-local addresses, multicast or a reserved range.
It is what the name I<resolves to> that counts, not how the URL reads:
C<http://localtest.me/> and C<http://127.0.0.1.nip.io/> are names whose DNS
answers C<127.0.0.1>, and C<http://2130706433/> or C<http://0x7f000001/> are
C<127.0.0.1> written another way; all are refused. An address is looked up
once, checked, and that address is the one connected to, so a name that answers
differently the second time (DNS rebinding) gains nothing. Each redirect is
checked the same way, and at most 5 are followed. Over C<https> the
certificate must still be the site's. The refusal says why, for example
C<localtest.me is 127.0.0.1, which is this machine>.
To read from an intranet site, name it:
{ url_private_hosts => ['reports.corp.example', '*.corp.example'] }
Those names may resolve to a private address; every other name is still
checked, and an address written as such (C<http://10.1.2.3/>) is never let
through. A name that is not a host name, or C<*> alone, stops the start.
This check is not a firewall: where the server's machine can reach something it
should not, rules on outgoing connections are the stronger defence.
=head2 Logging in
With C<password_hash> set, every page asks for a password first. A server
other machines can reach must have one (see L</Who can reach the server>); on
this machine alone it is optional. Make the line with
script/database-bi password
which asks for the password twice (without showing it) and prints
password_hash => 'pbkdf2-sha256$200000$...',
to put in C<database_bi.conf>. Only the hash is kept: PBKDF2-HMAC-SHA256 with
a random salt (L<Database::BI::Password>). A plain password there stops the
start, so it is never left in the file by mistake. To change the password,
make a new line; every session is then logged out.
There is one password, not one per person. A browser asking for a page is
sent to C</login> and, once the password is right, back to that page; any other
request (a script, a panel's request, a download) gets C<401> until it carries a
logged-in session. C<POST /logout>, the Log out button at the top of each page,
ends it. C</share/:id> and C</embed/:id> need no login: their signed, expiring
token is their permission, so anyone holding the link sees that canvas, and
only that one, for 30 days. The canvas itself (C</a/:id>) and the rest still
ask for the password.
The token is signed with C<share_secret> when it is set (at least 16
characters), else with a key derived from C<secrets>. Without either, the key
is new at each start, so a restart ends every share link, and the Share panel
says so; set C<secrets> (which keeps logins too) or C<share_secret> to keep
them. Changing either ends the links made with the old one. Before 0.021.0 a
missing C<share_secret> meant a fixed key, the same on every install, with which
anyone could sign a link to any canvas; that key in C<share_secret> stops the
start.
{
lib/Database/BI.pm view on Meta::CPAN
if (@{$framers}) {
my $enforced = $report_only ? undef : $policy;
$headers->add('Content-Security-Policy' => _frame_ancestors_directive($framers))
unless defined $enforced && $enforced =~ /(?:\A|;)\s*frame-ancestors\b/i;
}
});
# A relative time range named in the URL (?range=last_30_days&rcol=when)
# becomes the f= filters every data route already applies, so the table
# view, every chart, the pivot, stats, insights, the exports and a saved
# canvas all narrow to the same rows without any per-route code. It runs
# before dispatch because the range has to be part of the request's filter
# list by the time a route reads it, and a range that cannot be honoured
# answers 400 here rather than filtering the wrong rows.
$self->hook(before_dispatch => sub {
my $c = shift;
require Database::BI::Controller::Dashboard;
return Database::BI::Controller::Dashboard::_time_range_request($c);
});
# A filter whose operator is not one the pipeline knows is refused here,
# for every route, rather than keeping every row and saying nothing.
$self->hook(before_dispatch => sub {
my $c = shift;
return if $c->res->code; # already answered (a range refused above)
require Database::BI::Controller::Dashboard;
return Database::BI::Controller::Dashboard::_filter_request($c);
});
# Cross-row columns (?cs=/by=/order=) are part of the view: the table page
# draws a chip for each one and its panel edits them, and every chart panel
# carries them to its route, which runs the same pass over the same rows.
# It is a hook rather than four stash lines in each of the seven actions that
# render the dashboard, so a new action cannot silently lose them.
$self->hook(before_dispatch => sub {
my $c = shift;
require Database::BI::Controller::Dashboard;
$c->stash(Database::BI::Controller::Dashboard::_cross_row_ui_state($c));
});
# The rest of the view a request carries -- the join type, the reader's
# filters, the files stacked in with c= and de-duplication -- for the same
# reason: the dashboard's panels pass it on through one macro, and every
# chart page names it under its breadcrumb, so a chart never looks like
# the whole file when it is drawn from part of it.
$self->hook(before_dispatch => sub {
my $c = shift;
my $jtype = $c->param('jtype') // '';
$c->stash(
view_join_type => ($jtype =~ /\A(?:left|inner|outer)\z/ ? $jtype : ''),
view_filters => [ grep { length } @{ $c->every_param('f') } ],
view_combines => [ grep { /\A(?:path|table|url|redis|mariadb):/i } @{ $c->every_param('c') } ],
view_dedup => ($c->param('d') ? 1 : 0),
# lk=/nolk= turn a stored lookup on or off for this view, as name=value
# pairs so the macro can write them back in the order they came.
view_lookups => [ map { my $k = $_; map { { name => $k, value => $_ } } grep { length } @{ $c->every_param($k) } } qw(lk nolk) ],
# Names only: the rest of an entry is credentials.
mariadb_server_names => [ sort keys %{ $c->app->config('mariadb_servers') // {} } ],
);
});
# Every page names the tutorial chapter that helps with it. The layout
# turns this into a "Help: ..." link beside the main Tutorial link, so a
# reader on the pie chart screen lands on the pie chart chapter. It lives
# here rather than in every action: a new action is one entry in the action
# map in Database::BI::Tutorial, and t/tutorial.t checks it is there.
$self->hook(before_render => sub {
my ($c, $args) = @_;
# The language is the one the action already chose, if it got that far
# (_resolve_template is protected, so it cannot be asked from here).
my $page_template = sub {
my ($name) = @_;
my $conf = $c->app->config;
my $platform = $conf->{platform} // 'web';
my $language = $c->stash('language') // '';
$language = $conf->{language} // 'en'
unless $language =~ /\A[a-z]{2}(?:[-_][A-Za-z]{2})?\z/
&& -d $c->app->home->child("templates/$platform/$language");
$c->stash(language => $language);
return "$platform/$language/$name";
};
# A request refused in plain text is a dead end in a browser: no
# navigation, and no way back but the Back button. A page the browser
# navigated to -- a GET whose Accept names text/html, which fetch() and
# scripts do not send -- gets the error page instead: the trail back to
# the table, and the reason, escaped by the template. Everything else
# keeps the plain text below.
my $status = $args->{status} // 0;
if (defined $args->{text} && ($status == 400 || $status == 403)
&& $c->req->method eq 'GET'
&& ($c->req->headers->accept // '') =~ m{\btext/html\b}
&& $c->req->url->path->to_string !~ m{\A/api/}) {
$c->stash(
title => $status == 403 ? 'This is not available here' : 'This page cannot be drawn',
error_msg => delete $args->{text},
error_400 => 1,
);
$args->{template} = $page_template->('not_found');
$args->{handler} = 'tt';
$args->{format} = 'html';
}
# Mojolicious sends render(text => ...) as text/html. The error replies
# that use it repeat what was asked for (a column, a range), so as HTML a
# crafted link would run script on this site. A template or JSON escapes
# for itself; plain text only has to say that it is plain text.
$args->{format} //= 'txt'
if defined $args->{text} && !defined $c->stash('format');
# AI availability flag used by dashboard and insights templates.
$c->stash(ai_available => (length($c->app->config('ai_api_key') // '') ? 1 : 0))
unless defined $c->stash('ai_available');
# The chart pages and canvases read this from the stash. Resolving it
# per request rather than once at startup means a deployment (and a
( run in 1.973 second using v1.01-cache-2.11-cpan-3f962061493 )