# Why Revoke Execute Permissions on New Public Schema Functions in DeskcommCRM

> Learn why revoking execute permissions on DeskcommCRM public schema functions is crucial. Protect against privilege escalation, data leaks, and DoS attacks.

- Repository: [Rafael Melgaço/DeskcommCRM](https://github.com/melgarafael/DeskcommCRM)
- Tags: best-practices
- Published: 2026-09-12

---

**Revoking EXECUTE permissions on new public schema functions in DeskcommCRM prevents privilege escalation, data leakage, and denial-of-service attacks by ensuring only explicitly authorized roles can invoke internal database logic.**

DeskcommCRM relies on Supabase/PostgreSQL to manage its multi-tenant architecture, storing critical stored procedures in the **public** schema. By default, PostgreSQL grants execute permissions to the `public` role, which means unauthenticated users could invoke sensitive functions unless you explicitly **revoke execute permissions on new public schema functions in DeskcommCRM**.

## The Security Risks of Default Public Schema Permissions

When you create a function in the `public` schema without adjusting permissions, PostgreSQL automatically grants **EXECUTE** access to the `public` role. In Supabase environments, this includes the `anon` role used for unauthenticated API requests. This default behavior creates three critical attack vectors:

### Privilege Escalation

Malicious clients can invoke internal utilities intended only for privileged service-role code. For example, audit-log helpers or budget update functions—designed for backend processing—become callable by anyone with a public API key.

### Data Leakage

Functions that read sensitive tenant data may bypass Row-Level Security (RLS) filters if called with improper authorization. Without revoking execute permissions, an attacker could invoke a function without the necessary `organization_id` filtering, exposing data across tenant boundaries.

### Denial-of-Service

Resource-intensive functions, such as heavy analytics queries or AI-related remote procedure calls (RPCs), can be abused to exhaust database resources. Anonymous users could trigger expensive operations repeatedly, degrading performance for legitimate users.

## The DeskcommCRM Hardening Rule

To mitigate these risks, DeskcommCRM enforces a strict hardening doctrine: **every new function created in the `public` schema must immediately revoke execute permissions from both the generic `public` role and the unauthenticated `anon` role**.

According to the project's internal documentation in [`AGENTS.md`](https://github.com/melgarafael/DeskcommCRM/blob/main/AGENTS.md) (line 170), the rule states: *"Criou função em `public` → `revoke execute on function … from public, anon;`"* (Created function in `public` → revoke execute on function … from public, anon). The [`CLAUDE.md`](https://github.com/melgarafael/DeskcommCRM/blob/main/CLAUDE.md) file (line 446) reinforces this requirement as formal security doctrine.

This practice ensures that only explicitly granted roles—typically the `service_role` used by server-side code—can execute internal database logic.

## Implementation Pattern for Secure Functions

When adding new utilities to the public schema, follow this two-step pattern demonstrated in the migration file [`supabase/migrations/20260906020000_0222_fronteira_do_atendimento.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/migrations/20260906020000_0222_fronteira_do_atendimento.sql) (lines 29–121):

```sql
-- 1️⃣ Define the function with SECURITY DEFINER
CREATE OR REPLACE FUNCTION public.fn_update_budget_consumption()
RETURNS void AS $$
BEGIN
  -- internal logic that updates budget counters
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 2️⃣ Immediately revoke execution from generic roles
REVOKE EXECUTE ON FUNCTION public.fn_update_budget_consumption()
FROM public, anon;

```

After revocation, grant execution only to specific trusted roles:

```sql
-- Grant to service role only
GRANT EXECUTE ON FUNCTION public.fn_update_budget_consumption()
TO service_role;

```

## Automated Enforcement via Test Suite

DeskcommCRM validates compliance through automated testing. The test suite in [`tests/invariants/hardening-definer-varredura.test.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/tests/invariants/hardening-definer-varredura.test.ts) (line 222) asserts that every function in the public schema includes the required revocation comment or statement.

This invariant check prevents developers from accidentally committing functions with excessive permissions. If a migration creates a public function without the corresponding `REVOKE EXECUTE` statement, the CI pipeline fails, enforcing the security boundary before code reaches production.

## Summary

- **Default PostgreSQL behavior** grants EXECUTE to the `public` role, exposing functions to anonymous users in Supabase environments.
- **Revoking execute permissions** on new public schema functions in DeskcommCRM blocks privilege escalation, prevents cross-tenant data leakage, and protects against resource exhaustion attacks.
- **Implementation requires** immediately executing `REVOKE EXECUTE ... FROM public, anon` after every `CREATE FUNCTION` statement in the public schema.
- **Automated tests** in [`tests/invariants/hardening-definer-varredura.test.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/tests/invariants/hardening-definer-varredura.test.ts) enforce this hardening rule across the codebase.
- **Explicit grants** to `service_role` or other specific roles ensure only vetted backend code can invoke sensitive database functions.

## Frequently Asked Questions

### What happens if I forget to revoke execute permissions on a public schema function?

If you omit the revocation statement, the function remains executable by any role that inherits from `public`, including the `anon` role in Supabase. This allows unauthenticated users to call the function via the Supabase PostgREST API, potentially exposing internal logic or sensitive data to anyone with a public API key.

### Why does DeskcommCRM use the public schema instead of a private schema for internal functions?

DeskcommCRM utilizes the public schema for compatibility with Supabase's default configuration while relying on explicit permission revocation to maintain security. The `public` schema is the default target for Supabase client libraries, but by immediately revoking execute permissions and explicitly granting them only to `service_role`, the system achieves defense-in-depth without breaking existing RPC patterns.

### How does the automated test detect missing permission revocations?

The test suite in [`tests/invariants/hardening-definer-varredura.test.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/tests/invariants/hardening-definer-varredura.test.ts) scans migration files and function definitions to verify the presence of `REVOKE EXECUTE` statements or associated comments. When it identifies a function in the `public` schema lacking the required revocation, the test fails, blocking the pull request and preventing vulnerable code from merging into the main branch.

### Can I grant execute permissions to authenticated users rather than just service_role?

Yes, after revoking permissions from `public` and `anon`, you can grant EXECUTE to specific authenticated roles such as `authenticated` or custom role groups. However, DeskcommCRM typically restricts sensitive functions to `service_role` only, ensuring that internal business logic executes exclusively through vetted server-side code rather than direct client requests.