Search code examples
postgresqlpermissionsschemaprivilegesowner

Why owner of schema cannot change owner of table when the table has a different owner- Postgres

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.

Solution

  • 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.