Change "postgres" user ownership to another user

Question

I am using PostgreSQL 9.5.19.

I want to change the owner of some tables. All these tables are in the same database my_db in the public schema.

I have read the post Modify OWNER on all tables simultaneously in PostgreSQL.

Thanks to this post the operation succedeed but partially. Most of the table are owned by postgres user and not new_user.

I tried to manually change the owner but it failed.

Answer 1

You can change the ownership of tables owned by postgres if you are a superuser.

But you won't be able to REASSIGN OWNED, because postgres has to own the system objects.

Perhaps you can use psql's \gexec:

SELECT format('ALTER TABLE %I SET OWNER = willi;', table_name)
FROM information_schema.tables
WHERE ... \gexec

You can fill in the appropriate WHERE condition.