Re: CREATE USER system privilege?

Поиск
Список
Период
Сортировка
Искать

Re: CREATE USER system privilege?

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Oli Sennhauser  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

Re: CREATE USER system privilege?

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Oli Sennhauser  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

CREATE USER system privilege?

От:
Oli Sennhauser <oli.sennhauser@bluewin.ch>
Дата:
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

Re: CREATE USER system privilege?

От:
Oli Sennhauser <oli.sennhauser@bluewin.ch>
Дата:
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

Re: CREATE USER system privilege?

От:
Oli Sennhauser <oli.sennhauser@bluewin.ch>
Дата:
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  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/
FAQ