Supported way for custom NOLOGIN table owner to create FK referencing auth.users(id)?

[melvoraglobalintelligence-web]

We are using hosted Supabase with PostgreSQL 17 and want to follow the documented pattern of having a public profile table reference auth.users(id).

However, our setup has an additional ownership/security requirement:

• public.profiles must be created and owned by our custom role edu_app_owner.
• edu_app_owner is NOLOGIN and NOBYPASSRLS.
• Our migration runner uses postgres, which can SET ROLE edu_app_owner.
• We want the validated FK:

public.profiles(user_id) REFERENCES auth.users(id)

The issue is that postgres currently has the relevant access to the managed Auth objects, while edu_app_owner does not. After SET ROLE edu_app_owner, those privileges are not additive.

We do not want to:

• grant broad SELECT on auth.users;
• use service_role as a DDL workaround;
• impersonate Supabase-managed roles such as supabase_admin or supabase_auth_admin;
• give the custom owner BYPASSRLS;
• make postgres the permanent owner of our application tables;
• replace the real FK with application-level or trigger-only validation.

What is the officially supported approach on hosted Supabase for this case?

Would either of these approaches be supported?

1. A temporary, tightly scoped DDL privilege window granting only:

USAGE ON SCHEMA auth TO edu_app_owner

and

REFERENCES(id) ON TABLE auth.users TO edu_app_owner

with no grant option, followed by revocation after the validated FK has been created.

OR

2. A Supabase/platform-authorized actor adds only the FK constraint to the already-created custom-owned public.profiles table, while leaving table ownership unchanged and granting no permanent Auth access to edu_app_owner.

If option 1 is supported, which managed role/action is the supported grantor on hosted Supabase, and will the FK remain valid and enforced after the temporary USAGE/REFERENCES privileges are revoked?

We are specifically looking for a supported hosted-Supabase procedure rather than a PostgreSQL workaround that might conflict with managed Auth ownership or future platform upgrades.

Thank you.

[PepBunny]

I can't give you the officially supported answer. I don't work at Supabase, and only a maintainer can say which role is allowed to touch auth on hosted.

On plain Postgres the privilege check happens at DDL time. After SET ROLE edu_app_owner, running ALTER TABLE public.profiles ADD CONSTRAINT checks USAGE on schema auth and GRANT REFERENCES (id) ON auth.users once. After that the FK lives in the catalog and every insert enforces through the constraint itself. Revoking those temp grants does not drop it.

The part people miss is DML. Keeping NOBYPASSRLS and avoiding broad SELECT on auth.users sounds clean until inserts into public.profiles need to look up auth.users and fail for lack of visibility. Postgres usually also needs SELECT (id) for the check and for ON DELETE and ON UPDATE handling. That column level grant is much narrower than table SELECT, something to check in your case.

With the table left owned by the app owner and postgres only doing SET ROLE, the FK enforces normally for a NOBYPASSRLS role once that visibility is sorted. I still would not treat the temporary window as sanctioned until someone on their side says so.

[somuai]

The officially supported, cleanest approach on hosted Supabase that satisfies all your security constraints without touching auth schema permissions is Table Ownership Transfer via postgres during migration.

Architectural Reality of PostgreSQL Foreign Keys

1. DDL-time check only: The REFERENCES privilege on the referenced table (auth.users) is checked strictly by the table owner at constraint definition time.
2. Runtime enforcement via internal system triggers: Once created, PostgreSQL foreign keys are enforced at runtime via internal C triggers (RI_FKey_check_ins, RI_FKey_noaction_del, etc.). These system triggers operate with internal catalog-level authority and do not evaluate whether the inserting role or the table owner has ongoing SELECT or REFERENCES on auth.users.
3. No Auth grant pollution: You do not need to grant temporary or permanent USAGE / REFERENCES on auth to edu_app_owner, avoiding grant drift during platform upgrades.

---

Supported Migration Procedure

Since your migration runner connects as postgres (which already holds the required privileges on auth.users), have postgres create the constraint and then transfer ownership to edu_app_owner:

Option A: New Table Creation

-- Run as postgres:
CREATE TABLE public.profiles (
    user_id uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    display_name text,
    created_at timestamptz DEFAULT now() NOT NULL
);

-- Transfer table ownership to your custom role:
ALTER TABLE public.profiles OWNER TO edu_app_owner;

-- Enable RLS and configure policies as needed:
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

Option B: Existing Table Already Owned by edu_app_owner

-- Run as postgres:
-- 1. Transfer ownership to postgres to attach the Auth constraint
ALTER TABLE public.profiles OWNER TO postgres;

-- 2. Add the validated foreign key constraint
ALTER TABLE public.profiles
    ADD CONSTRAINT profiles_user_id_fkey
    FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE;

-- 3. Restore ownership back to edu_app_owner
ALTER TABLE public.profiles OWNER TO edu_app_owner;

---

Verification

You can verify the table owner, constraint catalog state, and runtime behavior:

-- 1. Confirm edu_app_owner owns the table:
SELECT tablename, tableowner 
FROM pg_tables 
WHERE schemaname = 'public' AND tablename = 'profiles';

-- 2. Confirm the foreign key constraint is valid and active:
SELECT conname, confrelid::regclass AS referenced_table, convalidated
FROM pg_constraint
WHERE conrelid = 'public.profiles'::regclass AND contype = 'f';

-- 3. Verify DML as edu_app_owner:
SET ROLE edu_app_owner;
-- Inserting an existing auth.users id succeeds; inserting a fake uuid correctly fails with foreign_key_violation (23503)
RESET ROLE;

Why this avoids platform pitfalls

• Zero permissions modified on auth: Prevents permission collisions when Supabase deploys GoTrue or Auth schema migrations.
• Permanent Ownership: edu_app_owner remains the sole owner in pg_tables, retaining full control over future index additions, RLS policy management, and vacuum settings.
• pg_dump / Branching Compatibility: Supabase Database Branching and standard supabase db pull serialize the ownership transfer cleanly.