In my PostgreSQL instance (say with one unique database constituted of one table only), I have 2 users:
user_A, who can create and populate
user_B, who should only be able to read (
SELECT) from it.
It happens that, because of an API I do not control,
user_A keeps dropping and recreating
my_table from scratch.
This causes a reset of
user_B privileges on
Thus, I am forced to
my_db=# GRANT SELECT on TABLE my_table TO user_B;
I know (cf. Grant permissions to user for any new tables created in postgresql) default privileges can only be set for objects created by the user they are granted to (i.e., that would be easier to set default privileges to
user_B for tables created by
user_B) — but this is not the case here.
Moreover, as mentioned in question linked above, the documentation specifies that "You can change default privileges only for objects that will be created by yourself or by roles that you are a member of".
So I could have
CREATE ROLE super_role; GRANT super_role TO user_A; GRANT super_role TO user_B;
SET ROLE super_role before
user_A creates the table… but I unfortunately have no control over how
user_A creates it.
How can I
GRANT SELECT by default to
user_B on tables created by