ALTER postgres table owned by user from the same group

Question

There are plenty of similar questions, but I'm still unable to understand something simple about postgres ALTER, DROP, roles and permissions.

I want to have a DB with multiple users belonging to the same group. When one of the users creates a table, others should be able to use it as if they created it themselves. I can make it work to some extent, but the ALTER and DROP remain a mystery.

For example, starting from scratch I create a database with two users:

CREATE DATABASE test_db;
\c test_db 

CREATE ROLE alice LOGIN INHERIT;
CREATE ROLE bjork LOGIN INHERIT;

Then I create a group and assign users to the group:

CREATE ROLE scientists;
GRANT scientists TO alice;
GRANT scientists TO bjork;
ALTER DEFAULT PRIVILEGES FOR ROLE alice GRANT ALL ON TABLES to scientists;
ALTER DEFAULT PRIVILEGES FOR ROLE bjork GRANT ALL ON TABLES to scientists;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES to scientists;

alice creates a table:

\c - alice
CREATE TABLE test_table (column_a TEXT);

bjork can read, write, but can't neither ALTER, nor DROP:

\c - bjork 
ALTER TABLE test_table ADD COLUMN column_b TEXT;
ERROR:  must be owner of relation test_table

SET'ing ROLE to scientists doesn't help either (and it shouldn't, because INHERIT)

set role scientists;
ALTER TABLE test_table ADD COLUMN column_b TEXT;
ERROR:  must be owner of relation test_table

What am I missing? Is it even possible to have test_table owned by alice and ALTER'ed by bjork?

Answer 1

You need to SET ROLE before you create the table, so that the role becomes the table owner.

Only the table owner is allowed to ALTER the table.