With the addition of this migrations which is meant to be run by a postgres superuser, that user ends up creating the sqitch registry schema.
Which means any other user cannot access that schema by default and so we cannot use those users to run other migrations.
This is fixed manually by running these as the supersuser (or the user who created the sqitch registry schema):
GRANT ALL PRIVILEGES ON SCHEMA sqitch_zambia_prod TO canopy;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA sqitch_zambia_prod TO canopy;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA sqitch_zambia_prod TO canopy;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA sqitch_zambia_prod TO canopy;
ALTER DEFAULT PRIVILEGES FOR USER reveal IN SCHEMA sqitch_zambia_prod GRANT ALL ON TABLES TO canopy;
ALTER DEFAULT PRIVILEGES FOR USER reveal IN SCHEMA sqitch_zambia_prod GRANT ALL ON SEQUENCES TO canopy;
ALTER DEFAULT PRIVILEGES FOR USER reveal IN SCHEMA sqitch_zambia_prod GRANT ALL ON FUNCTIONS TO canopy;
With the addition of this migrations which is meant to be run by a postgres superuser, that user ends up creating the sqitch registry schema.
Which means any other user cannot access that schema by default and so we cannot use those users to run other migrations.
This is fixed manually by running these as the supersuser (or the user who created the sqitch registry schema):
We need to figure out how to work this into our migrations either on this repo, or this other one: https://github.com/onaio/ansible-sqitch-migrations