Hosted Supabase: can PUBLIC EXECUTE be revoked from existing supabase_admin-owned pg_catalog functions?

[GrowthHackIt]

Hi,

I'm trying to establish the supported least-privilege boundary for a custom server-side PostgreSQL LOGIN on hosted Supabase.

I've found several related discussions, but they don't quite answer this specific case:

#49421 — pg_net objects owned by supabase_admin; confirms project postgres cannot revoke owner-granted privileges and discusses relying on the Data API exposure boundary.
#50046 — essentially the same ownership issue for existing unaccent functions with PUBLIC EXECUTE.
#50335 — similar issue restricting pg_net objects owned by supabase_admin.
#50325 — least-privilege custom PostgreSQL runtime role in the presence of PUBLIC TEMPORARY and supabase_admin-owned extension functions.
#48063 / #48259 — related questions about supabase_admin default privileges, although those concern future/default ACLs rather than ACLs on existing objects.

My case differs slightly because the custom role would have a direct PostgreSQL connection, so keeping a schema outside PostgREST's exposed db_schema does not by itself remove the role's effective SQL privileges.

Specific issue

On our hosted project, these existing PostgreSQL functions:

pg_catalog.lo_create(oid)
pg_catalog.lo_creat(integer)
pg_catalog.lo_from_bytea(oid, bytea)

are owned by supabase_admin and grant EXECUTE to PUBLIC.

We want a dedicated application LOGIN that is genuinely read-only and can execute only a very small allowlist of our own bounded read-only functions.

Because PostgreSQL privileges are additive, the custom role inherits PUBLIC EXECUTE on the lo_* functions even if we grant it no direct table privileges and revoke everything we control from that role.

Our project postgres role is:

not a superuser;
not a member of supabase_admin;
not the owner of these functions;
and does not appear to have the grant authority required to remove the PUBLIC grants.

We are intentionally not trying to obtain supabase_admin, escalate the hosted postgres role, or use an unsupported workaround.

Questions

1. Is there any supported self-service mechanism on hosted Supabase to remove PUBLIC EXECUTE from these existing supabase_admin-owned pg_catalog.lo_* functions?
2. If not, should these ACLs be treated as platform-managed and intentionally immutable by project owners, similar to the guidance in How can a project owner revoke PUBLIC privileges on the `net` schema (pg_net)? #49421/How should PUBLIC EXECUTE on `unaccent` functions owned by `supabase_admin` be handled on hosted Supabase? #50046 for extension-owned objects?
3. For a custom PostgreSQL LOGIN with a direct SQL connection (rather than an anon/authenticated role accessing only PostgREST), what is the recommended Supabase pattern for achieving a genuinely read-only/least-privileged role when it necessarily inherits these PUBLIC function privileges?
4. Is there a supported provider-assisted mechanism for changing these specific ACLs, or should application architecture be designed on the assumption that these grants cannot be removed?
5. More generally, is a direct custom PostgreSQL LOGIN intended to be a supported security boundary for this level of least privilege on hosted Supabase, or is the recommended approach to expose only narrowly controlled functions through the Supabase API rather than give the application a direct PostgreSQL credential?

Our alternative is to remove the application's direct PostgreSQL observer credential entirely and expose its small set of aggregate/read-only observations through narrowly permissioned functions. Before redesigning around that, I'd like to establish whether the direct-login model can be restricted as intended.

We haven't changed these ACLs or attempted role escalation in production. I'm primarily looking for the supported hosted-Supabase security model, rather than a PostgreSQL privilege workaround.

Thanks.

[somuai]

Here is the concrete breakdown of the hosted Supabase security model, catalog ownership boundaries, and recommended architecture for least-privileged direct connections:

---

1. Is there a supported self-service mechanism to remove PUBLIC EXECUTE on supabase_admin-owned pg_catalog.lo_* functions?

No.
In PostgreSQL, only the object owner or a superuser can revoke permissions granted by the owner. On hosted Supabase:

• pg_catalog.lo_* (and related large-object functions) are owned by supabase_admin.
• The customer administrative role (postgres) is not a superuser and does not possess membership in supabase_admin.
• Consequently, executing REVOKE ALL ON FUNCTION pg_catalog.lo_create(oid) FROM PUBLIC; as postgres raises:

  ERROR: 42501: permission denied for function ...
  

---

2. Should these ACLs be treated as platform-managed and immutable?

Yes.
PUBLIC grants on built-in catalog functions (pg_catalog) and platform extension schemas (extensions, vault, auth, storage) are immutable platform-level defaults on hosted Supabase. Attempting to alter catalog objects owned by supabase_admin or supabase_auth_admin will always encounter permission denials.

---

3. Recommended pattern for achieving least privilege with a direct PostgreSQL connection

When an external worker or observer requires a direct PostgreSQL LOGIN and inherits PUBLIC function execution:

1. Schema isolation:
   Revoke USAGE on your custom schemas (or public) from PUBLIC. The role will only be able to interact with tables and views you explicitly grant:

   REVOKE ALL ON SCHEMA public FROM PUBLIC;
   GRANT USAGE ON SCHEMA app_readonly TO custom_observer;
   GRANT SELECT ON ALL TABLES IN SCHEMA app_readonly TO custom_observer;

2. Lock down search path:
   Prevent implicit function resolution by locking the role's search path to safe schemas:

   ALTER ROLE custom_observer SET search_path = app_readonly, pg_temp;

3. Underlying table privilege enforcement:
   While lo_create or lo_creat can be invoked, they cannot create permanent large object entries unless the role has insert/write permissions on pg_catalog.pg_largeobject. A read-only role without write privileges on underlying storage tables cannot write large objects.

---

4. Provider-assisted mechanism for custom catalog ACLs

There is no provider-assisted mechanism or support-ticket workflow to alter system catalog ownership or revoke PUBLIC execute permissions on a per-project basis, because system catalog templates are part of the shared platform image.

---

5. Direct LOGIN vs. API Gateway Boundary

• Direct PostgreSQL connections are appropriate for internal services, migrations, BI tools, and trusted backends where schema-level GRANT SELECT and RLS suffice.
• However, if your threat model requires absolute defense-in-depth against calling any built-in pg_catalog utility functions (or if untrusted tenants connect directly), direct SQL access is not the recommended boundary.
• In that scenario, the recommended Supabase architectural pattern is to route queries through PostgREST RPCs or Edge Functions where the application is strictly bounded to a narrow API contract and has no direct protocol-level SQL connection to Postgres.

If this clarifies the platform permissions boundary and design considerations, please feel free to mark this as the accepted answer.

[GrowthHackIt]

Thanks - appreciate the response