Connect your database

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.

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.

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?

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

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:

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.

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.

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:

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