The owner of my schema - public is called 'eyal', and the owner of the tabled inside the schema, called 'postgres'.

I am trying to change my table owner (when im logged to user: 'eyal') to user: 'eyal' but i get the error - "must be owner of the table"

How is it possible to be an owner of a schema, but without the ability to change the schema tables owners?

I tryed using the following commands:

ALTER TABLE public.table_name OWNER TO "eyal";

&

GRANT ALL PRIVILEGES 
ON ALL TABLES IN SCHEMA "public"
TO "eyal";

but nothing works.

CodePudding user response：

The documentation describes this:

You must own the table to use ALTER TABLE. To change the schema or tablespace of a table, you must also have CREATE privilege on the new schema or tablespace. [...] 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 alternative is that you are a superuser, who can do anything.

CodePudding user response：

How is it possible to be an owner of a schema, but without the ability to change the schema tables owners?

https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PRIV

By default, users cannot access any objects in schemas they do not own. To allow that, the owner of the schema must grant the USAGE privilege on the schema. By default, everyone has that privilege on the schema public. To allow users to make use of the objects in a schema, additional privileges might need to be granted, as appropriate for the object.

A user can also be allowed to create objects in someone else's schema. To allow that, the CREATE privilege on the schema needs to be granted. In databases upgraded from PostgreSQL 14 or earlier, everyone has that privilege on the schema public

So it's like you as schema owner (eyal) granted create and usage privileges to another user (postgres) then (postgres) is the table owner.
Also https://www.postgresql.org/docs/12/ddl-priv.html#PRIVILEGE-ABBREVS-TABLE Schema only have two privileges: USAGE and CREATE. So as a schema owner, you can only apply these two privileges to other roles.