PostgreSQL

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

Policy	postgres/read-only
Connection kind	postgres
Upstream looks like	[email protected]:5432
Credential	The role's password
Tier	2 — Prodpeek is the only fence, and the profile says so
Time to set up	about 3 minutes

Grant exactly these

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.

Do not grant these

Each of these would undo the point of the rest

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.

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:

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:

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

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

Then, connected as the role:

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: [email protected]: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.

Check you got it right

• [ ] 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'.

If an agent is reading this

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.

Then add it to Prodpeek

Console: Services → Add a service, pick postgres/read-only, paste the credential. Or from an agent, add_service with no credential and hand over the drop link.

Then press Test connection and read all three lists.