How to change OWNER for DATABASE, SCHEMA or TABLE on AWS RDS PostgreSQL. An how to REASSIGN owner for all objects on AWS RDS PostgreSQL

postgres-owner-grants.sql

--
-- Change database owner
--

ALTER DATABASE "db_name" OWNER TO user;

--
-- List schemas
--

-- db_name=> \dn
--               List of schemas
--        Name        |          Owner          
-- --------------------+-------------------------
-- public             | other_user
-- other_schema       | other_user

--
-- Change schema owner
--

ALTER SCHEMA other_schema OWNER TO user;

--
-- Check schema changes
--

-- db_name=> \dn
--                List of schemas
--         Name        |          Owner          
-- --------------------+-------------------------
--  public             | other_user
--  other_schema       | user

--
-- List schema tables
--

-- db_name=> \dt other_schema.*
--                           List of relations
--  Schema       |  Name  | Type  |   Owner          
-- --------------+--------+-------+----------------
--  other_schema | table1 | table | other_user
--  other_schema | table2 | table | user
--  other_schema | table3 | table | other_user1

--
-- Change table owner
--

ALTER TABLE other_schema.table1 OWNER TO user;


--
-- Check schema table changes
--

-- db_name=> \dt other_schema.*
--                           List of relations
--  Schema       |  Name  | Type  |   Owner          
-- --------------+--------+-------+----------------
--  other_schema | table1 | table | user
--  other_schema | table2 | table | user
--  other_schema | table3 | table | other_user1

--
-- Change the ownership of database objects owned by a database role
-- It will change the ownership only for the current database, not for all instance databases
--

REASSIGN OWNED BY other_user TO user;
-- or you can change more role at once
REASSIGN OWNED BY other_user, other_user1 TO user;

Change owner of all table:

select 'ALTER TABLE ' || table_name || ' OWNER TO new_owner;' from information_schema.tables where table_schema = 'public' \gexec