Re: enebling regular user to create new users ? - Mailing list pgsql-general
Berend, Thank you for the function code. It helped me a lot! Regards, Zlatko -------Original Message------- From: Berend Tober Date: 06/15/05 18:08:22 To: Zlatko Matić Subject: Re: [GENERAL] enebling regular user to create new users ? Zlatko Matić wrote: > I know that superusers are allowed to do everything on the database, > but I consider this as dangerous. I want to have some user group with > rights of creating new users and giving them some authorizations, but > without such wide power as superusers have. So, > I was thinking about two possible scenarios: > a) to allow regular users to create new users > b) to restrict superuser's permissions > > What is possible and what do you suggest ? CREATE OR REPLACE FUNCTION create_user(name) RETURNS bool AS ' DECLARE PWD VARCHAR; CMD VARCHAR; BEGIN PWD := \'\'\'\' || get_random_string(8) || \'\'\'\'; IF EXISTS(SELECT 1 FROM pg_user WHERE usename = $1) THEN RETURN FALSE; END IF; CMD := \'CREATE USER "\' || $1 || \'" WITH ENCRYPTED PASSWORD \' || PWD || \' IN GROUP gen_user\'; EXECUTE CMD; RETURN TRUE; END; ' LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER; REVOKE ALL ON FUNCTION create_user(name) FROM public; GRANT EXECUTE ON FUNCTION create_user(name) TO GROUP pseudo_dba; CREATE OR REPLACE FUNCTION alter_group(name, bool, name) RETURNS bool AS ' DECLARE l_group ALIAS FOR $1; l_create_user ALIAS FOR $2; l_username ALIAS FOR $3; CMD VARCHAR; MIN_SUPER_USER INTEGER := 1; BEGIN IF (l_create_user NOTNULL) THEN IF (l_create_user) THEN PERFORM create_user(l_username); CMD := \'ALTER GROUP \' || l_group || \' ADD USER "\' || l_username || \'"\'; EXECUTE CMD; ELSIF (l_group = \'gen_user\') THEN PERFORM drop_user(l_username); ELSE CMD := \'ALTER GROUP \' || l_group || \' DROP USER "\' || l_username || \'"\'; EXECUTE CMD; END IF; IF (SELECT COUNT(*) FROM group_members WHERE groname = \'pseudo_dba\') < MIN_SUPER_USER THEN RAISE EXCEPTION \'At least % super user(s) must be defined in order to create new user accounts.\', MIN_SUPER_USER; END IF; END IF; RETURN TRUE; END; ' LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER; REVOKE ALL ON FUNCTION alter_group(name, bool, name) FROM public; GRANT EXECUTE ON FUNCTION alter_group(name, bool, name) TO GROUP pseudo_dba; -- etc., etc., etc., | |||
Attachment
pgsql-general by date: