Postgres Plus Advanced Server Oracle Compatibility Developer's Guide :
DROP USER -- remove a database user account
DROP USER name [ CASCADE ]
A user cannot be removed if it is still referenced in any database of the cluster; an error will be raised if so. Before dropping the user, you must drop all the objects it owns (or reassign their ownership) and revoke any privileges the user has been granted.
However, it is not necessary to remove role memberships involving the user; DROP USER automatically revokes any memberships of the target user in other roles, and of other roles in the target user. The other roles are not dropped nor otherwise affected.
Alternatively, if the only objects owned by the user belong within a schema that is owned by the user and has the same name as the user, the CASCADE option can be specified. In this case the issuer of the DROP USER name CASCADE command must be a superuser and the named user, the schema, and all objects within the schema will be deleted.
The name of the user to remove.
If specified, also drops the schema owned by, and with the same name as the user (and all objects owned by the user belonging to the schema) as long as no other dependencies on the user or the schema exist.
To drop a user account who owns no objects nor has been granted any privileges on other objects:
DROP USER john;
DROP USER john CASCADE;