Why Revoke Execute Permissions on New Public Schema Functions in DeskcommCRM

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 (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 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 (lines 29–121):

-- 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:

-- 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 (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 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 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}