Search Postgresql Archives

Re: enebling regular user to create new users ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux