pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCREATE USER system privilege?
5+ messages / 2 participants
[nested] [flat]
* CREATE USER system privilege?
@ 2003-12-22 11:07 Oli Sennhauser <oli.sennhauser@bluewin.ch>
2003-12-22 15:15 ` Re: CREATE USER system privilege? Tom Lane <tgl@sss.pgh.pa.us>
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 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 ` Re: CREATE USER system privilege? 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-22 11:07 CREATE USER system privilege? Oli Sennhauser <oli.sennhauser@bluewin.ch>
2003-12-22 15:15 ` Re: CREATE USER system privilege? Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-12-28 09:42 ` Oli Sennhauser <oli.sennhauser@bluewin.ch>
2003-12-28 16:22 ` Re: CREATE USER system privilege? 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-22 11:07 CREATE USER system privilege? Oli Sennhauser <oli.sennhauser@bluewin.ch>
2003-12-22 15:15 ` Re: CREATE USER system privilege? Tom Lane <tgl@sss.pgh.pa.us>
2003-12-28 09:42 ` Re: CREATE USER system privilege? Oli Sennhauser <oli.sennhauser@bluewin.ch>
@ 2003-12-28 16:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-07 16:38 ` Re: CREATE USER system privilege? 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?
2003-12-22 11:07 CREATE USER system privilege? Oli Sennhauser <oli.sennhauser@bluewin.ch>
2003-12-22 15:15 ` Re: CREATE USER system privilege? Tom Lane <tgl@sss.pgh.pa.us>
2003-12-28 09:42 ` Re: CREATE USER system privilege? Oli Sennhauser <oli.sennhauser@bluewin.ch>
2003-12-28 16:22 ` Re: CREATE USER system privilege? Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-03-07 16:38 ` Oli Sennhauser <oli.sennhauser@bluewin.ch>
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