Replies: 4 comments
|
Here is a direct diagnosis and supported solution for all three questions: 1. Customer-Side Recovery (Without Violating Security Invariants)Yes, you can perform this update cleanly from your Root Cause MechanicsIn PostgreSQL 16+, role memberships carry explicit granular options:
However, because GRANT app_rpc_owner TO postgres WITH SET TRUE;Why This Preserves Grantor InvariantsIn PostgreSQL 16, uniqueness in
Handling the Schema
|
|
From the role membership details you’ve provided, I wouldn’t try to force the replacement through the SQL Editor. There are two separate issues here.
PostgreSQL 17 distinguishes three membership options:
Your current membership has INHERIT FALSE and SET FALSE, so the SQL Editor session cannot exercise the function owner’s privileges through that membership. CREATE OR REPLACE FUNCTION requires ownership or appropriate owner-role privileges. Your 42501 errors are therefore consistent with the recorded membership configuration. I would avoid dropping and recreating the function under postgres. That could change its owner and security context, which you specifically want to preserve. One additional consideration: CREATE OR REPLACE FUNCTION preserves ownership and permissions, but other function attributes must be specified correctly. Make sure the eventual replacement explicitly preserves SECURITY DEFINER and the existing configuration settings.
The generated statement: SET lock_timeout TO 2s; should quote the duration: SET lock_timeout TO '2s'; That explains the reported 42601 syntax error, but fixing the quoting won’t resolve the ownership failure. I’d report it separately as a Dashboard SQL-generation issue, including the generated statement, PostgreSQL version and reproduction steps. I can’t confirm whether it’s already a known Supabase bug. Suggested recovery path Since this is a hosted project and you want to preserve the owner, ACL, role memberships and security configuration, I think a Supabase Support escalation is the appropriate next step unless an existing authorized owner-role connection is available. For the support request, you can collect a read-only snapshot: SELECT This records important function metadata without posting the function body or any application credentials. I’d include the two exact error codes and the existing role membership options, then ask Supabase whether it supports an owner-preserving replacement in this configuration. I haven’t reproduced this on a hosted project, so I wouldn’t claim there’s a supported self-service fix without Supabase confirming it. References: https://www.postgresql.org/docs/17/sql-createfunction.html https://www.postgresql.org/docs/17/role-membership.html https://supabase.com/docs/guides/database/postgres/roles-superuser |
|
Hi @walkys — I read through the thread and checked the two mechanisms against the PostgreSQL 16/17 docs and the Studio source, since the two answers above point in different directions. Short version: the self-service path @zisam-glitch sketched is consistent with how PostgreSQL 16+ handles membership grants, but it has two preconditions worth checking first, and I have not reproduced it on a hosted project — so please treat it as a test-first proposal on this TEST project, not a confirmed fix. The Dashboard quoting problem is real and separately confirmable in source. Why your 42501s are consistent with the recorded state
The self-grant mechanism, and why it should work in principle Since PostgreSQL 16, one (role, member) pair can have several membership rows, one per grantor, each with its own ADMIN/INHERIT/SET options (PG 16 release notes, "Allow multiple role membership records"). Because your Preconditions to check before running anything (all read-only): -- 1. Who owns the schema? The temporary CREATE grant below only works if postgres owns app_api or holds CREATE WITH GRANT OPTION on it.
SELECT n.nspname, pg_get_userbyid(n.nspowner) AS owner FROM pg_namespace n WHERE n.nspname = 'app_api';
-- 2. Confirm the membership row as recorded.
SELECT r.rolname AS role, m.rolname AS member, g.rolname AS grantor, am.admin_option, am.inherit_option, am.set_option
FROM pg_auth_members am JOIN pg_roles r ON r.oid = am.roleid JOIN pg_roles m ON m.oid = am.member JOIN pg_roles g ON g.oid = am.grantor
WHERE r.rolname = 'app_rpc_owner' AND m.rolname = 'postgres';
-- 3. Confirm the function identity and current settings first.
SELECT p.oid::regprocedure, pg_get_userbyid(p.proowner) AS owner, p.prosecdef, p.proconfig, p.proacl
FROM pg_proc p WHERE p.oid = 'app_api.staff(text,jsonb)'::regprocedure;If If the preconditions hold, the transactional shape @zisam-glitch proposed is the right one — run it as one transaction so any failure rolls everything back, and restate On the Dashboard In If the self-service path fails, escalate privately via Supabase Support with the project ref, the function signature ( Hope that helps, and I'd be interested to hear whether the self-grant step works on your TEST project — that would settle whether this is a supported recovery or a Support-only one. |
|
The membership mechanics are well covered above. What I haven't seen in the thread is the other half — making "we preserved the security configuration" a checked fact instead of an assumption, plus the diagnostics bundle for your question 2. I ran every query below against a PG16 fixture that reproduces your shape closely (custom owner role, 1. Capture before you touch anything — one row that holds every invariant you listed: select p.oid::regprocedure as signature,
p.proowner::regrole as owner,
p.prosecdef as security_definer, -- true => SECURITY DEFINER
p.proconfig, -- per-function GUCs, incl. search_path
p.proacl::text as function_acl,
pg_get_functiondef(p.oid) as definition
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'app_api' and p.proname = 'staff';Note the storage form: an empty 2. Dependents — the list that decides whether the fallback paths are safe: select d.deptype,
pg_identify_object(d.classid, d.objid, d.objsubid) as object
from pg_depend d
where d.refobjid = 'app_api.staff(text,jsonb)'::regprocedure
and d.refclassid = 'pg_proc'::regclass;In my fixture the view that selects from the function shows up here ( 3. Grants and schema ACL — select grantee, privilege_type
from information_schema.role_routine_grants
where specific_schema = 'app_api' and routine_name = 'staff';
select nspname, nspacl::text
from pg_namespace where nspname = 'app_api';4. Membership state (the rows Supabase Support will ask for): select r.rolname as granted_role, m.member::regrole as member,
m.admin_option, m.inherit_option, m.set_option
from pg_auth_members m
join pg_roles r on r.oid = m.roleid
where r.rolname = 'app_rpc_owner';The part worth knowing about preservation: on the same signature, select (p.proowner::regrole::text = 'app_rpc_owner') as owner_ok,
p.prosecdef as security_definer_ok,
(p.proconfig = array['search_path=""','statement_timeout=5s','lock_timeout=2s']) as proconfig_ok
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'app_api' and p.proname = 'staff';(One trap in that comparison: For question 2 — the escalation bundle is: the redacted On question 3 — file it separately. |
Uh oh!
There was an error while loading. Please reload this page.
Question
In a hosted Supabase TEST project, what is the supported way to update a function body when the SQL Editor role cannot inherit or SET the owning role, while preserving the existing security configuration?
Custom schema, function, and role names below are placeholders. Project identifiers, OAuth client IDs, credentials, tokens, and user data are omitted.
Current state recorded during troubleshooting
app_api.staff(text, jsonb), owned by custom roleapp_rpc_owner, withSECURITY DEFINER.proconfig): emptysearch_path,statement_timeout=5s,lock_timeout=2s.postgres, not a superuser.app_rpc_ownerhas grantorsupabase_admin,admin_option=true,inherit_option=false,set_option=false.The intended change is only one public OAuth client-ID literal in the function body. We want to preserve the owner, SECURITY DEFINER status, function configuration, ACL, role memberships/options, and schema privileges. We do not want to replace this with a postgres-owned SECURITY DEFINER function or widen privileges as a workaround.
Observed SQL failures
From the current SQL Editor session:
CREATE OR REPLACE FUNCTION:42501: must be owner of function staff.SET LOCAL ROLE app_rpc_owner:42501: permission denied to set role(role name generalized).A proposed transactional replacement script with before/after invariant checks has NOT been executed. We do not have a customer-accessible session as the custom owner or as supabase_admin.
Separate Dashboard editor observation
During the same troubleshooting, the operator reported that Database → Functions → Edit → Save function generated:
SET lock_timeout TO 2sand failed with:
42601: trailing junk after numeric literal at or near "2s"This is an observed Dashboard save error, not a confirmed root cause for the ownership problem. Quoting the timeout correctly would not itself establish the permissions needed to replace the function.
What we need clarified
We understand that hosted projects do not provide customer superuser access and are not asking for access to the internal supabase_admin role or a privilege bypass. Any necessary security changes would need explicit review and authorization first.
References reviewed:
Thank you. This question concerns function ownership/replacement, separate from our earlier pooler authentication discussion (#53309).
All reactions