Question 114466 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Transfer table ownership

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?

Tags:

2

Answers


  1. 0

    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 🙂

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search