Re: enebling regular user to create new users ? - Mailing list pgsql-general

From Zlatko Matić
Subject Re: enebling regular user to create new users ?
Date
Msg-id 42B93821.000001.03480@ZLATKO-58ZACZPV
Whole thread Raw
In response to Re: enebling regular user to create new users ?  (Berend Tober <btober@seaworthysys.com>)
List pgsql-general
Berend,
 
Thank you for the function code. It helped me a lot!
Regards,
 
Zlatko 
 
-------Original Message-------
 
Date: 06/15/05 18:08:22
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:

Previous
From: Együd Csaba (Freemail)
Date:
Subject: Re: Making the DB secure
Next
From: "Sim Zacks"
Date:
Subject: Re: is this a bug ?