Let execute_sql run as a project role you choose

Open
  • Is something wrong?
#537 0 comments 0 reactions 1 assignee View on GitHub

Maintainers usually reply within 1 day

@barryroodt is already working on this.

Since Nov 9, 2026.

Assessment

This issue has not been assessed yet.

Description

The gap

execute_sql runs in one of two modes today: as postgres, or read-only with --read-only. An agent that needs some writes, say inserting into a drafts table but never deleting, has only the destructive-SQL confirmation between it and everything else.

That confirmation reads query text, and #523, #531 and #532 show the limits: a MERGE ... WHEN MATCHED THEN DELETE or a comment-prefixed UPDATE runs without a prompt, while an INSERT whose text holds ; drop asks for one (and in an unattended run, times out and loses the write). The thread on #532 notes that classifier coverage is still being decided, and #426 adds a parser, which will narrow the gap but can't close it for every statement Postgres accepts.

The idea

Let execute_sql run as a Postgres role the project owner creates for the agent, for example --db-role drafts_agent. The owner gives that role exactly the grants and RLS policies the agent needs. Then the database decides: the MERGE ... DELETE fails with permission denied for table films whatever the classifier thinks, and confirmations stay as a UX layer for the writes the role is allowed to make.

It would sit between the two modes that exist now, and fits with the least-privilege requests in #239 and #255.

One constraint

It can't be SET ROLE prepended to the query. The session would still be postgres, so the agent's own SQL could run RESET ROLE and get every privilege back. It needs a login that is only that role, the way read-only mode runs as a separate user rather than downgrading a postgres session.

The Management API already issues short-lived logins through POST /v1/projects/{ref}/cli/login-role (read_only, password, TTL). A variant that issues a login holding only a named project role, used by /database/query when a role is given, might be one way to build it. You'll know the internals far better than I do, so take that only as a pointer.

Context

I run an MCP server where agents are Postgres roles that can't write certain columns, and checking those roles by hand was error-prone, so I wrote a small checker: counsel guard. It reads what a role can actually reach. On the supabase/postgres 17.11 image, a role that was granted service_role can read and delete every decrypted secret in Vault, which is the kind of thing a scoped --db-role would let people rule out up front.

Happy to help test this, or to write up the role setup for the docs, if it's useful.

Dominant language
TypeScript
Stars
3k
Forks
432
Avg merge
1d 7h
Merged PRs (30d)
52

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from supabase/mcp

All issues in supabase/mcp

Similar issues

More TypeScript issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.