BUG #17616: Silently skipping to update data to the database with owner privileges using flyway scripts
От | PG Bug reporting form |
---|---|
Тема | BUG #17616: Silently skipping to update data to the database with owner privileges using flyway scripts |
Дата | |
Msg-id | 17616-195bac997f5bac61@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #17616: Silently skipping to update data to the database with owner privileges using flyway scripts
|
Список | pgsql-bugs |
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?
В списке pgsql-bugs по дате отправления: