Thread: confused by superuser-definition

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?

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?

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?

greetings from today very sunny autria
hans

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