Re: 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.,