Support using a different role before applying the migration

[Kjir]

In the Postgres databases I manage, the privileges are managed through a set of roles, which cannot be used to login. Instead each login user is assigned roles and to perform the various operations you need to SET ROLE <admin_role> before. My understanding is that this is a pretty common way to manage permissions in Postgres.

Unfortunately this is not compatible with migrate.

Ideally, there would be a -role <role> option, which would run SET ROLE <role> before performing any of the other queries.

One workaround is to specify SET ROLE <role> at the top of every migration file. This should work on the default schema, however it will fail if the queries are to be run on a different schema.

For example:

migrate -database "postgresql://user:<pwd>@postgres-db/mydb?search_path=some_schema" -path db/migrations  up
2023/03/31 14:42:02 error: sql: Scan error on column index 0, name "current_schema": converting NULL to string is unsupported in line 0: SELECT CURRENT_SCHEMA()

I do not know if other databases have similar concepts to manage permissions or if this problem only arises with Postgres.

[nickzelei]

For what it's worth, this can be done by suppling a postgres option.

migrate -database "postgresql://user:<pwd>@postgres-db/mydb?search_path=some_schema&options=-c%20role%3D<rolename>" -path db/migrations  up