Query a Database from Code

Query Postgres directly from agent or app code with a read-only role, PG* vault secrets and pandas

Published data models are the recommended way to give agents data. When code needs to query a database directly — ad-hoc SQL, a table that is not modeled yet, a long-running app — connect from the sandbox with a read-only role. This guide covers the role, the PG* secrets that carry the connection, the packages, and the query code.

It builds on two foundation pages:

• Vault — how secrets become environment variables, and Shared vs Per-user.
• Sandbox Environments — how packages are baked into an image.

Prerequisites

This guide is specific to PostgreSQL (local, Neon, RDS, or any Postgres-compatible database). The read-only role SQL, the psycopg2 driver, and the connection URL format are all Postgres-specific — other engines (MySQL, SQL Server, BigQuery, etc.) use different roles, drivers, and syntax.

You will need:

• A PostgreSQL database and a superuser or owner login to create the role.
• Access to run SQL (via psql, pgAdmin, or your provider’s SQL editor).
• An agent or app in DataGen whose code will run the queries.

Managed databases (Neon, RDS, Supabase) are already reachable over TLS, so no tunnel is needed — any sandbox with outbound network connects directly. Tunnels are only relevant for exposing a database that lives on your own machine.

---

1. Create a read-only user

Agents connect with a role that can only SELECT — never write, drop, or alter. This bounds the blast radius: a leaked credential exposes data at worst, it can never mutate or destroy anything.

SQL (any Postgres)

Run this as a superuser or the database owner, connected to <DB>:

create_readonly_role.sql

-- Create the read-only login role.
CREATE ROLE <RO_USER> WITH LOGIN PASSWORD '<RO_PASSWORD>';

-- Grant read-only access to the current tables.
GRANT CONNECT ON DATABASE <DB> TO <RO_USER>;
GRANT USAGE ON SCHEMA public TO <RO_USER>;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO <RO_USER>;

-- Optional: also read tables created later (e.g. by future migrations).
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO <RO_USER>;

The last line is optional. GRANT SELECT ON ALL TABLES only covers tables that exist today; ALTER DEFAULT PRIVILEGES extends read access to tables created later. Run the script as the role that owns the tables.

Run the script:

Local Docker

docker exec -i <POSTGRES_CONTAINER> \
  psql -U postgres -d <DB> < create_readonly_role.sql

pgAdmin

1. Create the login role

   In the left tree, right-click Login/Group Roles (under the server, not the database) and choose Create -> Login/Group Role…

   • General tab -> Name: <RO_USER>
   • Definition tab -> Password: <RO_PASSWORD>
   • Privileges tab -> Can login? = Yes (leave the others off)
   • Save
2. Grant read access

   Run the GRANT statements in the Query Tool: right-click your <DB> database -> Query Tool, then paste the grants from the other tab and execute.

Verify it is actually read-only:

# Reads succeed:
psql "<connection-url>" -c 'SELECT count(*) FROM "SomeTable";'
# Writes are refused:
psql "<connection-url>" -c 'CREATE TABLE nope (id int);'
#   -> ERROR: permission denied for schema public

---

2. Store the connection as PG* secrets

Store the connection as separate libpq PG* variables. Postgres drivers read these automatically, so your code never assembles or parses a URL, and passwords with special characters need no encoding.

In the vault bound to your agent or app, add each row below with Add credential -> Secret, setting Target to the variable name and Type by scope:

Target (env var)	Type	Example value
PGHOST	Shared with workspace	10.10.0.15 (private IP or internal DNS)
PGPORT	Shared with workspace	5432
PGDATABASE	Shared with workspace	analytics
PGSSLMODE	Shared with workspace	prefer (use require if the server has TLS)
PGUSER	Per-user	jsmith_ro — each member’s own role
PGPASSWORD	Per-user	each member’s own password

Host, port, database and sslmode are the same for everyone, so an admin sets them once as Shared. User and password belong to each member, so they are Per-user: each member clicks Connect on the row and enters their own.

Use the libpq names exactly. DataGen uppercases the Target, so pghost also arrives as PGHOST, but a different name such as PSQL_PASSWORD is never read by libpq.

The host is the value that changes with where the code runs:

Where the code runs	PGHOST	PGPORT
Your machine -> local Docker Postgres	localhost	5432 (published port; check docker ps)
A container on the same Docker network	postgres	5432
Company DB on your private network	private IP / internal DNS (e.g. 10.10.0.15)	5432
Anywhere -> Neon / RDS / cloud	provider hostname (e.g. ep-...neon.tech)	provider port

