From: | Zlatko Matić <zlatko(dot)matic1(at)sb(dot)t-com(dot)hr> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | creating users in groups, in 8.1 |
Date: | 2005-10-27 08:46:28 |
Message-ID: | 000201c5dadf$cfcd2f20$5cf38353@zlatkovyfkpgz6 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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";
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2005-10-27 09:10:51 | Re: improve 'where not exists' query.. |
Previous Message | David Garamond | 2005-10-27 05:30:28 | Re: Why database is corrupted after re-booting |