creating users in groups, in 8.1

[Zlatko Matić <zlatko.matic1@xxxxxxxxxxx>]

Hello.
I had the following function in Postgres 8.0.4 for creation of users inside 
existing groups. Now I need to adjust it for new Roles system. What do I 
neeed to change?
Especially regarding: CMD := 'CREATE USER "' || l_username || '" WITH 
ENCRYPTED PASSWORD ''' || l_password || ''' IN GROUP "' || l_group || '" 
VALID UNTIL ''' || l_validity || '''';

Thanks in advance,

Zlatko

-- Function: alter_group(name, bool, name, varchar)
-- DROP FUNCTION alter_group(name, bool, name, "varchar");

CREATE OR REPLACE FUNCTION alter_group(name, bool, name, "varchar")
 RETURNS bool AS
$BODY$
DECLARE
 l_group ALIAS FOR $1;
 l_create_user ALIAS FOR $2;
 l_username ALIAS FOR $3;
 l_password ALIAS FOR $4;
 l_validity timestamp;
 CMD VARCHAR;
 MIN_SUPER_USER INTEGER := 1;
BEGIN
select into l_validity "rok_valjanosti"."rok_valjanosti" FROM ( SELECT 
min("rok_valjanosti"."rv_id") AS "rv_id", "rok_valjanosti"."rok_valjanosti" 
FROM "rok_valjanosti" GROUP BY "rok_valjanosti"."rv_id", 
"rok_valjanosti"."rok_valjanosti") sve;
IF (l_create_user NOTNULL) THEN
   IF (l_create_user) THEN
     CMD := 'CREATE USER "' || l_username || '" WITH ENCRYPTED PASSWORD ''' 
|| l_password || ''' IN GROUP "' || l_group || '" VALID UNTIL ''' || 
l_validity || '''';
     EXECUTE CMD;
   ELSE
     CMD := 'DROP USER "' || l_username || '"';
     EXECUTE CMD;
   END IF;
   IF (SELECT COUNT(*) FROM "user_group_view" WHERE "groupname" 
='{ADMINS}') < MIN_SUPER_USER THEN
     RAISE EXCEPTION 'At least % admin(s) in group ADMINS must be defined 
in order to create new user accounts.', MIN_SUPER_USER;
   END IF;
 END IF;
 RETURN TRUE;
END;

$BODY$
 LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
ALTER FUNCTION alter_group(name, bool, name, "varchar") OWNER TO matalab;
GRANT EXECUTE ON FUNCTION alter_group(name, bool, name, "varchar") TO 
matalab;
GRANT EXECUTE ON FUNCTION alter_group(name, bool, name, "varchar") TO GROUP 
"ADMINS"; 


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

              http://archives.postgresql.org