# Connect your database

Give Justwon a read-only login to your PostgreSQL database, let it connect over a verified encrypted connection, and check and remove it.

This page is for the person who owns the database: a data engineer or a database administrator. Connecting takes three things from you: a read-only login, a way for Justwon to reach the database, and, only if your tables are named unusually, up to three views. An owner or an admin in Justwon then enters the connection details once.

Justwon connects to PostgreSQL. Microsoft Fabric, SQL Server and other engines are not offered at the moment, and neither are MongoDB or other NoSQL databases.

## What Justwon reads, and what it does not

- Justwon reads product usage from your database to answer questions such as how active an account is. It runs a fixed set of read-only queries. It does not run SQL that you or anyone else wrote, and it does not write to your database.
- It reads three kinds of things: your **accounts** (customers), your **users** and the account each belongs to, and your **usage events** (who did what, and when).
- It reads the columns that the mapping names, not whole rows. The users table's emails and display names are read to match your users to people Justwon already knows. The table and column names of the mapped tables are stored, and so are the computed metric values. No other row of your database is stored.
- A column whose name suggests sensitive data, such as a phone number, a national id or a password, is refused by name. Identity matching reads end-user emails only.
- A workspace connects one database. To use a second one, use a second workspace.

## Create the read-only login

Run this as an administrator of the database, in the database you want Justwon to read. Replace the password and, if your tables are not in `public`, the schema name. Repeat the `GRANT USAGE`, `GRANT SELECT` and `ALTER DEFAULT PRIVILEGES` lines for each schema Justwon should see.

```sql
CREATE ROLE justwon_readonly LOGIN PASSWORD 'choose-a-long-random-password';

GRANT CONNECT ON DATABASE your_database TO justwon_readonly;

GRANT USAGE ON SCHEMA public TO justwon_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO justwon_readonly;

-- Tables and views created later in this schema are readable too.
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO justwon_readonly;
```

To limit Justwon to specific tables or views, skip `GRANT SELECT ON ALL TABLES` and grant `SELECT` on each one by name. A smaller grant means a smaller list for Justwon to map.

### Justwon refuses a login that can write

At the start of every run, not only when you connect, Justwon checks the login it uses. It refuses to read, and tells the admin, if the login:

- is a superuser, or can create roles, create databases or replicate;
- can `INSERT`, `UPDATE`, `DELETE` or `TRUNCATE` on any table, view or other relation outside the system schemas, directly, through `PUBLIC` or through a role it belongs to. A grant on a single column counts;
- is a member of `pg_write_all_data`, `pg_execute_server_program`, `pg_read_server_files` or `pg_write_server_files`.

`CREATE` on the schema `public` is allowed. Before PostgreSQL 15 every role has it by default. A change that gives the login a write privilege stops the next run and is reported in Justwon.

### Check the login yourself

These three queries use the same rules. The first must return one row of all `f`. The second and third must return no rows.

```sql
SELECT rolsuper, rolcreaterole, rolcreatedb, rolreplication
FROM pg_roles WHERE rolname = 'justwon_readonly';

SELECT n.nspname AS schema, c.relname AS relation
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'v', 'm', 'p', 'f')
  AND n.nspname <> 'information_schema'
  AND n.nspname NOT LIKE 'pg\_%' ESCAPE '\'
  AND (has_any_column_privilege('justwon_readonly', c.oid, 'INSERT, UPDATE')
       OR has_table_privilege('justwon_readonly', c.oid, 'DELETE, TRUNCATE'));

SELECT p.rolname
FROM pg_roles p
WHERE p.rolname IN ('pg_write_all_data', 'pg_execute_server_program',
                    'pg_read_server_files', 'pg_write_server_files')
  AND pg_has_role('justwon_readonly', p.oid, 'MEMBER');
```

## Encrypted connections and the certificate

Justwon connects with TLS and verifies both the certificate and that it is valid for the host name you give. There is no plaintext option and no way to switch verification off. Your database must have TLS enabled (`ssl = on`).

If a public authority signed your server's certificate, you paste nothing. If a private or cloud-provider authority signed it, paste the root certificate into the **CA certificate** field of the connect sheet:

- **Format and size.** PEM text, one or several `BEGIN CERTIFICATE` blocks, and nothing else. A private key is refused. At most 32 KB and 20 certificates. The pasted certificates are the only trust anchors for that connection.
- **Paste only what your instance chains to.** A provider's full global bundle, such as the Amazon RDS global bundle, does not fit. To see which root your server uses, run `openssl s_client -starttls postgres -connect your-host:5432 -showcerts`. The last certificate in the chain, or the issuer named in it, is the one to paste. Download it from your provider's own documentation and compare its fingerprint there.
- **Amazon RDS and Aurora.** The certificate chains to an AWS root. Paste that root, not the bundle.
- **Azure Database for PostgreSQL.** The certificate chains to a Microsoft or DigiCert root. If the connect sheet answers with a TLS refusal, paste the root your server's chain ends in.
- **Google Cloud SQL.** A default instance's server certificate has no name for the instance's host, so host name verification fails. Cloud SQL is not supported with its default certificates. Write to support@justwon.ai if this is your setup.

## Network access

Justwon connects directly from the internet to the host and port you give.

- **Host.** A public DNS name or a public IP address. A private address (10.x, 192.168.x, 172.16 to 172.31.x, loopback, link-local and similar) is refused, and so is any host name that resolves to one.
- **Port.** `5432`, or a port from `1024` to `65535`.
- **Direct connections only.** Transaction poolers, such as PgBouncer in transaction mode or a provider's pooler, are not supported. Use the database's direct connection address.
- **Allowing addresses.** Justwon's outgoing addresses are not fixed, so there is no list to allow. If your database only accepts known addresses, ask your Justwon contact before you connect. Do not open the port to the whole internet to get around it.
- **Seeing Justwon.** Justwon identifies itself with `application_name = 'justwon'`, so you can see its sessions in `pg_stat_activity`.

