Docs · Connecting to data

The read-only login

The page to forward to whoever administers the database — what Mosaic will read, what it cannot do, and the grants to run.

This page is written to be sent on. If you are waiting on a database administrator, a DBA team or a security review before you can connect a system, forward this link rather than writing the request yourself.

#What is being asked for

Someone in your organisation wants to compare two systems that are supposed to agree — a POS against an ERP, a warehouse against a ledger — and find out where and why they do not. Mosaic does that by reading both and lining the rows up.

What it needs from you is one login that can read the tables a comparison covers, and nothing else. No schema changes, no writes, no agent installed on the database server, no inbound firewall rule.

#Where it connects from

Most retail estates run Mosaic on a machine inside the network, because the systems being compared are not reachable from the internet and should not be made so. In that arrangement Mosaic needs outbound access only, and the database never leaves the network it is on. See Run Mosaic.

#What it reads

A comparison names its tables. Mosaic reads those and nothing beside them.

• If whoever built the comparison wrote a query, that query is on screen and can be shown to you before anything is granted.
• If they only named a table, Mosaic generates and displays the statement it will run: SELECT * FROM SALES LIMIT 50000.
• Reads happen when somebody presses run, or on a schedule they set — typically once a night. Nothing polls continuously.

#What it cannot do

Writes are refused three times over, and the third layer is the one you control.

The full list of what is refused, per engine, is under Read-only access.

#The login to create

Replace the names, the database and the password with your own. Grant only the schemas or tables the comparison actually covers; a login that can read one table more than it needs is a login you will have to think about again later.

#PostgreSQL

CREATE ROLE mosaic_reader LOGIN PASSWORD 'choose-a-long-one';
GRANT CONNECT ON DATABASE sales TO mosaic_reader;
GRANT USAGE ON SCHEMA public TO mosaic_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mosaic_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mosaic_reader;
ALTER ROLE mosaic_reader SET default_transaction_read_only = on;

#SQL Server

CREATE LOGIN mosaic_reader WITH PASSWORD = 'choose-a-long-one';
USE Sales;
CREATE USER mosaic_reader FOR LOGIN mosaic_reader;
ALTER ROLE db_datareader ADD MEMBER mosaic_reader;
DENY EXECUTE TO mosaic_reader;

#MySQL and MariaDB

CREATE USER 'mosaic_reader'@'%' IDENTIFIED BY 'choose-a-long-one';
GRANT SELECT ON sales.* TO 'mosaic_reader'@'%';

Narrow the '%' to the address Mosaic runs on if you would rather.

#Oracle

CREATE USER mosaic_reader IDENTIFIED BY "choose-a-long-one";
GRANT CREATE SESSION TO mosaic_reader;
-- One line per table a comparison reads:
GRANT SELECT ON sales_owner.store_sales TO mosaic_reader;

#Snowflake

CREATE ROLE mosaic_reader;
GRANT USAGE ON WAREHOUSE reporting_wh TO ROLE mosaic_reader;
GRANT USAGE ON DATABASE sales TO ROLE mosaic_reader;
GRANT USAGE ON ALL SCHEMAS IN DATABASE sales TO ROLE mosaic_reader;
GRANT SELECT ON ALL TABLES IN DATABASE sales TO ROLE mosaic_reader;
CREATE USER mosaic_reader PASSWORD = 'choose-a-long-one' DEFAULT_ROLE = mosaic_reader;
GRANT ROLE mosaic_reader TO USER mosaic_reader;

#Where the password ends up

Encrypted with AES-256-GCM before it is stored, under a key held in a file outside the database, and never sent back to a browser once saved — the connection form shows that a password is set, not what it is. On a self-hosted installation both the encrypted value and the key sit on your own machine.

#What leaves the database

Rows that a comparison read, and only as far as Mosaic. Results go outward only where somebody has set that up — a scheduled email, a file, a webhook, or a page published for readers — and each of those is recorded in the audit trail. See Outputs and Storage and audit.

#To revoke

-- PostgreSQL
DROP ROLE mosaic_reader;
-- SQL Server
DROP USER mosaic_reader; DROP LOGIN mosaic_reader;
-- MySQL, MariaDB
DROP USER 'mosaic_reader'@'%';
-- Oracle
DROP USER mosaic_reader CASCADE;
-- Snowflake
DROP USER mosaic_reader; DROP ROLE mosaic_reader;

A revoked login stops the comparison and changes nothing else.

#If you would rather not create a login at all

Two alternatives, both of which Mosaic treats as ordinary sources:

• A read replica or reporting copy. If one already exists, point the login at that instead of production.
• A file. If a nightly job already drops a CSV or an Excel export somewhere Mosaic can reach, it can read that and no database access is needed. See Files.

The comparison is the same either way. It is slower to notice a problem from a file than from the live table, which is the only cost.