pi-postgresql

Pi package that adds a postgresql_query tool so the AI can query PostgreSQL databases.

Install

Install from Git

Install the extension directly from the Forgejo repository:

pi install git:https://git.otmatas.com/pi-extensions/pi-postgresql.git

What this does:

1. Pi clones the repository into its package cache.

2. Pi runs npm install for the package dependencies.

3. The package postinstall script creates the config file if it does not exist:

   ~/.pi/agent/pi-postgresql/config.json
   

4. Restart Pi, or run /reload if Pi is already open, so the new postgresql_query tool is loaded.

5. Edit the config file with your PostgreSQL connection details before using the tool.

To test it temporarily without adding it to your Pi settings:

pi -e git:https://git.otmatas.com/pi-extensions/pi-postgresql.git

To update the Git-installed package later:

pi update git:https://git.otmatas.com/pi-extensions/pi-postgresql.git

Or update all installed Pi packages:

pi update --extensions

Install from npm

Once published to npm:

pi install npm:pi-postgresql

Local development install

From this folder:

npm install
pi install ./

Local development test:

npm install
npm run typecheck
npm test
npm pack --dry-run
pi -e ./

Security first — use a read-only PostgreSQL role

Always connect with a PostgreSQL role that only has read permissions on the target database/schema. Never use a superuser or owner/admin role, even with readOnly: true enabled.

The readOnly flag is a defense-in-depth safety net: it rejects dangerous SQL with a guard and runs accepted queries inside a PostgreSQL READ ONLY transaction. It is not a substitute for least-privilege database permissions.

Example:

CREATE ROLE pi_ro LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE your_database TO pi_ro;
GRANT USAGE ON SCHEMA public TO pi_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO pi_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO pi_ro;

Keep rejectUnauthorized: true when using TLS unless you knowingly use a self-signed certificate on a trusted network.

Configuration

This development version only reads and writes one config file:

~/.pi/agent/pi-postgresql/config.json

Config files are created with restrictive file permissions where the OS supports it.

{
  "host": "127.0.0.1",
  "port": 5432,
  "user": "postgres_readonly_user",
  "password": "",
  "database": "your_database",
  "ssl": false,
  "rejectUnauthorized": true,
  "readOnly": true,
  "maxRows": 100,
  "timeoutMs": 30000
}

Custom config paths, project-local config files, package-local config files, legacy paths, and environment overrides are not supported in this development version.

Read-only protection

readOnly: true is enabled by default. In this mode the extension rejects queries that do not start with SELECT or WITH, runs the query inside BEGIN READ ONLY, and blocks dangerous SQL such as:

• INSERT, UPDATE, DELETE, MERGE
• DROP, ALTER, CREATE, TRUNCATE
• CALL / stored procedure execution
• SELECT INTO
• row-locking SELECT ... FOR UPDATE / FOR SHARE
• admin/security commands such as GRANT, REVOKE, VACUUM, COPY, REFRESH MATERIALIZED VIEW

For best safety, also use a PostgreSQL role that only has read permissions. The code guard helps prevent accidental writes, but database permissions are the strongest protection.

Commands

This extension does not register slash commands.

Tool

• Tool: postgresql_query
  • query: SQL query
  • database: optional database override
  • maxRows: optional result row limit, capped at 1000
  • timeoutMs: optional query timeout
  • Returns results as a compact Markdown table, with full structured data kept in tool details.

Example

Ask Pi:

Query PostgreSQL for the 10 latest invoices and show customer, date, and total.

Pi can then call postgresql_query with a read-only query such as:

SELECT *
FROM public.invoices
ORDER BY invoice_date DESC
LIMIT 10;