Обсуждение: CREATE USER system privilege?

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.sennhauser@bluewin.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

Oli Sennhauser <oli.sennhauser@bluewin.ch> writes:
> I wanted to create a user who can create other users.

That is a superuser.

> --> Why does CREATE USER $1 fail???

You need to use EXECUTE to construct this query as a string.
Utility statements in general don't handle parameters.

            regards, tom lane

Hello Tom

>You need to use EXECUTE to construct this query as a string.
>Utility statements in general don't handle parameters.
>
>
Ok. I tried out:

PREPARE create_user(varchar) AS SELECT $1;
ok
EXECUTE create_user('otto');
ok
DEALLOCATE create_user;
ok
PREPARE create_user(varchar) AS CREATE USER $1;
ERROR:  syntax error at or near "CREATE" at character 33

--> Then I was looking into the manual and found that PREPARE/EXECUTE
only works with SIUD :-(

Facit: pgplsql does not work for my problem, execute does not work problem.

Is there any other way to enable a user to create other users than
granting superuser rights?
In my opinion it is a little much privileges to just create new users.

Proposal for a next release: CREATE USER system privilege. Where do I
have to request for such a proposal?

Regards Oli

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

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.sennhauser@bluewin.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

Oli Sennhauser <oli.sennhauser@bluewin.ch> writes:
>> You need to use EXECUTE to construct this query as a string.

> Facit: pgplsql does not work for my problem, execute does not work problem.

I didn't think I would have to spell it out in gory detail ...

regression=# create or replace function cruser(text) returns void as '
regression'# begin
regression'#   execute ''CREATE USER '' || quote_ident($1);
regression'#   return;
regression'# end' language plpgsql;
CREATE FUNCTION
regression=# select cruser('foo');
 cruser
--------

(1 row)

regression=# select cruser('bar');
 cruser
--------

(1 row)

regression=# select usename from pg_user;
        usename
-----------------------
 postgres
 pleb
 foo
 bar
(5 rows)

regression=#

            regards, tom lane

Hi admins

Long ago I was asking about system privilege "create user" and
procedures to create users without superuser rights.
Now I have finished some pgplsql-Procedures for granting this rights to
a normal operator.

You can find them on
http://mypage.bluewin.ch/shinguz/PostgreSQL/skripts/f_create_user.tar.gz

Regards Oli


Tom Lane wrote:

>Oli Sennhauser <oli.sennhauser@bluewin.ch> writes:
>
>
>>>You need to use EXECUTE to construct this query as a string.
>>>
>>>
>
>
>
>>Facit: pgplsql does not work for my problem, execute does not work problem.
>>
>>
>
>I didn't think I would have to spell it out in gory detail ...
>
>regression=# create or replace function cruser(text) returns void as '
>regression'# begin
>regression'#   execute ''CREATE USER '' || quote_ident($1);
>regression'#   return;
>regression'# end' language plpgsql;
>CREATE FUNCTION
>regression=# select cruser('foo');
> cruser
>--------
>
>(1 row)
>
>regression=# select cruser('bar');
> cruser
>--------
>
>(1 row)
>
>regression=# select usename from pg_user;
>        usename
>-----------------------
> postgres
> pleb
> foo
> bar
>(5 rows)
>
>regression=#
>
>            regards, tom lane
>
>
>


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

Haben Sie Ihre Firma schon im FOSS-Directory (www.foss-directory.ch) eingetragen?

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

Phone (+41) 1 940 24 82
e-Mail oli.sennhauser@bluewin.ch
Website http://mypage.bluewin.ch/shinguz/PostgreSQL/