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