CREATE USER system privilege?

[Oli Sennhauser]

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 [phone] or Mobile [phone]
e-Mail ***@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



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Tom Lane]

Post by Oli Sennhauser
I wanted to create a user who can create other users.

That is a superuser.

Post by Oli Sennhauser
--> 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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Post by Tom Lane
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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Oli Sennhauser]

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

Post by Tom Lane

Post by Tom Lane
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 [phone]
e-Mail ***@bluewin.ch
Website http://mypage.bluewin.ch/shinguz/PostgreSQL/


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org