graphile / graphile/migrate

Create default roles on `reset` if they don't exist

Open
#179 3 comments 0 reactions 0 assignees View on GitHub
Dominant language
TypeScript
Stars
835
Forks
64
Avg merge
17m
Merged PRs (30d)
5

Description

### Feature description

It would be a great developer experience if the Owner and Authenticator roles could be created on `reset` if they don't already exist. The tool could create the roles automatically or allow developers to do so themselves in hooks.

If the tool handled it automatically, then it could take additional placeholder variables:

```jsonc
{
"placeholders": {
"DATABASE_AUTHENTICATOR": "!ENV",
"DATABASE_AUTHENTICATOR_PASSWORD": "!ENV",
"DATABASE_OWNER_PASSWORD": "!ENV"
// …
}
}
```

### Motivating example

I'm initializing a database for the first time and trying to avoid writing a one-off `setup_db.js` script by hooking into the graphile-migrate event system.

I tried to create the default roles in an `!afterReset.sql` script like so:

```sql
BEGIN;

DO $$
BEGIN
CREATE ROLE :DATABASE_OWNER WITH LOGIN PASSWORD :DATABASE_OWNER_PASSWORD SUPERUSER;
EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
END;
$$;

DO $$
BEGIN
CREATE ROLE :DATABASE_AUTHENTICATOR WITH LOGIN PASSWORD :DATABASE_AUTHENTICATOR_PASSWORD NOINHERIT;
EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
END
$$;


```

However, this fails because graphile-migrate attempts to run the script as the Owner before the Owner is created:

```shell
graphile-migrate: dropped database 'my_database'
Error: Failed to create database 'my_database' with owner 'my_database_owner': role "my_database_owner" does not exist
```

I was surprised by the error because I expected graphile-migrate to connect as the root user.

Alternatively, I tried creating the default roles in a `!beforeReset.sql` script. However, this fails because the `beforeReset` hook expects the database to exist before it's been created:

```shell
error: database "my_database" does not exist
```

Creating these roles is something all graphile-migrate users have to do, so all of us could benefit from this feature 😄.

### Breaking changes

I don't think so.

### Supporting development

I [tick all that apply]:

- [x] am interested in building this feature myself
- [x] am interested in collaborating on building this feature
- [x] am willing to help testing this feature before it's released
- [x] am willing to write a test-driven test suite for this feature (before it exists)
- [ ] am a [Graphile sponsor](https://www.graphile.org/sponsor/) ❤️
- [ ] have an active [support or consultancy contract](https://www.graphile.org/support/) with Graphile

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.