I'm trying to make restricted DB users for the app I'm working on, and I want to drop the Postgres database user I'm using for experimenting. Is there any way to drop the user without having to revoke all his rights manually first, or revoke all the grants a user has?
How about
DROP USER <username>
This is actually an alias for DROP ROLE.
You have to explicity drop any privileges associated with that user, also to move its ownership to other roles (or drop the object).
This is best achieved by
REASSIGN OWNED BY <olduser> TO <newuser>
and
DROP OWNED BY <olduser>
The latter will remove any privileges granted to the user.
See the postgres docs for DROP ROLE and the more detailed description of this.
Addition:
Apparently, trying to drop a user by using the commands mentioned here will only work if you are executing them while being connected to the same database that the original GRANTS were made from, as discussed here: