confused by superuser-definition

zuhans@iname.com wrote:

hello,

i'm rather new to postgresql and am now standing in front of a big problem.

if i want to host my database on any provider out there, i don't now see
any chance to get my own right to insert new users into my db. because:
i'd only be able to do this, when i had the rights to "createuser", but
that would mean, that i'd have the superuser-rights for the whole db's
in "public" - if there is only this base-schema.

do i understand this right?

Well - what does the contract with your provider state? Do you get one
user or as many as you like? It's common to get one DB and one user.

if yes: what is a common approach to manage just own users if there is
the situation, that i have a changing amount of them?

Usually a simple wrapper - command-line or web-form. Checks you can only
add users to a given database.

btw: why isn't it possible to have (in a future release) a
createuser-right just for single databases and to have one
root/super-user for everydb?

No particular reason AFAIK. Someone was suggesting it on the hackers
list, but I'm not sure if anything came of it. If you are really
interested, check the archives on the hackers list and see what happened.

--
Richard Huxton
Archonet Ltd

zuhans@iname.com wrote:

hello,

i'm rather new to postgresql and am now standing in front of a big
problem.

if i want to host my database on any provider out there, i don't now
see any chance to get my own right to insert new users into my db.
because: i'd only be able to do this, when i had the rights to
"createuser", but that would mean, that i'd have the
superuser-rights for the whole db's in "public" - if there is only
this base-schema.

do i understand this right?

No.

-- Function: create_user(name)

CREATE OR REPLACE FUNCTION create_user(name)
RETURNS bool AS
'
DECLARE
PWD VARCHAR;
CMD VARCHAR;
BEGIN
PWD := \'INITIALPWD\';
IF EXISTS(SELECT * FROM pg_user WHERE usename = $1) THEN
RETURN FALSE;
END IF;
CMD := \'CREATE USER "\' || $1 || \'" WITH ENCRYPTED PASSWORD \' || PWD\';
EXECUTE CMD;
RETURN TRUE;
END;
'
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;

-- Note: "SECURITY DEFINER". This function was created by a user that
does have create user privileges.

--Berend Tober