Below is the syntax to delete a database user in postgres.
DROP USER rolename;
DROP ROLE rolename;
Both the above commands perform the same functionality. In Postgres, users are roles with login enabled. ‘USER’ is a synonym for ‘ROLE’ in Postgres.
The above command will fail if the user
(1) Owns any objects or
(2) Has privileges on any objects
To drop the user, you need to first
(1) Either drop all objects owned by the user or change ownership of all the objects owned by the user
(2)Revoke all privileges from the user
Run the below command to change ownership of all objects owned by ‘username’ to ‘postgres’ user.
REASSIGN OWNED BY username TO postgres;
Run the below command to revoke all privileges after reassigning the ownership to another user. If you don’t reassign the ownership, DROP OWNED command will drop all the objects as well. Run the below command with extra caution.
DROP OWNED BY username;
And finally you will be able to run the drop user command without any errors.
DROP USER username;
