pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
CREATE USER system privilege?
5+ messages / 2 participants
[nested] [flat]

* CREATE USER system privilege?
@ 2003-12-22 11:07  Oli Sennhauser <oli.sennhauser@bluewin.ch>
  0 siblings, 1 reply; 5+ messages in thread

From: Oli Sennhauser @ 2003-12-22 11:07 UTC (permalink / raw)
  To: 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.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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: CREATE USER system privilege?
@ 2003-12-22 15:15  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Oli Sennhauser <oli.sennhauser@bluewin.ch>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2003-12-22 15:15 UTC (permalink / raw)
  To: Oli Sennhauser <oli.sennhauser@bluewin.ch>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: CREATE USER system privilege?
@ 2003-12-28 09:42  Oli Sennhauser <oli.sennhauser@bluewin.ch>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Oli Sennhauser @ 2003-12-28 09:42 UTC (permalink / raw)
  To: pgsql-admin

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

Attachments:

  smime.p7s (application/x-pkcs7-signature, 3.4K) download

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: CREATE USER system privilege?
@ 2003-12-28 16:22  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Oli Sennhauser <oli.sennhauser@bluewin.ch>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2003-12-28 16:22 UTC (permalink / raw)
  To: Oli Sennhauser <oli.sennhauser@bluewin.ch>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: CREATE USER system privilege?
@ 2004-03-07 16:38  Oli Sennhauser <oli.sennhauser@bluewin.ch>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Oli Sennhauser @ 2004-03-07 16:38 UTC (permalink / raw)
  To: pgsql-admin

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/




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2004-03-07 16:38 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-22 11:07 CREATE USER system privilege? Oli Sennhauser <oli.sennhauser@bluewin.ch>
2003-12-22 15:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-12-28 09:42   ` Oli Sennhauser <oli.sennhauser@bluewin.ch>
2003-12-28 16:22     ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-07 16:38       ` Oli Sennhauser <oli.sennhauser@bluewin.ch>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox