Thread: 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

The pg_user columns:
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

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

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' )

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of David Sankel
Sent: mercredi 31 août 2005 12:30
To: pgsql-general@postgresql.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

The pg_user columns:
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

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

On Wed, Aug 31, 2005 at 12:30:14 +0200,
  David Sankel <camior@gmail.com> wrote:
>
> 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.

In 8.1 you will be able to replace the second FROM with USING to do a join
in a DELETE. Currently you need to take advantage of the missing from
feature.

Thanks Patrick and Bruno for your replies,

The auto-added "FROM" feature is pretty slick for enabling JOINs within a DELETE. Allowing this to be explicit in 8.1 is going to be even better.

Since DELETEing from pg_users is an unsupported way to remove users, I am going to use the procedure in the end. It's a little-modified version of Patrick's code:

CREATE OR REPLACE FUNCTION removeUsersFromGroup( groupName name ) RETURNS int4 AS $$
DECLARE
  userRecord RECORD;
  numUsersDropped int4 := 0;
BEGIN
  FOR userRecord IN
   SELECT usename FROM pg_user,pg_group
   WHERE usesysid = ANY (grolist)
   AND groname = groupName
  LOOP
   numUsersDropped := numUsersDropped + 1;
   EXECUTE('DROP USER ' || userRecord.usename);
  END LOOP;
  RETURN numUsersDropped;
END
$$ LANGUAGE 'plpgsql';

Thanks again for the help,

David J. Sankel