Feature wishlist: Gaps encountered migrating from Supabase to pgschema

[asonawalla]

Context

We recently migrated from Supabase's declarative schema management to pgschema's declarative approach. (Supabase supports both migrations and a declarative mode where you define schema in SQL files—we were using the latter.) The migration was successful, and we're happy with pgschema! The maintainers have been incredibly responsive (thank you for the quick turnaround on #220 and reviewing #226).

This issue documents the gaps we encountered and our current workarounds. None of these are blockers—we have working solutions for all of them—but we wanted to share this feedback in case it's useful for prioritization or if other Supabase users migrate to pgschema.

The common thread: Supabase setups tend to rely heavily on RLS, custom roles (anon, authenticated, service_role), and function-based auth patterns. These are all manageable outside pgschema, but it does mean maintaining several bootstrap scripts alongside the schema files.

---

Gaps and Workarounds

1. Database-Level Objects

What we needed:

• CREATE EXTENSION IF NOT EXISTS "pgcrypto" (for gen_random_uuid() in PK defaults)
• CREATE ROLE for anon, authenticated, service_role
• Role grants so the connected user can SET ROLE for testing

Current workaround: Bootstrap SQL files run via psql before pgschema apply.

Notes: We understand these are database-level rather than schema-level objects, so they may be intentionally out of scope. Just documenting for completeness.

---

2. Plan Database Configuration

What we needed: The plan database needs the same extensions as the target database. Without pgcrypto in the plan DB, planning fails because gen_random_uuid() doesn't exist.

Current workaround: A bootstrap script that creates pgschema_plan and installs required extensions before any pgschema plan command.

Potential enhancement: pgschema could auto-create the plan database and/or sync extensions from the target, or document this requirement more prominently.

---

3. Privileges (GRANT/REVOKE, ALTER DEFAULT PRIVILEGES)

What we needed:

• ALTER DEFAULT PRIVILEGES so new tables auto-grant to appropriate roles
• GRANT EXECUTE ON FUNCTION for auth-related functions
• REVOKE for tables that should be service-role-only or read-only

Current workaround: Two bootstrap files—one for default privileges (pre-apply) and one for explicit grants (post-apply).

Notes: This is our most verbose workaround. Supabase setups often have nuanced permission models (some tables user-accessible, some service-only, some read-only reference data), and managing this outside pgschema means keeping grants in sync manually when adding tables.

---

4. Function Metadata

What we needed:

• COMMENT ON FUNCTION for documentation
• SET search_path for security hardening
• SECURITY DEFINER for trigger functions that need elevated privileges

Current workaround:

• Function comments applied via post-apply bootstrap SQL
• ALTER FUNCTION ... SET search_path applied via post-apply bootstrap SQL
• Restructured functions to avoid needing SECURITY DEFINER where possible

Notes: pgschema supports COMMENT ON TABLE/COLUMN but not COMMENT ON FUNCTION. The SET search_path and SECURITY DEFINER/INVOKER clauses in CREATE FUNCTION are not preserved.

---

5. Policy Expression Normalization

What we encountered:

• Function qualifiers: public.current_user_id() vs current_user_id() (Policy expressions show perpetual diff due to schema qualification mismatch #220, fixed in fix: normalize policy expression schema qualifiers #222 ✅)
• Table qualifiers: public.users vs users (Perpetual policy diffs: table references not normalized #224, PR fix: normalize table references in policy expressions #226 pending)

Notes: These caused perpetual diffs until fixed. Thank you for the quick fix on #222!

---

Summary

Category	Workaround	Impact
Extensions	Pre-apply bootstrap SQL	Low
Roles	Pre-apply bootstrap SQL	Low
Plan database	Pre-apply bootstrap SQL	Low
Default privileges	Pre-apply bootstrap SQL	Medium
Explicit grants	Post-apply bootstrap SQL	Medium
Function comments	Post-apply bootstrap SQL	Low
Function settings	Post-apply bootstrap SQL	Medium
Expression normalization	Upstream fixes	Resolved/pending

For us, the privilege management is the main ongoing maintenance burden. The others are mostly set-and-forget.

---

Not Asking For

To be clear: we're not expecting all of these to be implemented! Some (like roles and extensions) may be intentionally out of scope. We just wanted to share our experience in case it helps inform priorities or helps other Supabase users who might hit similar gaps.

Thanks again for building pgschema—the declarative approach is a huge improvement over migration files, and the responsiveness on issues has been fantastic.

[tianzhou]

@asonawalla thanks for the detailed list. This is certainly helpful for anyone looking for migrating from Supabase.

I have addressed most of 4. Function Metadata.

For "SECURITY DEFINER for trigger functions that need elevated privileges", could you please provide an example. The simple test case does show it retains SECURITY DEFINER https://github.com/pgschema/pgschema/blob/main/testdata/diff/create_function/add_function/diff.sql#L38

[asonawalla]

For "SECURITY DEFINER for trigger functions that need elevated privileges", could you please provide an example. The simple test case does show it retains SECURITY DEFINER https://github.com/pgschema/pgschema/blob/main/testdata/diff/create_function/add_function/diff.sql#L38

Ah, this was a mistake - I can confirm this is actually working correctly on our end. Sorry about that!

[tianzhou]

@asonawalla 3,4,5 should have been addressed. While 1 and 2 are both by design.

I have also created a new release 1.6.0 https://github.com/pgschema/pgschema/releases/tag/v1.6.0

[asonawalla]

@tianzhou amazing, thanks so much!

Noticed a small gap still with search_path comparisons... submitted #235 for your review.

[silentworks]

@asonawalla do you mind sharing some of these bootstrap scripts you are using and how you apply them before running pgschema plan?

[asonawalla]

@asonawalla do you mind sharing some of these bootstrap scripts you are using and how you apply them before running pgschema plan?

I'm wrapping pgschema in a task runner that always runs a few sql files (via psql -f) before pgschema ops. Those files are idempotent and responsible for setting up the plan DB, extensions, and roles. I can't share the exact scripts publicly for security reasons, but I'll say they make heavy use of psql meta commands, for example:

SELECT format('CREATE DATABASE %I', 'pgschema_plan')
WHERE NOT EXISTS (
  SELECT 1 FROM pg_database WHERE datname = 'pgschema_plan'
)\gexec

\connect pgschema_plan

[asonawalla]

@tianzhou I think we've achieved everything we wanted to with this issue, right? Feel free to close if appropriate, I can file additional issues / fixes if they come up.