Connect a database

Adea needs one thing from your database: a user that can only read. You create it, give Adea the details, and Adea starts reading. It takes about five minutes.

If you don’t run the database yourself, send the person who does the developer link. They connect it without an account.

Before you start

• Use a read replica if you have one. Adea runs one query at a time on each connection, and holds back when your database is busy. A replica keeps that load off your main database.
• The database must be reachable from the internet. Adea connects only to public addresses, never to a private network.
• Use SSL if your database supports it.
• Have the host, the port and the database name ready.

Allow Adea’s addresses

If your database sits behind a firewall or an allowlist, you allow Adea’s addresses there. When you add the database in Adea, the connect screen shows them with a copy button. Adea reaches your database from those and from nowhere else, and they are the same for every company. If you are setting this up for someone else, the developer link you send from Adea shows them too.

Create the read-only user

Choose your kind of database. Replace your_database, the other your_ names and the password with your own, and use a long password.

PostgreSQL

CREATE USER adea_readonly WITH PASSWORD 'choose-a-long-password';
GRANT CONNECT ON DATABASE your_database TO adea_readonly;
GRANT USAGE ON SCHEMA public TO adea_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO adea_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO adea_readonly;

If your tables are not in the public schema, repeat the schema lines for each schema Adea should read.

MySQL and MariaDB

The same statements work on both.

CREATE USER 'adea_readonly'@'%' IDENTIFIED BY 'choose-a-long-password';
GRANT SELECT, SHOW VIEW ON your_database.* TO 'adea_readonly'@'%';
FLUSH PRIVILEGES;

Amazon Redshift

CREATE USER adea_readonly PASSWORD 'choose-a-long-password';
GRANT USAGE ON SCHEMA public TO adea_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO adea_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO adea_readonly;

BigQuery

BigQuery has no users with passwords. You create a service account that can run queries and view data, and give Adea its key.

gcloud iam service-accounts create adea-readonly --project=your-project \
  --display-name="Adea (read-only)"
gcloud projects add-iam-policy-binding your-project --role=roles/bigquery.jobUser \
  --member=serviceAccount:adea-readonly@your-project.iam.gserviceaccount.com
gcloud projects add-iam-policy-binding your-project --role=roles/bigquery.dataViewer \
  --member=serviceAccount:adea-readonly@your-project.iam.gserviceaccount.com
gcloud iam service-accounts keys create adea-key.json \
  --iam-account=adea-readonly@your-project.iam.gserviceaccount.com

Adea checks that the key cannot write to any dataset, and refuses one that can. Before every query, Adea asks BigQuery how much data it would read, and refuses a query above the limit per query or per month. A question can’t run up your BigQuery bill.

Snowflake

CREATE ROLE adea_readonly;
GRANT USAGE ON WAREHOUSE your_warehouse TO ROLE adea_readonly;
GRANT USAGE ON DATABASE your_database TO ROLE adea_readonly;
GRANT USAGE ON ALL SCHEMAS IN DATABASE your_database TO ROLE adea_readonly;
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE your_database TO ROLE adea_readonly;
GRANT SELECT ON ALL TABLES IN DATABASE your_database TO ROLE adea_readonly;
GRANT SELECT ON ALL VIEWS IN DATABASE your_database TO ROLE adea_readonly;
GRANT SELECT ON FUTURE TABLES IN DATABASE your_database TO ROLE adea_readonly;
GRANT SELECT ON FUTURE VIEWS IN DATABASE your_database TO ROLE adea_readonly;
CREATE USER adea_readonly TYPE = SERVICE DEFAULT_ROLE = adea_readonly
  DEFAULT_WAREHOUSE = your_warehouse RSA_PUBLIC_KEY = 'paste-the-public-key';
GRANT ROLE adea_readonly TO USER adea_readonly;

Adea signs in with a key pair. Generate the pair, put the public key in the user as above, and give Adea the private key.

Connect it in Adea

1. Open Data and choose to add a source.
2. Choose the kind of database and enter the details, or paste a connection string.
3. Adea tests the connection one step at a time: it reaches the database, signs in, checks that the user can only read, and reads the list of tables. Each step turns green. If one fails, Adea says what to fix.
4. Adea reads the names of your tables and columns, and starts learning what they mean.

What Adea reads, and what it keeps

Adea does not keep a copy of your database. It reads what a question needs, works out the answer and drops the rows. Each query runs in its own short-lived process that holds only that one connection.

Every query is checked before it runs. Only a single read is allowed, and Adea refuses anything that could change data. Guardian looks at every pull of data on top of that.

If it doesn’t connect

What you see	What to do
Adea can’t reach the database	Check the host and port, and that the addresses Adea shows are allowed in your firewall.
Adea says the user can write	Adea refuses a user that can change data. Create the user as shown above and take away any other rights.
Sign-in fails	Check the user name and password. In MySQL and MariaDB the user must be allowed from any host ('%'). In PostgreSQL the user must be allowed to connect from outside your network.
Adea connects but sees no tables	Give the user SELECT on the schema that holds your tables, as in the examples above.
Questions are slow	Connect a read replica instead of your main database.