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