CREATE USER system privilege?

From: Oli Sennhauser <oli(dot)sennhauser(at)bluewin(dot)ch>
To: pgsql-admin(at)postgresql(dot)org
Subject: CREATE USER system privilege?
Date: 2003-12-22 11:07:35
Message-ID: 3FE6D077.7000403@bluewin.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi

I wanted to create a user who can create other users. But this causes
some problems:

* I did not find any CREATE USER system privilege. So I have to create
the first user as Superuser? What I do not want!
--> Is this correct?

* So I decided to write a function owned by a superuser which can be
called by this first user (see below).
--> Why does CREATE USER $1 fail??? CREATE USER xyz works. Is this a bug
or did I miss something?

* Is there realy no other way to execute a stored procedure than SELECT
function();
--> I was looking about half an hours into the documents but did not
find something like: exec function();

Thanks for tipps
Oli

CREATE OR REPLACE FUNCTION
public.create_user(pg_catalog.pg_user.usename%TYPE)
RETURNS varchar AS '
DECLARE
ret VARCHAR;
BEGIN
CREATE USER oli;
SELECT INTO ret $1;
CREATE USER $1;
RETURN ret;
END;
' LANGUAGE plpgsql
SECURITY DEFINER;

-------------------------------------------------------

Oli Sennhauser
Database-Engineer (Oracle & PostgreSQL)
Rebenweg 6
CH - 8610 Uster / Switzerland

Phone (+41) 1 940 24 82 or Mobile (+41) 79 450 49 14
e-Mail oli(dot)sennhauser(at)bluewin(dot)ch
Website http://mypage.bluewin.ch/shinguz/PostgreSQL/

Secure (signed/encrypted) e-Mail with a Free Personal SwissSign ID: http://www.swisssign.ch

Import the SwissSign Root Certificate: http://swisssign.net/cgi-bin/trust/import

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Oli Sennhauser 2003-12-22 11:20:24 Opinion wanted: Default select rights for users via public schema
Previous Message Holzheu 2003-12-22 08:20:42 Re: plperl does not build on RH7.1