Thread: BUG #17616: Silently skipping to update data to the database with owner privileges using flyway scripts

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?

On Fri, Sep 16, 2022 at 10:00:24AM +0000, PG Bug reporting form wrote:
> About a week ago I found that one update script was silently ignoring
> updating data to a table.

> 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.

Lacking USAGE on a schema does not cause "silently ignoring updating data to a
table".  If the same table name exists in multiple schemas, it can cause you
to update a different schema's table.  Example:

===
create user alice;
create schema a;
create schema b;
grant usage on schema b to alice;
create table a.x (c int);
create table b.x (c int);
grant insert on table b.x to alice;
set session authorization alice;
set search_path = a, b;
insert into x values (1); -- updates b.x
\dt x
===

It's also possible to write a trigger that silently stops the update due to
the missing USAGE privilege.

> 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.

The database owner doesn't broadly bypass privilege checks.  It's normal that
"GRANT USAGE ON SCHEMA <our schema> TO "<owner of the db>"" could increase the
database owner's effective privileges.

> Is it possible there is some bug here
> related to some timing how the default privileges and the instantiated
> privileges are generated?

It's not impossible, but we'd need a self-contained test
(https://www.postgresql.org/docs/current/bug-reporting.html).