Missing role blocks plan and apply

[mrMoe]

Hi there,

currently I'm trying to evaluate if we can/should use pgschema in our in house software and stumbled over the following problem.
We have our credentials in an .env file.

$ go tool pgschema --help                                                                                                                                                                                                                                            Declarative schema migration for Postgres

Version: 1.10.0@unknown linux/amd64 unknown
...

$ go tool pgschema dump --debug --schema workspace > schema.sql
time=2026-06-03T15:05:04.707+02:00 level=DEBUG msg="Attempting database connection" host=localhost port=5432 database=app user=app_admin sslmode=disable application_name=pgschema
time=2026-06-03T15:05:04.715+02:00 level=DEBUG msg="Database connection established successfully"

$ go tool pgschema plan --debug --schema workspace --file main.sql
time=2026-06-03T14:29:53.425+02:00 level=DEBUG msg="Attempting database connection" host=localhost port=5432 database=app user=app_admin sslmode=disable application_name=""
time=2026-06-03T14:29:53.433+02:00 level=DEBUG msg="Database connection established successfully"
time=2026-06-03T14:29:56.201+02:00 level=DEBUG msg="Attempting database connection" host=localhost port=37513 database=pgschema_temp user=app_admin sslmode=disable application_name=""
time=2026-06-03T14:29:56.213+02:00 level=DEBUG msg="Database connection established successfully"
time=2026-06-03T14:29:56.214+02:00 level=DEBUG msg="Attempting database connection" host=localhost port=5432 database=app user=app_admin sslmode=disable application_name=pgschema
time=2026-06-03T14:29:56.224+02:00 level=DEBUG msg="Database connection established successfully"
time=2026-06-03T14:29:56.434+02:00 level=DEBUG msg="Executing SQL" description="drop temporary schema" sql="DROP SCHEMA IF EXISTS \"pgschema_tmp_20260603_142953_ef9e6b92\" CASCADE"
time=2026-06-03T14:29:56.434+02:00 level=DEBUG msg="SQL execution succeeded" description="drop temporary schema"
time=2026-06-03T14:29:56.434+02:00 level=DEBUG msg="Executing SQL" description="create temporary schema" sql="CREATE SCHEMA \"pgschema_tmp_20260603_142953_ef9e6b92\""
time=2026-06-03T14:29:56.435+02:00 level=DEBUG msg="SQL execution succeeded" description="create temporary schema"
time=2026-06-03T14:29:56.435+02:00 level=DEBUG msg="Executing SQL" description="set search_path for desired state" sql="SET search_path TO \"pgschema_tmp_20260603_142953_ef9e6b92\", public"
time=2026-06-03T14:29:56.435+02:00 level=DEBUG msg="SQL execution succeeded" description="set search_path for desired state"
time=2026-06-03T14:29:56.435+02:00 level=DEBUG msg="Executing SQL" description="disable function body validation for desired state" sql="SET check_function_bodies = off"
time=2026-06-03T14:29:56.435+02:00 level=DEBUG msg="SQL execution succeeded" description="disable function body validation for desired state"
time=2026-06-03T14:29:56.451+02:00 level=DEBUG msg="Executing SQL" description="apply desired state SQL to temporary schema" sql="--\n-- pgschema database dump\n--\n*****GRANT USAGE ON SEQUENCE "types_$_key_seq" TO app_workspace;\n\n"
time=2026-06-03T14:29:56.464+02:00 level=DEBUG msg="SQL execution failed" description="apply desired state SQL to temporary schema" error="ERROR: role \"app_workspace\" does not exist (SQLSTATE 42704)"
Error: failed to apply desired state: failed to apply schema SQL to temporary schema pgschema_tmp_20260603_142953_ef9e6b92: ERROR: role "app_workspace" does not exist (SQLSTATE 42704)

As we use a different login role during daily operation than the app_admin the temporary database can not be created because the dump doesn't know anything about how to create the app_workspace. app_workspace is part of the postgres server not the schema workspace that got dumped.

One workaround I already successfully tried is to create a main.sql like

-- main.sql with custom organization

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'app_workspace') THEN
        CREATE ROLE app_workspace WITH LOGIN;
    END IF;
END
$$;

\i schema.sql

But that feels like a third wheel. Additionally now pgschema has the role somewhere in its temporary files I do not know the location of. I imagine, this could lead to some downstream problems with colleagues why stuff works on my machine and not on theirs which are hard to debug.

So what was the original plan to handle database roles with pgschema and is there a more native way e.g. did I do something wrong with the dump? I do not want to just ignore the privileges but actively manage them.

Thanks for this great project and keep up the awesome work!