Transfer table ownership

Question

In Postgres, when a table owner, say "xxx" tries to transfer the table ownership to user "yyy", he is getting error

ERROR: must be member of role "yyy"

When I searched further, it can be fixed in two ways.

1. As a "Super user", we can transfer table ownership of any table. However it needs Super user/core DBA intervention every time.

2. As user "yyy" we need to grant like "grant yyy to xxx;", and then as user "xxx" transfer table ownership like "alter table test_table owner to yyy;". However, this seems aliasing user "yyy" to "xxx", and user "xxx" will get all permissions and grants of "yyy". Though table ownership is possible her, unnecessary privileges are also granted, which is not the intention.

Apart from these two ways, is there any way to transfer table ownership?

Answer 1

We had a lot of issues with this scenario and we came up with solution but in your case where permission escalation is not an option I think it's not possible, as only roles which can transfer ownership can be those which can access both roles, in your case xxx and yyy.

Per documentation for REASSIGN OWNED: https://www.postgresql.org/docs/current/sql-reassign-owned.html

REASSIGN OWNED requires membership on both the source role(s) and the target role.

Per documentation for REASSIGN OWNED: https://www.postgresql.org/docs/current/sql-reassign-owned.html

To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the table's schema.

Only idea which comes to my mind which you could try is that you could grant "less privileged role" to "more privileged role" in this case assuming:

• xxx - more privileges
• yyy - less privileges

GRANT yyy TO xxx and then from xxx role we could actually do reassign owned by yyy TO xxx

I have tested this locally with containers and it works, let me know if this helps :)

Answer 2

No, there is no way to avoid these requirements. As the documentation says:

You must own the table to use ALTER TABLE. [...] To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the table's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the table. However, a superuser can alter ownership of any table anyway.)

The key is in the parenthesis: changing ownership of an object won't allow you to do anything that you couldn't do with DROP/CREATE. Anything else would be a security bug.

There is REASSIGN OWNED to change object ownership in bulk, but the permission requirements are the same.