enebling regular user to create new users ?

Zlatko Matić wrote:

Hi. 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 ?

Neither is possible directly. (B) means they're not a superuser and (A)
means they are. You could create a function marked "SECURITY DEFINER" as
a super-user and have that function create users on behalf of regular
users though.

--
Richard Huxton
Archonet Ltd

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

Richard Huxton <dev@archonet.com> writes:

Zlatko Mati� wrote:

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 ?

Neither is possible directly. (B) means they're not a superuser and (A)
means they are.

There has been some talk of separating the power to create new users
from the power of being superuser (although presumably only a superuser
should be allowed to create new superusers). If the planned pg_role
rewrite gets submitted before the 8.1 feature freeze, I might look at
adding that frammish into it.

regards, tom lane

On Wed, 2005-06-15 at 12:50 -0400, Tom Lane wrote:

I might look at
adding that frammish into it

Frammish?

Tom Lane wrote:

Richard Huxton <dev@archonet.com> writes:

Zlatko Matiæ wrote:

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 ?

Neither is possible directly. (B) means they're not a superuser and (A)
means they are.

There has been some talk of separating the power to create new users
from the power of being superuser (although presumably only a superuser
should be allowed to create new superusers). If the planned pg_role
rewrite gets submitted before the 8.1 feature freeze, I might look at
adding that frammish into it.

Did I see talk of per-database users too? That would be a sensible
dividing-line I suppose - you could have complete control of "your"
database and who can access it without interfering with anyone else.

--
Richard Huxton
Archonet Ltd

Per-database user, right, that's what I need...hope it will be included in
future releases...
Separating the power to create new users from the power of being superuser,
also. It is very important.
Greetings,

Zlatko

----- Original Message -----
From: "Richard Huxton" <dev@archonet.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "Zlatko Matić" <zlatko.matic1@sb.t-com.hr>;
<pgsql-general@postgresql.org>
Sent: Wednesday, June 15, 2005 9:35 PM
Subject: Re: [GENERAL] enebling regular user to create new users ?

Tom Lane wrote:

Richard Huxton <dev@archonet.com> writes:

Zlatko Matiæ wrote:

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 ?

Neither is possible directly. (B) means they're not a superuser and (A)
means they are.

There has been some talk of separating the power to create new users
from the power of being superuser (although presumably only a superuser
should be allowed to create new superusers). If the planned pg_role
rewrite gets submitted before the 8.1 feature freeze, I might look at
adding that frammish into it.

Did I see talk of per-database users too? That would be a sensible
dividing-line I suppose - you could have complete control of "your"
database and who can access it without interfering with anyone else.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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ć
Cc: pgsql-general@postgresql.org
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.,