Re: Drop multiple roles

Julien <julien@sgme.com> writes:
> I would like to drop multiple roles via SQL.

> I can figure how to list my specific roles with a select on pg_roles
> but deleting these roles from pg_roles is probably not a good
> solution.

It would be safe if and only if you're entirely certain that the roles
own no objects and have no privileges granted anywhere.  But I wouldn't
risk it anyway --- for example, if you do it like that, you have no
protection against accidentally deleting all your superuser roles.

> Is there any clean way to do this with SQL or do I have to make a script ?

You need a script ... but keep in mind that in 9.0 and up, there's the
DO command, which allows an anonymous script.  So, something like
(untested)

    do $$
    declare rolename text;
    begin
      for rolename in select rolname from pg_roles where ...
        loop
          execute 'DROP ROLE ' || quote_identifier(rolename);
        end loop;
    end $$;

            regards, tom lane