Skip site navigation (1) Skip section navigation (2)

Re: Drop multiple roles

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Julien <julien(at)sgme(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Drop multiple roles
Date: 2011-09-13 17:17:23
Message-ID: 8444.1315934243@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Julien <julien(at)sgme(dot)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

In response to

pgsql-novice by date

Next:From: bradgDate: 2011-09-14 00:50:14
Subject: Re: PQisBusy() always busy
Previous:From: Carlo AscaniDate: 2011-09-13 16:59:24
Subject: Re: Drop multiple roles

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group