PostgreSQL Permissions in Amazon RDS using Flyway Migration

1
votes

I'm trying to get Flyway up and running for database source control of Postgres and am running into permissions issues.

Scenario: New database on AWS RDS PostgreSQL instance.

  • RDS superuser configured is "postgres" and I login as that to create the db and setup roles.

  • I create a "flyway_user" account that I want the flyway tool to use and add it to the rds_superuser role.

  • I create a couple schemas that flyway will manage and GRANT ALL ON SCHEMA to the "flyway_user" account.

When I then try to have flyway do a migration to create tables in these schemas I get an error of permission denied. So what am I missing here? Thanks in advance for any advice.

2
postgresqlamazon-web-servicesflyway

2 Answers

3
votes

I know this question is quite old, but it shows up on Google and I've been having the same issues.

While these permissions might be a little too loose, this is what I've done to get Flyway migrating correctly when using RDS and a flyway specific user.

-- Create a Migrator Role
CREATE ROLE schema_migrator;
GRANT ALL PRIVILEGES ON DATABASE <database> TO schema_migrator;
GRANT ALL PRIVILEGES ON SCHEMA <schema> TO schema_migrator;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema> TO schema_migrator;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT ALL PRIVILEGES ON TABLES TO schema_migrator;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema> TO schema_migrator;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT ALL PRIVILEGES ON SEQUENCES TO schema_migrator;

-- Assign Role to flyway_user
GRANT schema_migrator TO flyway_user;
0
votes

From what I can tell, you have to set the password for the postgres user to flyway as flyway automatically uses that use. Based on http://flywaydb.org/contribute/code/database.html