# PostgreSQL

Connect your PostgreSQL database and its tables come into <Term id="nexus">Nexus</Term> to analyse and build apps on. The work runs on the copy, never loading the database your product runs on.

Your <Term id="studio">Studio</Term>'s AI prepares the connection; you finish it with a user name and password that only reads.

## Before you start [#before-you-start]

- A <Term id="project">project</Term>. Its Studio's AI does the work. See [Create a project](/docs/projects/create-a-project).
- A **read-only database user**. Never give Klarety the login your application uses. Ask your database administrator to create one, or run this as a database owner (change the names to yours):

```sql title="read_only_user.sql"
CREATE ROLE klarety_reader LOGIN PASSWORD 'choose-a-long-password';
GRANT CONNECT ON DATABASE your_database TO klarety_reader;
GRANT USAGE ON SCHEMA public TO klarety_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO klarety_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO klarety_reader;
```

  The last line makes tables created later in that schema readable too, but only tables created by the user who runs these commands. Repeat the `USAGE` and `SELECT` lines for each schema you want to share.
- The database's address (host name and port) and the database name.
- The database must accept connections from outside your network. If it only accepts known addresses, talk to whoever runs it first.

## Connect it [#connect-it]

<Steps>
<Step>
### Open PostgreSQL [#open-postgresql]
Go to <AppLink to="connectors" />, search for **PostgreSQL** and open it.
</Step>
<Step>
### Send the request to a project's AI [#send-the-request-to-a-projects-ai]
Under **Connect it with your Studio's AI**, choose the **Project** that should use PostgreSQL. **What the AI is asked** starts as "Connect PostgreSQL to this project so it can load tables from your database with a read-only login." Edit it to say exactly what you want, for example: "Load the customers and orders tables from our production database, and refresh orders every night". Then select **Send to AI**.

The AI starts at once if the Studio is open, or when it next opens. Select **Open Studio** to go there.

<Screenshot src="/docs/_media/connect-data-with-ai-in-dashboard-data-page.png" alt="Connecting PostgreSQL with a project's AI from the Connectors page" />
</Step>
<Step>
### Finish the connection [#finish-the-connection]
The AI drafts the connection and never asks for a key in the chat. The draft shows as **Waiting for you**. Select **Connect** to open the **Connect PostgreSQL** dialog.

For a database, the dialog has two fields: **User name** and **Password**. Enter the read-only user you prepared. The dialog shows the only address the login is ever sent to, which is your database's address. Check it before you continue.
</Step>
<Step>
### Choose what the AI may do [#choose-what-the-ai-may-do]
Under **What the AI may do with it**, choose **Can read** (nothing there changes) or **Can read and change** (the AI may also add, change and remove things). Then select **Connect**.

<Screenshot src="/docs/_media/slack-connector-how-to-connect-in-ui.png" alt="The Connect PostgreSQL dialog with the access choice" />
</Step>
<Step>
### Test it and tell the AI [#test-it-and-tell-the-ai]
The dialog says **PostgreSQL is connected**. Select **Test it**, then **Tell the AI** so it carries on.
</Step>
</Steps>

You can also start inside a project: in **Settings**, **Keys**, describe it in **What do you want to connect?** and select **Ask AI to connect**.

<Callout type="warn" title="Keep it read-only">
  Choose **Can read**. Even with a read-only database user, this stops the AI trying to change anything.
</Callout>

## What you get [#what-you-get]

Each table you ask for becomes a Nexus <Term id="table">table</Term> with the same columns. The AI reads it with one query per table, so you can ask for a slice: "only orders from 2025 onwards" or "only the columns we need for reporting".

- Query the tables in the <AppLink to="data.sql" /> and join them with your other data.
- Ask the AI to keep a table fresh, for example every night. It builds an <Term id="agent">agent</Term> that reloads it on a schedule. See [What agents do](/docs/agents).
- Decide who sees each table in [Data access](/docs/data/data-access).

## Test, pause and reconnect [#test-pause-and-reconnect]

A project's connections are listed in its **Settings**, under **Keys**, with their status.

<Screenshot src="/docs/_media/manage-connections-by-toggle-on-off-per-project.png" alt="A project's connections in Settings, Keys" />

| Status | What it means |
| --- | --- |
| **Waiting for you** | Drafted by the AI. Select **Connect** to finish it. |
| **Works** | Connected and usable by the project. |
| **Needs reconnecting** | The key or sign-in stopped working. Select **Reconnect**. |
| **Paused** | Nobody can use it until you select **Resume**. |

From the same row: **Test**, **Pause**, **Turn off here** (this project stops using it) or **Remove** (deletes the key, and every Studio using it stops reaching PostgreSQL).

A connection belongs to the project it was made in. Owners and admins see all of them under **Your connections** on <AppLink to="connectors" />. It stays off for other projects until an admin switches it on under **Projects that may use** it.

## If the test fails [#if-the-test-fails]

**Test** reports one of these:

- It works: the project reached the service.
- The key was rejected: select **Reconnect** and enter a new one.
- The service cannot be reached: check that it is up and accepts outside connections, then test again.
- Internet is off: the project's internet is **Off**. Turn it on in its **Settings**. With **Only allowed**, the Studio reaches only its connections and the sites you add.
- It is not connected yet: select **Connect**.

Specific to databases:

- **The login is refused.** Check the user name and password, and that the user has `CONNECT` on the database. Select **Reconnect** to enter them again.
- **A table is missing.** The read-only user cannot see it. Grant `USAGE` on its schema and `SELECT` on the table, then ask the AI again.
- **The address is not the one you expected.** Do not continue. Tell the AI the correct host in the chat; the address is not a secret.

## Where your key goes [#where-your-key-goes]

The key or sign-in is encrypted with a managed key service the moment you select **Connect**, and not even the AI can read it. The Studio never holds the key. If the one site it is sent to is not the service's own address, Klarety warns you and asks you to type the site to confirm.

## Next steps [#next-steps]

- [Tables](/docs/data/tables)
- [SQL editor](/docs/data/sql-editor)
- [How connectors work](/docs/connectors)