Back to Blog

How to Create a SELECT-Only Postgres User for AI

Don't give Cursor, Claude, or ChatGPT your app's Postgres login. Create a dedicated SELECT-only role, grant the right schemas, wire it into Synra, and verify writes fail.

Don’t paste your app’s DATABASE_URL into an AI connector.

That login usually owns the schema — migrations, deletes, truncates. Synra blocks writes at the gateway by default; the database should refuse them too.

Below is the Postgres recipe: a dedicated SELECT-only role for Cursor, Claude, or ChatGPT. Use it with staging first, gateway read-only, and table allowlists.

What the role should (and shouldn’t) do

Can: CONNECT, USAGE on the schemas you pick, SELECT on the tables or views you pick.

Can’t: INSERT / UPDATE / DELETE / TRUNCATE, DDL (CREATE / ALTER / DROP), or own the app’s tables.

Call it synra_readonly, cursor_ai, or claude_select — not postgres, not the Railway/Neon owner.

Step 1 — Create the role

Connect as a privileged user (psql, Neon SQL editor, Railway query tab, Supabase SQL editor):

CREATE ROLE synra_readonly WITH
  LOGIN
  PASSWORD 'generate-a-long-random-password'
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOINHERIT;

Generate a real password (password manager). You’ll put it in Synra once; you won’t type it into mcp.json.

Step 2 — Allow connect + schema usage

GRANT CONNECT ON DATABASE your_database TO synra_readonly;

GRANT USAGE ON SCHEMA public TO synra_readonly;

If your tables live under analytics or app, grant USAGE on those schemas too. Skip schemas the AI shouldn’t know exist.

Step 3 — Grant SELECT (pick one style)

All current tables in public:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO synra_readonly;

Only specific tables (better for AI):

GRANT SELECT ON
  public.orders,
  public.order_items,
  public.products,
  public.signups
TO synra_readonly;

Leave users, token tables, and raw message stores off the list unless you have a real reason.

Tables created later — default privileges so new tables don’t silently stay invisible (or worse, you grant too much later by habit):

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO synra_readonly;

Run that as the role that creates tables (often the app owner), or new relations won’t pick it up.

Views: grant SELECT on the view if you expose a sanitized user_id-only view instead of users.

Step 4 — Quick permission check

Still in SQL:

-- Should work
SET ROLE synra_readonly;
SELECT COUNT(*) FROM public.orders;

-- Should fail
UPDATE public.orders SET id = id WHERE false;

Reset when done: RESET ROLE;

If the UPDATE succeeds, stop — the role isn’t SELECT-only yet.

Step 5 — Wire it into Synra

Build a URI with the new user (not the owner):

postgresql://synra_readonly:PASSWORD@host:port/your_database?sslmode=require
  1. mcpserver.design → Connections → Add → PostgreSQL
  2. Paste the URI (or fill host/user/password)
  3. Name it by environment: staging-select-only
  4. Test connection → Save
  5. Restrict tables in table access if you want a second gate
  6. Copy the MCP endpoint into Cursor / Claude / ChatGPT

Host-specific URI notes: Neon, Railway (public proxy, not railway.internal), Supabase.

Step 6 — Verify from the AI client

In Cursor or Claude:

  1. “How many rows in orders?” — real number.
  2. “Run UPDATE orders SET id = id WHERE false.” — should fail (gateway and/or permission denied).
  3. Open Usage — SELECT succeeded, write didn’t.

If step 2 works, rotate the endpoint and fix grants before anyone else uses it.

Common gotchas

Still using the app user in Synra. Re-check the URI username. Easy to paste the old string.

permission denied for schema public. Missing GRANT USAGE ON SCHEMA.

Sees no tables. You granted on the wrong schema, or table allowlist in Synra is empty of the tables you expect — or you never GRANT SELECT.

New tables invisible. Default privileges weren’t set by the creating role.

Supabase / RLS. A SELECT grant is necessary but not always sufficient. RLS policies still apply; the role needs policies that allow the reads you intend (often a bypass role is wrong for AI — prefer policies that match a read-only analyst).

What this doesn’t replace

A SELECT-only role won’t pick staging for you, hide columns inside an allowed table, or make the MCP URL safe to commit (endpoint hygiene).

It will make the database itself reject writes — useful when those credentials show up somewhere other than Synra’s gateway.

Create the role, put it in Synra on staging, restrict tables if you need to, then ask something you already know the answer to.

7-day trial at mcpserver.design.

Related reading:

← Back to Blog