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

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  TO """ 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  TO """ 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).