For Neon, the connection details are under Console -> your project -> Connection Details. Use the pooled (-pooler) host for agent and sandbox workloads.

Reachability. A private address like 10.10.0.15 only resolves from a sandbox on the same network. On-prem DataGen runs the sandbox inside your network, so it reaches the database directly — no tunnel. From a cloud sandbox, the database must be reachable via firewall allowlist, VPC peering, or a tunnel.

Alternative: a single DATABASE_URL secret

Prefer one value? Add a single Secret with Target DATABASE_URL and the full URL as its value, assembled from the same pieces:

postgresql://<RO_USER>:<RO_PASSWORD>@<HOST>:<PORT>/<DB>

Managed databases add TLS parameters, for example Neon:

postgresql://<RO_USER>:<RO_PASSWORD>@<HOST>/<DB>?sslmode=require&channel_binding=require

Read it with os.environ["DATABASE_URL"]. Set Type to Per-user when the URL embeds a personal credential, otherwise Shared with workspace.

If the password contains URL-special characters (@ : / ? # %), URL-encode them — @ becomes %40. The PG* approach avoids this entirely because libpq takes each value literally.

---

3. Bake the query packages

Give the sandbox pandas, sqlalchemy and psycopg2-binary through a Sandbox Environment. The built-in Data Engineering template already includes them (plus duckdb, polars and pyarrow). Create an environment from it, wait for the ready badge, then select it on your agent or app.

Manager	Packages	Why
pip	pandas, sqlalchemy, psycopg2-binary	the query stack — libpq is bundled in the -binary wheel
apt	libpq-dev	only needed if you switch to non-binary psycopg2

Alpine / musl base: psycopg2-binary ships only glibc wheels. On a musl image, use psycopg[binary] (psycopg 3 ships musl wheels) and connect with a postgresql+psycopg:// URL instead of postgresql+psycopg2://.

---

4. Query it

import pandas as pd
from sqlalchemy import create_engine

# Connection details come from the PG* env vars set in the vault -- nothing hardcoded.
# (For the single-secret setup instead, use create_engine(os.environ["DATABASE_URL"]).)
engine = create_engine(
    "postgresql+psycopg2://",
    pool_pre_ping=True,   # keeps pooled connections healthy across idle gaps
)

df = pd.read_sql('SELECT * FROM "SomeTable" LIMIT 100;', engine)
print(df.shape)

engine.dispose()   # release the connection pool when done

Quick end-to-end check

import psycopg2
with psycopg2.connect(connect_timeout=15) as c:   # PGHOST/PGUSER/PGPASSWORD from env
    cur = c.cursor()
    cur.execute("SELECT current_user, current_database();")
    print(cur.fetchone())   # confirms WHO you are and WHERE -- catches wrong-role bugs

---

Troubleshooting

Run fails with "Vault not ready"

A member the run runs as has not filled in their Per-user PGUSER / PGPASSWORD. They click Connect on each row in the vault. See Vault gotchas.

ModuleNotFoundError: sqlalchemy / psycopg2

The agent or app is not bound to an environment with the query packages, or the environment is still building. Check the badge on Sandbox Environments.

Why pool_pre_ping=True

A pooled connection can go stale between queries — the server’s idle timeout drops it, the DB restarts, or a serverless backend (Neon) suspends. pool_pre_ping sends a cheap SELECT 1 before reusing a connection, turning a stale-connection crash into a transparent reconnect. Cheap insurance for any long-running or intermittently-idle sandbox.

First query is slow (1-3 seconds)

The first connection after a Neon database has been idle triggers a cold start while the compute wakes up. Set a generous connect_timeout — it is not a network fault, and the next query will be fast.

Table "does not exist" errors

Quote PascalCase table names in raw SQL: 'SELECT * FROM "Workspace"'. Unquoted, Postgres lowercases the identifier and reports the table does not exist.

Connection times out

The sandbox cannot reach the host. A private IP only works from a sandbox on the same network; from a cloud sandbox, allowlist the database or use a tunnel or peering.

---

Security checklist

• The sandbox connects with the read-only role, never the owner.
• The connection lives in vault secrets (PG* or DATABASE_URL), never in code or git.
• ALTER DEFAULT PRIVILEGES is in place so future tables stay readable.
• (Postgres 14 and older) CREATE is revoked from PUBLIC so the role cannot write.
• Cloud databases use sslmode=require (add channel_binding=require where supported).
• Rotate the password if it was ever pasted into a chat, log, or ticket.

Next

Files

Upload documents and spreadsheets your agents can read.

Use data in agents

Attach published data models to agents and apps instead of raw SQL.