Thread: Database/Table Owner Question

Thank you in advance.

I realize only the superuser and table owner can change the ownership of a
table, but is there a way to modify the database owner privileges to do
the same?


Example:
userA is database owner and creates/owns 20 tables
superuser creates userB
superuser changes database owner to userB
userB cannot change userB cannot change ownership of userA_tables



I realize this was probably on purpose to set up the balance of power
between the table and database owner, but would there be a way to quickly
set the db owner's ability to change table owners in their database?

I'm hoping for a quick spot, maybe similar to the roles table, which would
require performing grants on all tables, but just updating a setting in
one place.  I don't want userB to become superuser, just able to change
table owners.


Reason:
We have a lot of test databases with multiple db_owners, but very few
superusers, and table_owners switch all the time.

>>> <Michael.L.Blume@frb.gov> wrote:
> We have a lot of test databases with multiple db_owners, but very few

> superusers, and table_owners switch all the time.

A quick, untested idea:

Create a table_owner role.

Create your users with NOINHERIT and GRANT table_owner to them as
appropriate.

REVOKE CREATE ON SCHEMA public FROM public.

GRANT CREATE ON SCHEMA PUBLIC TO table_owner.

A user would need to SET ROLE table_owner to create a table.
RESET ROLE would put them back to normal.

Just a thought....

-Kevin