Disclaimer:
Reassigning ownership can cause database deadlocking issues in high traffic / high locking environments. Also, ownership changes are tricky when BLOBs are being used in your Postgres objects owned by the user you want to delete. For these situations it may be worth just revoking any super user access and disable the user login for the user being retired than to completely clean it up. Leaving the old user as a member of the new user doesn’t harm anything other than added noise to the user list.
Figure out existing memberships with this query
SELECT
r.rolname AS role_name,
m.rolname AS member_name,
o.rolname AS grantor,
a.admin_option
FROM pg_auth_members a
JOIN pg_roles r on a.roleid = r.oid
JOIN pg_roles m on a.member = m.oid
JOIN pg_roles o on a.grantor = o.oid;
We are assuming the “replacement-user” is already a member of the “to-delete-user” in the following instructions
Login in with a user who is member of the user you are trying to delete. You can also grant yourself as a member of the user your currently logged in with is a super user:
GRANT "replacement-user" TO "my_user";
Revoke any super user permissions from the user being targeted for deletion
REVOKE "cloudsqlsuperuser" from "to-delete-user";
Run these statements against EVERY DATABASE (i.e., establish a new connection to each database) that the user might have touched. The drop user statement will fail otherwise with a vague message about how the user still owns objects Reference: -- --
REASSIGN OWNED BY "to-delete-user" TO "replacement-user";
SELECT relname FROM pg_class c JOIN pg_roles r ON r.oid = c.relowner WHERE r.rolname = 'to-delete-user'; -- verify ownership removed (should return zero rows)
DROP OWNED BY "to-delete-user"; -- means drop permissions to any existing resources as long as its executed right after REASSIGN OWNED BY "to-delete-user" TO "replacement-user";
Remove membership of old user from new user
REVOKE "to-delete-user" from "replacement-user";
Delete old user
DROP USER "to-delete-user";