How to delete a Postgres role

What you see

ERROR:  role "reporting" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema public
privileges for table public.invoices
owner of view public.monthly_revenue
3 objects in database p_app_two

Why it happens

Postgres won't drop a role while anything still refers to it: objects it owns, privileges granted to it, default privileges set for it. The last line matters most: roles belong to the whole Postgres server, not to one database, so the role can own objects or hold grants in databases other than the one you're connected to. DROP ROLE checks all of them.

How to fix it

1. Check you have the right role. Never drop roles the stack depends on: postgres, anon, authenticated, service_role, authenticator, pgbouncer, dashboard_user, anything starting with supabase_ or pgsodium, and sh_readonly. Dropping one of these breaks the API, Auth, Storage or the dashboard.

2. See what the role owns in the current database:

select n.nspname as schema, c.relname as name, c.relkind as kind
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.relowner = 'reporting'::regrole;

3. Move or remove its objects and privileges. In each database the error mentions, run:

-- hand anything it owns to postgres (keeps your tables and views)
reassign owned by reporting to postgres;

-- remove its remaining privileges and default privileges here
drop owned by reporting;

reassign owned comes first on purpose. drop owned on its own also drops every object the role owns, which is rarely what you want for tables.

4. Drop the role:

drop role reporting;

If the error still lists objects "in database p_something", repeat step 3 in that database.

Common snags

• "permission denied to reassign objects" / "must be able to SET ROLE": the role you're connected as needs to be a member of, or have admin rights on, the role being dropped. Usually the role that created it has this.
• Active sessions: an app still connected with the role keeps working until it disconnects. Stop it first, or you may see confusing errors later.
• Default privileges: alter default privileges ... to reporting entries also block the drop. drop owned clears them in the current database.

On SupaHost

• All projects on an instance share one Postgres server, and each project is a database named p_<name> (for example p_app_two). A role you create in one project's SQL editor exists in every project on the instance, which is why the error can point at another project's database.
• To run step 3 in another project, open that project's Studio (Dashboard → that project → Database tab → Open Studio) and use its SQL editor. On Business you can also use sudo supahost-psql app-two over SSH.
• The built-in roles are shared too, so a change to anon or authenticated (dropping, renaming, altering) affects every project on the instance.

Related

• Roles and permissions
• "permission denied for table" (42501) errors
• How to revoke execute on a Postgres function