Question: Exact scope of Supabase CLI login-role deletion

[compassgrowthco]

Question: Exact scope of Supabase CLI login-role deletion
Supabase's documentation recommends DELETE /v1/projects/{ref}/cli/login-role to remove cli_login_postgres and its “read-only counterparts.”
Before using this experimental endpoint, could someone from the Supabase team clarify:

1. Which exact PostgreSQL roles or role-name patterns can it delete?
2. Is the standard supabase_read_only_user role explicitly excluded?
3. Does the endpoint modify any other memberships, privileges, schemas, application data, or Auth settings?
4. What happens if a target role doesn't exist or the operation partially fails?
5. Is an expired cli_login_postgres role safe to leave in place temporarily while subsequent CLI operations use an explicit database password?
   I'm looking for the endpoint's documented behavior rather than project-specific troubleshooting.
   Relevant Supabase documentation

[cekuu35]

The endpoint reference answers part of this by its shape, even though the reference page itself is thin. Pulling together what the docs establish, what that implies for your five questions, and a protocol that gets you the exact answers empirically in under an hour.

What the reference docs do establish

• The endpoint is titled "Delete existing login roles used by CLI" — DELETE /v1/projects/{ref}/cli/login-role, explicitly marked experimental / Beta.
• It takes no request body and no role-name parameter — only the project ref. That's the load-bearing detail for your Q1: an endpoint with no selector argument can only operate on a fixed, platform-defined set — the roles the platform itself associates with CLI login usage (cli_login_postgres and its documented "read-only counterparts"). It cannot delete arbitrary or user-created roles, because it has no way to name them.
• OAuth scope required: database:write.
• Response on success: {"message": "ok"} — the API surface documents no per-role result list, which also tells you something: whatever it does, it reports a single aggregate status (relevant to your Q4 — see protocol below).

What the docs don't establish (your Q2, Q3, and the platform parts of Q4, Q5)

Whether supabase_read_only_user is inside that fixed set, whether the endpoint revokes memberships/grants rather than just dropping roles, and the partial-failure semantics are not documented anywhere I could find. I wouldn't assume the exclusion — "read-only counterparts" in the docs could plausibly cover it.

A verification protocol that answers all five questions with evidence

On a disposable staging project (this is an experimental endpoint — treat it like a migration you'd never run blind on prod):

-- 1. snapshot the "who's who and what can they touch" state
create table snap_roles as
  select rolname, rolsuper, rolcreaterole, rolcanlogin, rolvaliduntil
  from pg_roles where rolname not like 'pg\_%';

create table snap_members as
  select r.rolname as member, g.rolname as granted_role
  from pg_auth_members m join pg_roles r on r.oid = m.member
  join pg_roles g on g.oid = m.roleid;

create table snap_grants as
  select grantee, table_schema, table_name, privilege_type
  from information_schema.role_table_grants where grantee <> 'PUBLIC';

Then call the endpoint once, wait for 200, and re-snapshot:

-- 2. diff: which roles vanished, which memberships/grants changed
(select 'dropped', rolname from snap_roles
  except select 'dropped', rolname from pg_roles)
union all
(select 'member-lost', member || ' lost ' || granted_role from snap_members
  except select 'member-lost', m || ' lost ' || g from
    (select r.rolname m, g.rolname g from pg_auth_members x
      join pg_roles r on r.oid = x.member join pg_roles g on g.oid = x.roleid) t);

That diff is the answer to Q1–Q3 with receipts: you'll see exactly which roles it drops (and whether supabase_read_only_user moves), whether any membership or table-level grant changed for roles that survive, and — by snapshotting pg_authid-adjacent rolvaliduntil for supabase_read_only_user — whether the standard read-only role is touched at all. For Q4 (missing-role / partial-failure), run the protocol twice: the second call on an already-clean project shows the behavior when targets don't exist (compare the 200 {"message":"ok"} against the diff — if the second call also returns ok with zero changes, the endpoint is idempotent toward absent roles; if it errors, you have the partial-failure answer).

The interim question (Q5): is an expired cli_login_postgres safe to leave?

Reasonable to leave it for now, with two conditions: (a) verify it has no meaningful grants on your project (select * from snap_grants where grantee = 'cli_login_postgres' — the CLI login role is designed for read-only introspection, but verify on your project rather than trusting the design), and (b) treat its password as rotated-by-platform (the CLI's re-login flow replaces it; an old verifier sitting in pg_authid is not replayable as a credential). The alternative is plain SQL — drop role cli_login_postgres; after the dependency check (select depender ... from pg_shdepend via a superuser-capable session is awkward on hosted, so on hosted the endpoint-or-leave-it choice is the pragmatic pair).

Worth noting: the underlying ask — "document the exact target set in the reference" — is a fair docs gap to file separately. An experimental endpoint whose blast radius is only inferable from its parameter-less shape is exactly the kind of thing the reference page should spell out, especially since it requires the database:write scope.