Im trying to set a different role for the tables than the one I have in the settings This is due to how we deploy the app - each time it’s deployed a new pg user is created and used by the app. But all those new users inherit from the postgres one.
The problem is that migrations need to be run using the postgres role and not the dynamic user. To achieve that I need to do something like:
defmodule Wonderbot.Repo.Migrations.CreateServices do use Ecto.Migration def up do execute "SET ROLE postgres;" execute "CREATE EXTENSION IF NOT EXISTS hstore;" .....
So that’s working correctly, but the problem is with the schema_migrations table, which I don’t control. Since ecto creates it automatically and using the dynamic user.
And if I try to run a migration like
defmodule Wonderbot.Repo.Migrations.AlterMigration do use Ecto.Migration @disable_ddl_transaction true def up do execute "SET ROLE postgres;" execute "ALTER TABLE schema_migrations OWNER TO postgres;" end end end
It just hangs for ever, I guess it is because the schema_migration table is being locked by the migration process.
So is there any way to control how the schema_migration is being created so I can set the role?