# Connect PostgreSQL to Prodpeek

Profile: `postgres/read-only` · connection kind: `postgres` · tier 2
Credential: The role's password
Suggested URL: `readonly@db.example.com:5432`

Query a Postgres server directly — named diagnostics plus a free SELECT that is only enabled when the role is not a superuser.

## What you must not do

You cannot create this credential on the user's behalf — it needs their login and,
usually, an approval step. Walk them through it and verify the result. Never ask
them to paste the credential into the chat; it goes straight into Prodpeek's
console, which encrypts it at rest. You can run these statements if the user gives you an admin psql session, but do not invent the password — have the user set it, or generate one and tell them to store it in their password manager. Never put it in chat.

## Grant exactly these permissions

- `A dedicated role with LOGIN and nothing else by default` — Prodpeek connects as this role. It is the fence, not the SQL text.
- `CONNECT on each database the connection should read` — Databases are then chosen per connection in the console.
- `USAGE on the schemas, SELECT on the tables` — The free SELECT can only read what the role can read.
- `pg_monitor (optional)` — Lets activity, replication slots and table statistics show other sessions' rows rather than only your own. Read-only by design.

## Refuse these, and say why if the user asks for them

- `SUPERUSER` — This is the one that matters. A superuser can COPY ... TO PROGRAM — command execution on the database host — and pg_read_file(), and a read-only transaction prevents NEITHER. Prodpeek detects it on connect and disables the free SELECT, keeping the named queries. Do not work around that; fix the role.
- `The postgres user itself` — It is a superuser. Using it is the same mistake with a friendlier name.
- `INSERT / UPDATE / DELETE / TRUNCATE on any table` — Every statement already runs inside BEGIN READ ONLY, but the credential is what makes that a guarantee rather than a setting we apply.
- `pg_read_server_files / pg_write_server_files / pg_execute_server_program` — Server filesystem and command execution, which is not reading your data.

## Verify before the credential is used

- SELECT rolsuper FROM pg_roles WHERE rolname = current_user;  →  must be false.
- Try an INSERT as the role. It must fail with a permissions error, not succeed.
- In Prodpeek, Test connection must NOT report 'connected as a SUPERUSER'.

## Then, in Prodpeek

1. Services → Add a service → choose the profile `postgres/read-only`.
2. Connection kind `postgres`, URL `readonly@db.example.com:5432`.
3. Paste the credential. It is encrypted in the store and never shown again.
4. Run **Test connection**. It lists every tool the upstream advertises and how the
   profile classifies each one. Anything under "not in the policy" is denied by
   default — report that list rather than assuming it is fine.

## Full human walkthrough

## The role is the fence

Prodpeek's free `SELECT` is not made safe by inspecting the SQL. A regex that
accepts statements starting with `SELECT` is not a read-only guarantee, because
this passes it and deletes rows:

```sql
WITH x AS (DELETE FROM users RETURNING *) SELECT * FROM x;
```

What actually refuses that is the role and the transaction: a role with no write
grants, inside `BEGIN READ ONLY`. Postgres does the refusing. That is why this
recipe spends its time on the role and barely mentions the tool.

## Create the role

Run as an admin, once per environment:

```sql
CREATE ROLE prodpeek_ro LOGIN PASSWORD 'generate-a-strong-one';

-- Per database the connection should read:
\c your_database
GRANT CONNECT ON DATABASE your_database TO prodpeek_ro;
GRANT USAGE ON SCHEMA public TO prodpeek_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO prodpeek_ro;

-- So new tables are readable too, without redoing this each time:
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO prodpeek_ro;

-- Optional: lets the diagnostic views show other sessions, not just this role's.
GRANT pg_monitor TO prodpeek_ro;
```

Repeat the `\c` block for each database. Databases the role cannot `CONNECT` to are
unreachable regardless of what is selected in the console — belt and suspenders.

## Verify before you paste it anywhere

```sql
SELECT rolsuper FROM pg_roles WHERE rolname = 'prodpeek_ro';   -- false
```

Then, connected as the role:

```sql
INSERT INTO some_table DEFAULT VALUES;   -- must fail with a permissions error
```

If the insert succeeds, the role has write grants and the Tier claim does not hold.
Fix the grants rather than relying on Prodpeek to refuse it.

## Connect it

**Services → Add a service → PostgreSQL.** The URL is a target, not a DSN:
`prodpeek_ro@db.internal:5432`. Paste the password as the credential.

Then **Choose databases** on the service card. Prodpeek reads the live list from the
server and you tick the ones this connection may read. Nothing ticked means it can
read nothing — an empty selection is a refusal, never a wildcard.

## The honest limit

A SELECT-only role still reads `users.password_hash`, personal data, and any API key
an application happens to store in a table. "Read-only" and "safe" are different
claims, and this is where the difference is sharpest. The database allowlist bounds
which databases; schema- and table-level scoping is not yet expressible in a profile
and is the next milestone. If a database holds something an agent should never see,
do not tick it.