The following bug has been logged on the website:
Bug reference: 17616
Logged by: Annika Ruohtula
Email address: annika.ruohtula@gmail.com
PostgreSQL version: 12.12
Operating system: Windows
Description:
Flyway scripts are using the owner account everywhere to create, update and
drop objects in the database. The scripts are also maintaining a lot of
configuration related data in the tables. The scripts also create other
privileges for application level users with ALTER DEFAULT PRIVILEGES command
in the first migration scripts. There are no issues with the privileges when
using the application in the test and production environments.
The flyway-scripts have been working very well a couple of years already.
There are about 200 migration scripts already.
About a week ago I found that one update script was silently ignoring
updating data to a table. The migration script was first adding a new column
to existing table and then updating data to the column. The same sql
statements were working fine when running them using SQL console. I also
moved the existing flyway script backwards in the running order and took
another older one in place of it. After that the older one which was working
fine in the running order earlier was now also ignoring updating the data to
the table (so the issue didn't seem to relate with the actual sql statements
but rather when the sql statements was run). So it seemed like the issue was
caused by some timing related thing (maybe elapsed time from the beginning
of executing the first flyway script).
I found a workaround which seemed to fix the issue: I added grant-statement
"GRANT USAGE ON SCHEMA <our schema> TO "<owner of the db>"" after adding the
column to the table and before the actually updating the data to the new
column. After that, the update-statement was working ok.
Then we decided in the team meeting to add the same statement before each
migration as a hook. Currently it has been working ok, but of course we are
a bit worried about the situation, because in theory this should not be
needed for the owner of the database. Is it possible there is some bug here
related to some timing how the default privileges and the instantiated
privileges are generated?