Flyway grants via sql migrations - not working

Question

conf properties values :

flyway.defaultSchema= discussions
flyway.schemas= discussions

migration is followed as below :

+-----------+---------+------------------------------+--------+---------------------+---------+
| Category  | Version | Description                  | Type   | Installed On        | State   |
+-----------+---------+------------------------------+--------+---------------------+---------+
|           |         | << Flyway Schema Creation >> | SCHEMA | 2020-03-23 15:55:38 | Success |
| Versioned | 1       | INITIAL SETUP                | SQL    | 2020-03-23 15:55:38 | Success |
| Versioned | 2       | R INITIAL SETUP              | SQL    |                     | Pending |
| Versioned | 3       | R1 INITIAL SETUP             | SQL    |                     | Pending |
| Versioned | 4       | CREATE TABLE TEMPLATE        | SQL    |                     | Pending |

Initial setup will create tablespace

create tablespace tablespace_dts location 'E:\Tablespace\tablespace_dts';

    create tablespace tablespace_mtd location 'E:\Tablespace\tablespace_mtd';

    create tablespace tablespace_ind location 'E:\Tablespace\tablespace_ind';

    create tablespace tablespace_out location 'E:\Tablespace\tablespace_out';

    create tablespace tablespace_temp location 'E:\Tablespace\tablespace_temp';

v2 will do following

begin

for c  in select 1 where not exists (select 1 from pg_user where  usename = 'app_user' ) loop
    raise notice 'in app_user';
    execute ' create user app_user with password ''adept''';

end loop;       

for c  in select 1 where exists (select 1 from pg_tablespace,pg_user where  spcname = 'tablespace_dts' and usename = 'app_user') loop
    raise notice 'in grant create on tablespace_dts to app_user';
    execute 'grant create on tablespace tablespace_dts to app_user with grant option';

end loop;   

for c  in select 1 where exists (select 1 from pg_tablespace,pg_user where  spcname = 'tablespace_mtd' and usename = 'app_user') loop
    raise notice 'in grant create on tablespace_mtd to app_user';
    execute 'grant create on tablespace tablespace_mtd to app_user with grant option';

end loop;

for c  in select 1 where exists (select 1 from pg_tablespace,pg_user where  spcname = 'tablespace_ind' and usename = 'app_user') loop
    raise notice 'in grant create on tablespace_ind to app_user';
    execute 'grant create on tablespace tablespace_ind to app_user with grant option';

end loop;


for c  in select 1 where exists (select 1 from pg_tablespace,pg_user where  spcname = 'tablespace_out' and usename = 'app_user') loop
    raise notice 'in grant create on tablespace_out to app_user';
    execute 'grant create on tablespace tablespace_out to app_user with grant option';

end loop;


for c  in select 1 where exists (select 1 from pg_tablespace,pg_user where  spcname = 'tablespace_temp' and usename = 'app_user') loop
    raise notice 'in grant create on tablespace_temp to app_user';
    execute 'grant create on tablespace tablespace_temp to app_user with grant option';

end loop;   

please note, here grants works

v3 will do following :

grant usage on schema discussions to app_user;

grant select on all tables in schema discussions to app_user;   

grant update on all tables in schema discussions to app_user;

grant insert on all tables in schema discussions to app_user;

grant create on schema discussions to app_user with grant option;

v4 creates table lets say webhook_certificate

So when I try to query table webhook_certificate from app_user in schema discussions. it says, permission denied, although i granted in v3.

if same v3 is executed manually, it works and allows to access discussions.webhook_certificate. Please note : v3 is marked as success, no failure during flyway migrate. Then how come grants not working.

Please help.

Answer 1

I have figured it out. Thanks for all your support.

V4 that is create table should be executed before V3 (grants script).

As grant is provided on existing objects. So at every migrate, I need to run grant script at end. For this, I can user afterMigrate - Runs after successful migrations.