Say, that database "foo" is now owned by "user1"
Alter owner:
\c postgres
ALTER DATABASE "foo" OWNER TO "user2"
Reassign ownership (see also https://stackoverflow.com/a/13535184/1943126):
note: this will affect objects in current database ("foo") but also shared objects (databases,tablespaces)!!!
\c "foo"
REASSIGN OWNED BY "user1" TO "user2"
note: the following is probably not needed, covered by re-assignment of ownership
Revoke existing permissions from previous owner on all available schemas:
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM "user1";
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA "bar" FROM "user1";
REVOKE USAGE ON SCHEMA "bar" FROM "user1";
Grant permissions on new owner:
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "user2";
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA "bar" TO "user2";
GRANT USAGE ON SCHEMA "bar" TO "user2"
Check existing permissions on tables:
SELECT grantor, grantee, table_schema, table_name,privilege_type FROM information_schema.table_privileges