Hosted Supabase: supported managed-routine PUBLIC EXECUTE boundary for a strict application role? #51928
Replies: 3 comments
Architecture & Guidance: Hosted Supabase Managed-Routine Boundaries & Least-Privilege DesignHere is the authoritative confirmation of platform limitations regarding managed 1. Platform Support Confirmation
2. Supported Architecture: Schema-Separated Data API BoundaryRather than fighting PostgreSQL's global pseudo-role inheritance in managed schemas, isolate your domain into dedicated schemas where you control default ACLs:
3. Step-by-Step ImplementationStep 1: Create Schemas & Enforce Zero-Default PrivilegesConfigure your application schemas so that future functions and routines never inherit CREATE SCHEMA app;
CREATE SCHEMA api;
-- Remove public creation and usage
REVOKE ALL ON SCHEMA app, api FROM PUBLIC;
-- Ensure newly created functions in application schemas have NO public execute
ALTER DEFAULT PRIVILEGES IN SCHEMA app, api REVOKE EXECUTE ON ROUTINES FROM PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA app, api REVOKE ALL ON TABLES FROM PUBLIC;Step 2: RLS Policies with Tenant Membership & MFA (AAL2) GuardsMove domain tables to ALTER TABLE app.command_workspaces ENABLE ROW LEVEL SECURITY;
ALTER TABLE app.command_workspaces FORCE ROW LEVEL SECURITY;
-- 1. Base Tenant Membership Filter (Permissive)
CREATE POLICY tenant_isolation_policy ON app.command_workspaces
FOR ALL
TO authenticated
USING (
EXISTS (
SELECT 1 FROM app.memberships m
WHERE m.tenant_id = command_workspaces.tenant_id
AND m.subject_id = auth.uid()
AND m.active = true
)
);
-- 2. Restrictive Guard: MFA (AAL2) Required for Sensitive Owner Operations
CREATE POLICY mfa_aal2_guard ON app.command_workspaces
AS RESTRICTIVE
FOR ALL
TO authenticated
USING (
-- Requires verified 2FA if executing sensitive commands
(auth.jwt() ->> 'aal') = 'aal2'
);Step 3: Narrow Boundary Function (Owned by Dedicated App Admin Role)Create an application schema owner (avoiding CREATE ROLE app_admin NOLOGIN;
GRANT USAGE ON SCHEMA app, api TO app_admin;
GRANT ALL ON ALL TABLES IN SCHEMA app TO app_admin;
CREATE OR REPLACE FUNCTION api.dispatch_workspace_command(
p_tenant_id UUID,
p_document JSONB,
p_revision BIGINT
)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY INVOKER -- Honors caller's RLS and JWT identity
SET search_path = app, pg_temp
AS $$
BEGIN
INSERT INTO app.command_workspaces (tenant_id, document, revision)
VALUES (p_tenant_id, p_document, p_revision);
RETURN jsonb_build_object('status', 'committed', 'revision', p_revision);
END;
$$;
-- Grant execution explicitly to the authenticated consumer role only
REVOKE ALL ON FUNCTION api.dispatch_workspace_command FROM PUBLIC;
GRANT EXECUTE ON FUNCTION api.dispatch_workspace_command TO authenticated;Step 4: Expose Boundary to PostgRESTIn the Supabase Dashboard under Project Settings -> API -> Exposed Schemas, add ALTER ROLE authenticator SET pgrst.db_schemas = 'api, public';
NOTIFY pgrst, 'reload config';4. Rollback PlanIf you need to revert the boundary: -- 1. Restore PostgREST exposure
ALTER ROLE authenticator SET pgrst.db_schemas = 'public';
NOTIFY pgrst, 'reload config';
-- 2. Drop application boundary schemas and roles
DROP SCHEMA api CASCADE;
DROP SCHEMA app CASCADE;
DROP ROLE app_admin; |
|
The short version: on hosted Supabase you cannot durably revoke 1. Why the REVOKE won't stick. 2. Why it's usually not a real privilege path. The Data API (PostgREST) only exposes the schemas you list in Settings → API → Exposed schemas — For your admission check, classify rather than blanket-reject: -- effective exposure per routine: API-reachable schema + ACL
select n.nspname as schema, p.proname,
p.prosecdef as security_definer,
exists (select 1 from pg_namespace x
where x.nspname = n.nspname) as in_public_path,
exists (
select 1 from aclexplode(coalesce(p.proacl, acldefault('f', p.proowner))) a
where a.grantee = 0 -- PUBLIC
and a.privilege_type = 'EXECUTE'
) as public_execute
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where n.nspname not in ('pg_catalog','information_schema','pg_toast');Treat as findings only the rows where If specific managed-owned routines must be callable but locked down, the supported pattern is an app-owned |
|
A correction to Step 1 in the first answer: For functions alter default privileges for role postgres revoke execute on functions from public;If migrations run as another role, repeat it for that role. Existing functions still need an explicit |
Uh oh!
There was an error while loading. Please reload this page.
Summary / impact: We need a supported least-privilege design for a private application on hosted Supabase. Directory activation remains disabled; this is not a reported outage or data leak.
Schema: Repository migrations define
tenants(id UUID),memberships(tenant_id UUID, subject_id UUID, role TEXT, active BOOLEAN)andcommand_workspaces(tenant_id UUID, document JSONB, revision BIGINT). These tables enable and force RLS. Separate directory reader, observation and lifecycle capabilities are local proposals, not assumed deployed.Roles: Existing application roles are owner, manager, editor, analyst, viewer and worker. Anonymous clients must not access private workspace records. There is no proposed cross-tenant Super Admin.
Rules: Access must follow current active tenant membership. Sensitive directory decisions additionally require the initiating owner, verified MFA, exact approval/revision/expiry and atomic audit/history preservation. Publication observations need a separate machine capability. An authenticated role alone is insufficient.
Actual blocker: Our last read-only inventory found PUBLIC EXECUTE on managed-owned non-system routines. Existing admission rejects undeclared effective routine permissions, even without schema lookup. No managed grants have been changed. Generic CREATE POLICY statements do not remove those EXECUTE privileges.
Reference:
https://supabase.com/docs/guides/troubleshooting/custom-role-inherits-privileges-that-were-not-explicitly-granted-ddaa1c
Request: Is a provider-assisted conversion to explicit service consumers supported, with an authorised executor, consumer matrix, upgrade handling and rollback? If unsupported, please confirm the limitation and recommend a same-project narrow Data API/RPC boundary without application SQL credentials or service-role bypass. Once the boundary is established, please identify any necessary minimal grants, CREATE POLICY statements and narrowly owned functions, with prerequisites and rollback. Please do not suggest blanket revocations or postgres-owned SECURITY DEFINER as a generic permission fix.
All reactions