Ad-hoc read-only Postgres query CLI, built at Inkie. Works with any Supabase-backed (or plain Postgres) project.
pgr "select id, created_at, message from audit_events order by created_at desc limit 20"
pgr exists because the Supabase MCP server gives blanket, largely unrestricted database access out of the box, including write capability. That's more access than any agent should have against a production database.
pgr is the deliberate alternative: fast diagnostic queries against prod Supabase without opening the Studio UI, optionally scoped to a single agent's own allowlisted tables, and it never writes. If an agent needs a write, it hands over the SQL, and a human runs it directly or ships it as a migration. Writing is never something pgr itself does, on purpose, see Auth setup and Security notes below for how that's enforced, not just asserted.
Designed for use in Claude Code and other agent sessions as well as by hand: agents can run bun /path/to/pgr/src/cli.ts directly, script queries, or import the programmatic API.
Requires Bun (built and tested on Bun 1.x, no minimum version is
pinned in package.json). age and pass are optional, needed only for the
per-agent creds file and the pass-store credential options respectively, see
Auth setup; the plain SUPABASE_DB_URL env var option needs neither.
git clone <repo-url> pgr
cd pgr
bun installThen either install it as a real pgr command:
bun run build # bundles src/cli.ts to dist/cli.js, which the bin entry points at
bun link # puts a `pgr` executable in Bun's global bin directory
pgr --versionbun link writes the executable to Bun's global bin directory (~/.bun/bin on a
default install, bun pm bin -g prints it). Bun's own installer adds that to your
PATH; if you got Bun some other way, add it yourself or pgr won't resolve.
Undo the whole thing with bun unlink from this directory.
Or skip the build and run the source directly, optionally behind a shell alias:
bun /path/to/pgr/src/cli.ts "select 1 as n"
alias pgr="bun /path/to/pgr/src/cli.ts"pgr [options] "<SELECT query>"
pgr --help
pgr --version
Options:
--format=<json|table|csv> Output format (default: json)
--output=<file> Write output to file instead of stdout
--agent=<name> Override AGENT_NAME for per-agent table gating
--help Show this help
--version Show version
# JSON output (default)
pgr "select count(*) as n from tickets"
# Pretty table
pgr --format=table "select id, status, created_at from tickets order by created_at desc limit 10"
# CSV to file
pgr --format=csv --output=out.csv "select * from audit_events limit 500"
# CTE
pgr "with recent as (select * from audit_events order by created_at desc limit 5) select * from recent"pgr resolves credentials in this order. Every option below resolves the same
Postgres role by convention: pgr_readonly (SELECT-only grants,
BYPASSRLS, bounded statement_timeout).
The credentials are not what enforces read-only, though. pgr sets
default_transaction_read_only on every connection it opens, in the Postgres
startup packet, so the server refuses every write (INSERT/UPDATE/DELETE/DDL,
or a SELECT that calls a write-capable function), regardless of what the
supplied credentials are actually granted. Point SUPABASE_DB_URL at a
superuser connection string and you still cannot write through pgr.
That holds as long as the connection actually accepts the startup parameter.
pgr sending it correctly is verified on the wire by the test suite, but it has
not been verified against a live pooler, and not every pooler passes
connection-level startup parameters through to the backend. A pooler that
silently ignored the parameter, rather than rejecting the connection, would
leave that session writable, and only the SELECT-only grants and the SQL guard
would be standing between a query and a write. If you're unsure about yours,
point pgr at a direct connection (port 5432) rather than the transaction-mode
pooler (port 6543).
export SUPABASE_DB_URL="postgresql://pgr_readonly.<ref>:<password>@aws-0-<region>.pooler.supabase.com:5432/postgres"For unattended/agent use, pgr reads ~/agents/$AGENT_NAME/.pgr-creds.age (age-encrypted) or .pgr-creds (plaintext) when AGENT_NAME is set, so subprocesses don't need a warm gpg-agent. Ignore this unless you're running several separate agent processes that each need their own credential file.
Store the full connection string in the pass password manager:
pass insert -m pgr/readonly-connection-string
# Paste the postgresql:// connection string for pgr_readonly when promptedOptions 2 and 3 have to assume some layout. The defaults are generic, and each one is overridable with an environment variable, read fresh on every invocation:
| Variable | Default | What it sets |
|---|---|---|
PGR_AGENT_ROOT |
~/agents |
Directory holding one subdirectory per agent |
PGR_AGENT_CONFIG_FILE |
.pgr-agent.json |
Per-agent config filename, inside that subdirectory |
PGR_PASS_ENTRY |
pgr/readonly-connection-string |
pass entry holding the read-only connection string |
So the per-agent creds file is $PGR_AGENT_ROOT/$AGENT_NAME/.pgr-creds.age and
the table allowlist is $PGR_AGENT_ROOT/$AGENT_NAME/$PGR_AGENT_CONFIG_FILE. The
working-directory allowlist described under Per-agent table
allowlist is deliberately outside all of this: it
is always ./.pgr-agent.json, whatever these variables are set to.
If you already run a fleet of agents with its own conventions (agent homes
somewhere other than ~/agents, an existing per-agent config file you'd rather
extend than sit beside, a pass hierarchy organised your way), set these three
to match instead of reorganising to suit pgr. pgr only ever reads its own
pgr block in that config file and leaves the rest of the file alone, so
pointing it at a file you already use for something else is fine.
pgr --help prints the paths currently in effect, so you can check what a given
shell will actually resolve. Option 1 assumes nothing about where anything lives
and ignores all three.
pgr doesn't create or manage the Postgres role itself, you provision it once, directly against your database. A minimal role with the properties pgr expects (the role name is a convention, not a requirement, call it whatever fits your setup and point the connection string at that):
create role pgr_readonly with login nosuperuser nocreatedb nocreaterole noinherit connection limit 20;
-- Real read-only enforcement lives in pgr itself (see Security notes below),
-- but a SELECT-only role is still the right first layer: keep it scoped.
alter role pgr_readonly bypassrls; -- only needed if you connect directly (not via PostgREST/JWT) and RLS would otherwise return zero rows
alter role pgr_readonly set statement_timeout = '30s';
alter role pgr_readonly set idle_in_transaction_session_timeout = '60s';
alter role pgr_readonly set lock_timeout = '5s';
grant usage on schema public to pgr_readonly;
revoke usage on schema auth from pgr_readonly;
revoke usage on schema storage from pgr_readonly;
-- Grant SELECT on exactly the tables you want pgr to be able to read.
-- Start narrow and add as needed, there's no default allowlist.
grant select on public.your_table_here to pgr_readonly;
alter role pgr_readonly with password '<generate one, don''t hand-type it>';Store the resulting connection string via whichever of Options 1-3 above fits your setup. Rotate the password periodically and whenever someone with access leaves.
Opt-in, and off unless you switch it on. pgr gates table access in two
situations: when AGENT_NAME is set, as an environment variable or
per-invocation via --agent=<name>, and when a .pgr-agent.json sits in the
directory you run pgr from. With neither, you're in operator mode: no gate,
every table the role can read is queryable, and the rest of this section doesn't
apply to you.
When a name is set, pgr reads ~/agents/$AGENT_NAME/.pgr-agent.json and
allows only the tables listed under pgr.tables.allow (optionally limited to
some columns, see Column permissions). It is default-deny: a
missing file, a missing pgr block, or an empty list means no tables. Denied
queries exit 1 without opening a connection. information_schema and
pg_catalog are always readable, so an agent can still introspect the schema.
Both halves of that path are overridable: PGR_AGENT_ROOT for the ~/agents
part, PGR_AGENT_CONFIG_FILE for the .pgr-agent.json part, so the allowlist
can live in a per-agent config file you already maintain. See
Where pgr looks.
{
"pgr": {
"tables": {
"allow": ["your_table_here", "another_table"]
}
}
}Granting access means editing that file, it's plain JSON and there's no other
mechanism, and the change applies on the next invocation. Names are matched
case-insensitively, and a bare name in the list also allows its schema-qualified
form (your_table_here covers public.your_table_here). Other keys in the file
are left alone, pgr only reads its own block.
If AGENT_NAME isn't set, pgr looks for .pgr-agent.json in the directory you
ran it from. If that file is there, it gates the invocation on exactly the same
terms: same file shape, same pgr.tables.allow list, same default-deny, and
information_schema and pg_catalog are still readable. This is the scoped
read for one person in one project, with no agent homes to set up and no
AGENT_NAME to export. Drop the file in the project to scope pgr there,
delete it to go back to operator mode.
That location is fixed on purpose. PGR_AGENT_ROOT and PGR_AGENT_CONFIG_FILE
move the per-agent file, not this one, so what gates a given directory is
answerable by looking in it.
The per-agent tier wins outright. With AGENT_NAME set, pgr reads that
agent's file and ignores any .pgr-agent.json in the working directory,
including when the agent's own file is missing, which stays default-deny as
before. Nothing about an existing AGENT_NAME setup changes.
Gotcha: the trigger is the mere presence of AGENT_NAME. If your shell exports
it for something unrelated, this gate switches on silently and starts denying
queries. If pgr refuses a table you know your role can read, check
echo $AGENT_NAME first, then check for a .pgr-agent.json in the directory
you're standing in.
An entry in allow can be an object that limits a table to some of its columns,
alongside plain strings:
{
"pgr": {
"tables": {
"allow": [
"interviews",
{ "table": "memberships", "columns": ["id", "created_at", "role", "client_id"] }
]
}
}
}A plain string keeps its meaning: every column of that table. An object entry is
default-deny for columns: any column not in columns is refused. This works in
both the per-agent file and the working-directory file, and the table name
follows the same matching rules as a string entry (case-insensitive, a bare name
also covers its schema-qualified form).
For a table with a column list, pgr parses the query before it runs and refuses
it, without opening a connection, if it could read anything else. It resolves
columns through aliases, joins, CTEs, subqueries, LATERAL and set operations.
The query is refused if it:
- references a column that is not listed, anywhere in the query (
select,where,join ... on,using,group by,order by, window clauses), - uses
select *,m.*, or any other whole-row form (to_jsonb(m),row_to_json(m), a bare alias,m::text), - uses an unqualified column while a restricted table is in scope, since
pgrhas no catalog to tell which table it belongs to (writem.role, notrole), - renames columns with an alias list (
from memberships as m(a, b)), - calls a function that runs SQL from a string (
query_to_xmland friends), including as aFROMitem, - reads
pg_stats,pg_stats_ext,pg_stats_ext_exprs,pg_statisticorpg_statistic_ext_data, which expose sample values and histogram bounds of every column (the rest ofpg_catalogstays readable), or - is a query
pgrcannot parse. While column rules are active this is refused outright, whether or not it names a restricted table, because there is no way to tell what it reads. If a legitimate query is refused for this reason, write it in a simpler form (NATURAL JOIN,(t).col,TABLE tandCOLLATEare not understood by the parser).
count(*) and exists (select * ...) are fine, as they read no column. A bare
name in order by that matches an output column is fine too.
The denial names the column and the config file:
pgr: access denied for column `memberships.email`.
Reason: memberships has a column allowlist and `email` is not on it (~/agents/me/.pgr-agent.json -> pgr.tables.allow).
Allowed columns: id, created_at, role, client_id.
The config is validated strictly. A file that is not valid JSON, an unknown key
under pgr or pgr.tables, an object entry with extra keys, a missing or empty
columns, a wildcard column, or the same table listed twice makes pgr exit with
an error naming the file and the offending path. It never falls back to a guess.
A missing file, or a file with no pgr.tables.allow, is still plain default-deny.
This is stricter than before: junk entries in allow (a number, null) and
malformed JSON used to be skipped silently and now stop pgr with an error.
Identifiers follow Postgres folding: unquoted names are case-insensitive, but a
quoted name keeps its case, so m."ROLE" is not the allowed column role.
Column names in columns are matched in lower case, so a column whose real name
has capital letters cannot be allowed this way (it fails closed); use a view with
lower-case names instead.
Known limitations: the list of functions that run SQL from a string is a
denylist, so something it does not name (dblink, a SECURITY DEFINER helper in
your database, a function added by a future Postgres version) can still read
columns this check cannot see. Like the rest of the SQL guard this is a local,
defence-in-depth check, not the security boundary. For hard column limits that hold for every caller, grant the
database role only those columns. Row-level filtering is not part of this.
The SQL guard is a secondary, string-level filter, not a guarantee. The real security
boundary is Postgres refusing writes on a default_transaction_read_only session, backed by
the SELECT-only grants on pgr_readonly; see Auth setup above. Treat the guard as
defence in depth against accidental or injected DML/DDL, and as the thing that gives you a
clear local error instead of a server one, not as a bypass-proof gate on its own:
- Allowed:
SELECTstatements, CTEs (WITH ... SELECT), subqueries, JOINs, aggregates, window functions - Blocked:
INSERT,UPDATE,DELETE,DROP,ALTER,TRUNCATE,CREATE,GRANT,REVOKE,COPY,SET,EXECUTE,CALL,DO - Blocked: multiple statements (
;-separated) - Blocked: dollar-quoted strings (
$$ ... $$), PL/pgSQL territory - Blocked: data-modifying CTE bodies (
WITH t AS (INSERT ... RETURNING *) SELECT * FROM t) - Blocked:
SELECT ... INTO newtable(creates a table) - Comment stripping prevents
-- SELECT\nDROP TABLEtricks, but this is a string filter, not proof against every bypass shape.
The guard biases toward false-positives, if a query pattern is borderline, it's rejected.
- Every connection
pgropens is read-only at the Postgres session level (default_transaction_read_only, set in the startup packet bysrc/client.ts). This is enforced by the server, not bypgr, and it does not depend on which role the supplied credentials resolve to. It is what closes the guard's string-level edge cases (write-CTE,SELECT ... INTO): even a query that slips past the guard has no writable session to execute in. There is no flag that turns it off. If you genuinely need write access, connect to the database directly, outside this tool. - The
pgr_readonlyrole is the second layer: SELECT-only grants,auth/storageschemas revoked, a boundedstatement_timeout. It limits what is readable, which the session setting does not. Keeppgrfor trusted operator/agent use only regardless. - Never expose
pgrto untrusted input via HTTP or any network interface. - Connection strings are never logged or printed.
- Queries themselves are logged. Every invocation appends one JSON line to
~/.pgr/audit/YYYY-MM-DD.jsonl: timestamp, the agent name (orcwd-projectwhen a working-directory allowlist gated it, oroperatorwhen nothing gated it), the full SQL text, the tables it referenced, and the allow/deny decision (plus the reason, on a deny). It's local-only, never transmitted anywhere, and best-effort, a failed write goes to stderr and the query still runs, so treat it as a record for you rather than a tamper-proof audit trail. Delete or rotate the directory yourself;pgrnever prunes it.
# Install dependencies
bun install
# Run tests
bun test
# Typecheck
bun run typecheck
# Run directly
bun run src/cli.ts "select 1 as n"
# Bundle the CLI to dist/cli.js (what the `pgr` bin entry points at)
bun run buildDay-to-day use runs src/cli.ts straight through Bun; bun run build is only
needed if you want a single bundled file to install or ship (npm publish is out
of scope for v0, see below).
See CONTRIBUTING.md.
v0 is the core query engine. Out of scope for now:
- Named query templates (
pgr report:weekly-summary) - Interactive REPL
- npm publish
MIT