# Connect PostgreSQL

> Monitor PostgreSQL from your Mac's notch. A read-only query checks your database on a schedule, and each new row it finds reaches you once.

Source: https://coisland.app/docs/connect-postgres/

## What you need

- **You need:** A user and its password, from Your server, or your provider’s Connect page
- **The form asks:** Host, Port, Database, Connector name, User, Password, TLS
- **You can watch:** Custom SQL, Validation
- **Access:** One SELECT, WITH or SHOW at a time, in a read-only transaction that is rolled back

To connect PostgreSQL to CoIsland, you add a connector with the server's host, a database user and its password, then write a query to watch. You do it alone: any user that can sign in and read works, and no admin step or app registration is needed.

CoIsland connects to your server straight from your Mac: there is no CoIsland server and no CoIsland account. The password stays in your login Keychain. Every check is one statement inside `BEGIN READ ONLY`, rolled back after, so PostgreSQL itself refuses any write.

## Get a user and a password

- **Your own server:** a read-only role is safest.

  ```sql
  CREATE ROLE coisland_reader LOGIN PASSWORD 'a long password';
  GRANT pg_read_all_data TO coisland_reader;   -- PostgreSQL 14 and later; else GRANT SELECT on the tables
  ```

- **Amazon RDS or Aurora:** RDS console › **Databases** › your instance › **Connectivity & security** for the endpoint and port; **Configuration** for the database name.
- **Supabase:** your project › **Connect** (top bar). The direct host `db.<ref>.supabase.co` is IPv6 only unless you have the IPv4 add-on; the pooler host `aws-<n>-<region>.pooler.supabase.com` works everywhere, with the user `postgres.<ref>`.
- **Neon:** your project › **Connect** › the host (`ep-...neon.tech`), the role and its password.
- **Azure Database for PostgreSQL:** the server's **Overview** page, server name and admin login.
- **Google Cloud SQL:** the instance's **Connections** (public IP) and **Users**.

## Connect PostgreSQL in CoIsland

1. Open **Settings › Connectors** and click **+** (Add Connector).
2. Choose **PostgreSQL**, fill in the fields, then click **Test connector**.
3. Click **Add Connector**.

| Field | What to enter |
|---|---|
| Host | The server's address alone, like `db.example.com`; `localhost` for a server on this Mac. A URL with `postgres://`, a path or a password is refused |
| Port | Optional: 5432 unless given (Supabase's transaction pooler is 6543) |
| Database | Optional: the one named like the user unless given |
| Connector name | What monitors call it. CoIsland suggests the database's name |
| User | The database user |
| Password | Kept in your Keychain. Sent in clear or as MD5 only to a verified server or this Mac; otherwise only SCRAM-SHA-256 is used |
| TLS | **Encrypted, certificate verified** (default), **Encrypted, server not verified**, or **Not encrypted (this Mac only)** |

**Test connector** signs in and shows the user, the database, the server's version, how the connection is encrypted and how many tables and views the user can read.

### Which TLS to choose

- **Encrypted, certificate verified:** macOS checks the certificate against the authorities it trusts and the host name, like every other connector. Works with Neon, Azure and any server with a public certificate, or one whose authority you trusted in Keychain Access.
- **Encrypted, server not verified:** for a server that signs its certificate with its own authority: Supabase, Amazon RDS, Google Cloud SQL, a self-signed server. The connection is encrypted, but a server in the middle could pose as yours, so CoIsland then signs in only with SCRAM-SHA-256, which never sends the password. To verify instead, download the provider's root certificate and trust it in Keychain Access.
- **Not encrypted:** only for `localhost`, `127.0.0.1` or `::1`.

## Watch PostgreSQL: Custom SQL and Validation

**Custom SQL** runs any `SELECT`, `WITH` or `SHOW` and alerts on new rows, or when the row count crosses a number. **Validation** takes a table, a column and a rule (is null, is less than...) and writes the SQL for you. Under **Advanced**, **Database** runs a monitor on another database than the connector's, and **Schema** sets where unqualified names are found.

```text
-- name: Queries running over 5 minutes
-- kind: postgres.custom-sql
-- connector: shop
-- every: 5m
-- key: pid

SELECT pid, usename, state, now() - query_start AS running_for, query
FROM pg_stat_activity
WHERE state <> 'idle' AND now() - query_start > interval '5 minutes'
```

Values show as PostgreSQL writes them, times in the monitor's time zone. A check stops after 60 seconds, and reads at most 10,000 rows.

## Troubleshooting PostgreSQL connector errors

| Message | What to do |
|---|---|
| PostgreSQL refused the user or the password | Check both; a password reset in the provider's console replaces the old one |
| The server refused the sign-in: no pg_hba.conf entry... | The server does not accept this user from your address: ask for an entry, or allow your IP in the provider's network settings |
| The certificate of ... could not be verified | Choose **Encrypted, server not verified**, or trust the provider's root certificate in Keychain Access |
| This PostgreSQL server does not offer TLS | Turn TLS on at the server; for a server on this Mac, choose **Not encrypted** with host `localhost` |
| Monitors only read, in a read-only transaction | The SQL tries to write; monitors never do |
| cannot insert multiple commands into a prepared statement | A monitor runs one statement: remove what follows the `;` |
| the server asks for ..., which CoIsland does not speak | The server wants Kerberos, a certificate or channel binding only; use a password user |
| The server has no room for another connection | `max_connections` is reached; CoIsland waits and tries again |

## Frequently asked questions

### Can CoIsland change anything in my database?

No. Only `SELECT`, `WITH` or `SHOW` is accepted, one statement at a time, inside `BEGIN READ ONLY`, and rolled back.

### Does it need a PostgreSQL driver or libpq?

No. CoIsland speaks PostgreSQL's protocol itself, with SCRAM-SHA-256 or MD5 passwords.