Extension points for local database customization

[wilhuff]

Feature request

I'd like to be able to re-run some SQL at development database creation time that is not part of a migration but that is run before migrations run. Specifically, I'd like to be able to create roles that can login, but don't want to have the same password in each environment, nor do I want such credentials checked into source control.

Is your feature request related to a problem? Please describe.

I have some features that require going to Postgres directly from Node.js but I want to be able to sign in with a Postgres user to do so. In production it's straightforward to just CREATE ROLE and be on my way, but there doesn't seem to be any great way to durably create the same setup in development that would survive a supabase db reset.

There used to be a globals.sql, which even contained comments indicating it was a place to customize roles, but release v0.15.6 removed support for processing this file, moving Supabase's role definitions into the CLI itself. FWIW, I agree with that change, but now there really isn't a great way to apply that kind of customization to the database. Note that I'm not interested in modifying Supabase's own roles, just creating my own.

I note that as of v0.15.5, extensions.sql is now processed before migrations, so that user migrations can refer to extensions, but from what I gather, the intent of this file is only to install Postgres extensions. Creating roles in this file happens to work today, but this seems to be a hack at best. Should the needs of enabling extensions require changing the order of application of this file, my development environments might break. I also worry that this might be subject to a supabase db push, which I definitely don't want.

Describe the solution you'd like

I'd like there to be some supported way to describe pre-migration provisioning of the development instance. It's possible that the only action I'd like from you is to officially bless extensions.sql as the way to do that. If not, processing SQL scripts in a folder like supabase/init after start but before migrations would be ideal. A single file would also work. In either case, these provisioning SQL scripts would not be propagated via supabase db push.

I don't think I'm asking to re-enable the processing of globals.sql that was dropped. Users of the older version of the CLI that generated that file may still have this old file lying around, and re-enabling this would likely interfere with Supabase itself.

Describe alternatives you've considered

As I noted, adding roles to extensions.sql works today, but I worry that activities other than installing extensions in this file are unsanctioned.

I can also almost do this as a migration: create an empty migration, push it to preview and production, and then enter development-only actions in the migration. However, this is dangerous, because if we ever rebuild preview or create some other pre-production environment, the development-only migrations will be applied there, which is undesirable.

seed.sql does not work because it runs after migrations. I'd like to be able to describe the grants or policies for non-Supabase roles in the same migration that creates the tables. If a migration creates a grant to a role that doesn't exist, the migration fails.

[soedirgo]

Hey @wilhuff, sorry for the late response, and thanks for the thorough description!

This has been brewing in my mind for a while - I think it's time to put a solution in place.

extensions.sql is now processed before migrations, so that user migrations can refer to extensions, but from what I gather, the intent of this file is only to install Postgres extensions. Creating roles in this file happens to work today, but this seems to be a hack at best

I don't think extensions.sql would be sufficient for roles, since roles are cluster-wide objects - supabase db reset drops the database, but doesn't "reset" the cluster, so roles will persist. The solution will have to be something like globals.sql that is only run once on supabase start, but not on e.g. supabase db reset.

That means there are two things to support: a "roles" script and a "pre-migrations" script. ("globals" refer to cluster-wide objects i.e. roles and tablespaces, but we don't use the latter, so "roles" is more descriptive I think.)

I don't think I'm asking to re-enable the processing of globals.sql that was dropped. Users of the older version of the CLI that generated that file may still have this old file lying around

At this WIP stage I'm still open to this (I do breaking changes every now and then), but I agree it should be avoided if possible. There's actually a related issue #66 on this matter - if we make the roles script an external command, we can avoid breaking things for users of older versions. We can also use this for a pre-migrations script that covers extensions and maybe other things (no reason it should be limited to extensions).

To summarize, the solution I'm proposing is to extend the config with scripts roles, preMigrations, and seed which should cover all the issues above. Thoughts?

[wilhuff]

Those seem like they'd work, however the high-level you're proposing seems more general:

• One script to run when the a cluster is created
• One script that runs before migrations are run on a specific database
• One script that runs after migrations are run

It seems like you could give them names like init or create for the first, and the others could be pre and post and then it will feel less transgressive when putting something other than roles in the roles file. Then again, specialized filenames suggest a structure for users--my issue is that there weren't enough of these to cover the things I wanted in the database and it felt wrong to shoehorn what I wanted to do into an extension point not meant for it.

Also note that special support for cluster-wide operations isn't really necessary. Here's effectively what I have in extensions.sql today that works for me:

DO
$$
  BEGIN
    IF NOT EXISTS (
      SELECT FROM pg_catalog.pg_roles
      WHERE rolname = 'site'
    ) THEN
      CREATE USER site
        WITH ENCRYPTED PASSWORD '***';
    END IF;
  END;
$$;

A little bit of documentation about how the different scripts interact with the shadow database and branches and specific advice for how to do things like add roles could make a pair of pre- and post-migrations scripts enough.

[soedirgo]

I see, agree that pre- and post-migrations scripts are enough for now. I'll go ahead with that then 👍

[ydennisy]

Any updates here? :)

[daniel-j-h]

Specifically, I'd like to be able to create roles that can login, but don't want to have the same password in each environment, nor do I want such credentials checked into source control.

I ran into the use case described above where I want to create a custom role but I do not want to hard code its credentials into the roles.sql file and have it checked into our git repository. In my specific example this is a role for metabase so that I can have a read-only restricted role for quick dashboards and visualizations.

At the moment the roles.sql file gets read as-is and executed as sql against the database

cli/internal/db/push/push.go

Lines 66 to 75 in 7c08f7f

	func CreateCustomRoles(ctx context.Context, conn *pgx.Conn, w io.Writer, fsys afero.Fs) error {
	roles, err := fsys.Open(utils.CustomRolesPath)
	if errors.Is(err, os.ErrNotExist) {
	return nil
	} else if err != nil {
	return errors.Errorf("failed to load custom roles: %w", err)
	}
	fmt.Fprintln(w, "Creating custom roles "+utils.Bold(utils.CustomRolesPath)+"...")
	return apply.BatchExecDDL(ctx, conn, roles)
	}

For the use case of custom roles I believe the current roles.sql incentivizes hard-coding passwords and checking them in.