creating users in groups, in 8.1
От | Zlatko Matić |
---|---|
Тема | creating users in groups, in 8.1 |
Дата | |
Msg-id | 000201c5dadf$cfcd2f20$5cf38353@zlatkovyfkpgz6 обсуждение исходный текст |
Список | pgsql-general |
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";
В списке pgsql-general по дате отправления: