# How to revoke execute on a Postgres function

> Revoking execute from anon alone doesn't stop a function being called through the Data API. How Postgres and Supabase grant EXECUTE by default, and how to lock a function down.

Source: https://supahost.app/kb/revoke-function-execute · Updated 2026-11-11

You ran `revoke execute on function ... from anon`, yet the function can still be called with the anon key through `supabase.rpc()`. That's because the grant that lets anon call it usually isn't a grant to `anon`.

## Where EXECUTE comes from

Two separate defaults give callers access to a new function:

1. **Postgres grants EXECUTE to `PUBLIC`** on every new function. `PUBLIC` means every role, including `anon` and `authenticated`. Revoking from `anon` doesn't touch this grant.
2. **Supabase's default privileges** in the `public` schema also grant EXECUTE on new functions to `anon`, `authenticated` and `service_role` directly.

And because `public` is an exposed schema, any function there that a role can execute is reachable at `/rest/v1/rpc/<name>`.

## Lock down one function

Revoke from `PUBLIC` and the API roles together, then grant back only what you want. Include the argument types, since Postgres identifies functions by name and signature:

```sql
revoke execute on function public.approve_booking(uuid) from public, anon, authenticated;

-- only signed-in users may call it
grant execute on function public.approve_booking(uuid) to authenticated;
```

Check the result:

```sql
select
  has_function_privilege('anon', 'public.approve_booking(uuid)', 'execute') as anon,
  has_function_privilege('authenticated', 'public.approve_booking(uuid)', 'execute') as authenticated;
```

Through the API, a caller without EXECUTE now gets a `42501` permission error instead of a result.

`create or replace function` keeps the existing grants, but `drop function` followed by `create function` starts over with the defaults. Put the `revoke` in the same migration as the `create`.

## Change the defaults for new functions

To stop new functions being callable by everyone, change the default privileges for the role that creates them (`postgres` when you use Studio or migrations):

```sql
-- the built-in PUBLIC grant can only be removed globally, not per schema
alter default privileges for role postgres
  revoke execute on functions from public;

-- the Supabase grants to the API roles in the public schema
alter default privileges for role postgres in schema public
  revoke execute on functions from anon, authenticated;
```

After this, every new function needs an explicit `grant execute`. Functions that already exist keep their current grants.

Default privileges are stored per database, so this affects only the project you run it in.

## Keep internal functions out of the API

A function that should never be called over HTTP doesn't belong in `public`. Put it in a schema that isn't exposed:

```sql
create schema if not exists private;

create function private.recalculate_availability(room_id bigint)
returns void
language sql
set search_path = ''
as $$
  update public.rooms
  set free_slots = (select count(*) from public.slots s where s.room_id = recalculate_availability.room_id and s.booked_by is null)
  where id = recalculate_availability.room_id;
$$;
```

Triggers, cron jobs and other functions can still call it. If an RLS policy calls it, the calling role needs `usage` on the schema and `execute` on the function; see [security definer functions and RLS](/kb/security-definer-functions).

## On SupaHost

The Data API's exposed schemas are fixed (`public`, `storage`, `graphql_public`, `pgmq_public`), so any other schema you create, like `private` above, is never reachable over HTTP. The MCP server's `get_advisors` tool also flags functions in `public` with a mutable `search_path`.

## Related

- [Calling database functions (RPC)](/docs/api/rpc)
- [Roles and permissions](/docs/database/roles)
- [Exposed schemas](/docs/api/exposed-schemas)
- ["permission denied for table" (42501) errors](/kb/permission-denied-42501)