Must be owner of table in Postgres
Must be owner of table error in PostgreSQL occurs when a user attempts to perform an operation that requires ownership privileges on a table, such as altering the table's structure, dropping the table, or changing ownership. This error indicates that the user executing the command does not have sufficient rights on the table to carry out the operation.
You are more likely to run into this issue if you use a 3rd-party service to create the tables on behalf of you. Because the 3rd-party service will use its own database user to create the table, and the created table owner will be that database user.
Here are the common causes and suggested fixes for this issue:
Performing Ownership-Required Operations
DROP TABLE, or
REINDEX TABLE require the user to be the owner of the table or a superuser in PostgreSQL.
If it's appropriate for the user to own the table, an existing superuser or the current owner can change the table's ownership to the user. This can be done using the
ALTER TABLE command:
Lack of Superuser Privileges
Even if a user has been granted all privileges on a table, certain operations still require superuser status or ownership of the table because these operations can affect the table's fundamental structure or behavior.
For operations that necessarily require superuser privileges, perform the operation using a superuser account. This approach should be used cautiously, as superuser accounts have unrestricted access to the database system.
Also note, cloud database providers only provide semi-superuser privileges. You can't perform certain operations at all.
If changing ownership or using a superuser account is not feasible, you can also manage access through roles. A superuser can grant the role that owns the table to the user needing to perform the operation:
EVENT TRIGGER to Change Ownership after Table Creation
In scenarios where tables are frequently created and ownership needs to be managed dynamically, consider using event triggers to automatically change ownership of newly created tables to a specific role or user
You can also use
EVENT TRIGGER to automate the ownership changes.
After creating the function, the next step is to create an event trigger that fires on
CREATE TABLE events.
This trigger will call the function you just created.
This trigger activates after any
CREATE TABLE operation completes and calls the fn_change_table_ownership() function to change the ownership of the newly created table(s).
Postgres permission model is more complex than MySQL. To learn the best practice, you can further check How to Manage Postgres Users and Roles.