Re: Removing all users from a group

Hi David,

The correct syntax would probably be :

DELETE FROM pg_user
WHERE usesysid = ANY (pg_group.grolist)
AND pg_group.groname = 'somegroupname'

However, you won't be allowed to delete users this way.
The only recommended methods is to use the DROP USER command to remove
users...

One way to achieve this is to use a function, even if easier methods
probably exist...

CREATE OR REPLACE FUNCTION RemoveUsersFromGroup( text ) RETURNS int4 AS $$
DECLARE
_UserGroupName ALIAS FOR $1;

_Username RECORD;
_deleted int4;
BEGIN
_deleted := 0;
FOR _Username IN
SELECT usename
FROM pg_user, pg_group
WHERE usesysid = ANY (grolist)
AND groname = _UserGroupName
LOOP
_deleted := _deleted + 1;
EXECUTE( 'DROP USER ' || _Username.usename );
END LOOP;

RETURN _deleted;
END

$$ LANGUAGE 'plpgsql';

--SELECT RemoveUsersFromGroup( 'test' )

Regards,
Patrick

----------------------------------------------------------------------------
---------------
Patrick Fiche
email : patrick(dot)fiche(at)aqsacom(dot)com
tél : 01 69 29 36 18
----------------------------------------------------------------------------
---------------

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of David Sankel
Sent: mercredi 31 août 2005 12:30
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Removing all users from a group

Hello List,

I'm trying to delete all the users from a group and it seems as though there
isn't sufficient array functionality to do it.

The pg_group columns:
http://www.postgresql.org/docs/8.0/interactive/catalog-pg-group.html
<http://www.postgresql.org/docs/8.0/interactive/catalog-pg-group.html>

The pg_user columns:
http://www.postgresql.org/docs/8.0/interactive/view-pg-user.html
<http://www.postgresql.org/docs/8.0/interactive/view-pg-user.html>

After having a peek at the above, we know we can see all the users in a
group with this:

SELECT *
FROM pg_user, pg_group
WHERE usesysid = ANY (grolist)
AND groname = 'somegroupname';

"ANY" is a function that can tell if a value is in an array:
http://www.postgresql.org/docs/8.0/interactive/arrays.html#AEN5491
<http://www.postgresql.org/docs/8.0/interactive/arrays.html#AEN5491>

Okay, that select function worked fine, but if we want to delete we cannot
use a join (implicit by the ',') to help us out. So the following should
work:

DELETE FROM pg_user
WHERE usesysid = ANY ( SELECT grolist
FROM pg_group
WHERE groname = 'somegroupname' )

But, alas, it doesn't. Neither does any combination of IN and ANY. It seems
to me like this should work since the same syntax works if we weren't
talking about arrays.

So, how can we delete all users within a specified group? Is there a bug or
is the above code incorrect?

When testing the above delete function, I found it useful to substitute
"SELECT *" for "DELETE" to get non-destructive queries.

Thanks for any help,

David J. Sankel