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?
+2
A:
How about
DROP USER <username>
This is actually an alias for DROP ROLE. You shouldn't need to explicity drop any priveleges associated with that user, they will simply cease to be in effect once the user is dropped.
There may however be some ownership issues, in which case you'll need to drop or reassign those objects.
REASSIGN OWNED BY <olduser> TO <newuser>
or
DROP OWNED BY <olduser>
See the postgres docs for DROP ROLE
Tim Kane
2010-06-11 14:58:59
Doing: `CREATE TABLE foo(bar SERIAL); ALTER TABLE foo OWNER TO postgres; CREATE USER testuser; GRANT ALL ON foo TO testuser; DROP USER testuser`gave the error messages: `ERROR: role "testuser" cannot be dropped because some objects depend on it DETAIL: access to table foo`.However, `DROP OWNED BY testuser` did the trick, apparently Postgres considers grants to be droppable objects.
Sii
2010-06-14 10:39:09