How to GRANT SELECT by default to B, on tables created by A?

Background

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 my_table,

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

Thus, I am forced to

my_db=# GRANT SELECT on TABLE my_table TO user_B;

every time user_A recreates my_table.

Notes:

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;

and then SET ROLE super_role before user_A creates the table… but I unfortunately have no control over how user_A creates it.

Question

How can I GRANT SELECT by default to user_B on tables created by user_A?

PostgreSQL has default privileges, the perfect tool for your requirement:

ALTER DEFAULT PRIVILEGES FOR ROLE user_a
   GRANT SELECT ON TABLES TO user_b;

Now user_b will automatically get SELECT privileges on all tables created by user_a.

StackExchange Database Administrators Q#238898, answer score: 9