## Optional: three views for an unusual schema

Justwon looks for your accounts, users and events by table name and column name. If your schema is too different for that to work, create these views and Justwon maps them with high confidence. You can create any of them. They live in your own database, in any schema Justwon's login can read. The connect sheet shows the same list under **Prefer to map your own tables?**

| View | Columns | Notes |
| --- | --- | --- |
| `justwon_accounts` | `account_id`, `name`, `domain` | `domain` is optional |
| `justwon_users` | `user_id`, `account_id`, `email` | `email` is optional |
| `justwon_events` | `occurred_at`, `user_id`, `account_id`, `event_name` | `occurred_at` must be a `date` or a timestamp type |

`account_id` must have the same type in all three views. An example over a typical schema:

```sql
CREATE VIEW justwon_accounts AS
SELECT id AS account_id, company_name AS name, website_domain AS domain
FROM customers
WHERE deleted_at IS NULL;

CREATE VIEW justwon_users AS
SELECT u.id AS user_id, u.customer_id AS account_id, u.email
FROM app_users u
WHERE u.deactivated_at IS NULL;

CREATE VIEW justwon_events AS
SELECT e.created_at AS occurred_at, e.actor_id AS user_id, e.customer_id AS account_id,
       e.kind AS event_name
FROM audit_log e
WHERE e.kind IN ('login', 'report_created', 'invite_sent');

GRANT SELECT ON justwon_accounts, justwon_users, justwon_events TO justwon_readonly;
```

Justwon does not list materialized views. Create a plain view over the materialized view and grant `SELECT` on the plain one.

### Index the events table

Before each query, Justwon asks PostgreSQL what the query would cost, with `EXPLAIN`, which does not run it. A query whose estimated cost is above the ceiling is not run. An events table with no index on the account column is the usual cause. Index the table, or the table under the view:

```sql
CREATE INDEX CONCURRENTLY events_account_time_idx ON audit_log (customer_id, created_at);
```

## Connect it and check it

The details are entered once, in the connect sheet. Selecting **SQL database** on the **Available** tab of **Integrations** opens it, with the fields for the host, the port, the database, the read-only user and the password.

1. Sign in to https://app.justwon.ai as an owner or an admin and open **Integrations**.
2. Open the **Available** tab, find **SQL database** and select it. Its card says **Connect**.
3. Fill in **Host**, **Port**, **Database**, **Read-only user** and **Password**. Add the **CA certificate** if your certificate needs one. The password is stored encrypted. Open **How to connect** in the sheet to copy the SQL for the login.
4. Select **Connect SQL database**. If Justwon cannot connect, it shows one fixed sentence, listed below.
5. Open the **Data model** tab. Justwon lists the tables and views it can read and proposes a mapping for your accounts, users and events. Check it and select **Confirm data model**. No usage data is read until you confirm it. Listing the tables and columns reads the catalog only.

Connecting is limited to a few attempts in ten minutes, so check the details before you try again.

To check from your side, run this while a read is going. It shows Justwon's sessions. They are read-only, and the server stops a statement after 30 seconds.

```sql
SELECT usename, application_name, state, query
FROM pg_stat_activity WHERE application_name = 'justwon';
```

Run the checks from the section on the read-only login again after any permission change.

## When the connection is refused

Justwon never shows the database driver's own message, because it can name hosts, users or tables. Each failure is one fixed sentence.

| What the sheet says | What to do |
| --- | --- |
| Justwon can only connect to databases on the public internet. | Use the public address of the database. An address inside a private network cannot be reached. |
| Justwon does not connect to that port. | Use 5432, or a port between 1024 and 65535. |
| The host name could not be found. | Check the spelling of the host. |
| The database did not answer in time. | Check that it accepts connections from Justwon and that the port is open. |
| The database refused the connection. | Check the host and port, and that the database accepts connections from Justwon. |
| A secure connection could not be set up. | Justwon needs TLS with a certificate it can verify. If your own or your cloud's authority signed the certificate, paste that CA certificate into the **CA certificate** field. |
| The login was not accepted. | Check the user and password, and that the server's access rules allow this user to connect to this database. |
| The database was not found. | Check the name of the database. |
| The login can change data, and Justwon only connects with a login that can only read. | Remove the login's write permissions, or create a login as shown above. |
| Too many connection attempts for this workspace. | Wait a few minutes and try again. |
| Justwon is already using its connection to this database. | Wait a moment and try again. |
| The database took too long to answer. | Try again. If it keeps happening, check that the login can read the tables and that the database is not overloaded. Index or narrow the tables the mapping reads. |
| The database sent back more than Justwon reads at once. | Point Justwon at a narrower table or view. |

If a message is not in this table, or the problem continues, write to support@justwon.ai.

## Disconnect

1. Open **Integrations** and the **Connected** tab, and select the SQL database connection.
2. On the **Configuration** tab, under **Disconnecting**, select the button for SQL database, then select **Disconnect**.

Justwon deletes the stored connection, the password included. It deletes the identity mappings it made from your users, and withdraws what the mapping had read. The sources it came from are closed, and are not shown or quoted again. After ten days Justwon removes the content it read from the tool; what Justwon worked out from it stays with the account. Reconnect within ten days to bring the sources back. Values filled in through field mappings are cleared, except a contract's dates. Nothing new comes in. What stays is the facts, scores and assessments, and the people and companies. Nothing remains in your database, because Justwon did not write there.

Then drop the login on your side:

```sql
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM justwon_readonly;
DROP OWNED BY justwon_readonly;
DROP ROLE justwon_readonly;
```