Postgres Pro
Downloads
Home   >   mailing lists

Thread: Create user or role from inside a function?

Create user or role from inside a function?

From
"Dan"
Date:
Hey,

I am running PostgreSQL 8.1.4 and I want to create a user from inside a
function. Is this possible in 8.1?

Ive found quite a few references on google using EXECUTE, but this seems
relevant to earlier versions, not 8.1.

I have a function like this:

CREATE FUNCTION user_create (un varchar, uid bigint, pw varchar) RETURNS
VARCHAR LANGUAGE plpgsql AS '
  BEGIN
    EXECUTE "CREATE USER " || un || " WITH PASSWORD " || pw;

    RETURN un;
  END
';

Executing this function yields:

# SELECT user_create('bob',1234,'bobspassword');
ERROR:  column "CREATE USER " does not exist
CONTEXT:  SQL statement "SELECT  "CREATE USER " ||  $1  || " WITH PASSWORD
" ||  $2 "
PL/pgSQL function "user_create" line 2 at execute statement

Directly executing CREATE USER in this function also fails.. CREATE USER
$1 appears to be what is being executed.

Any tips would be appreciated.

Cheers,

Dan

Re: Create user or role from inside a function?

From
Roman Neuhauser
Date:
# ml@mutox.org / 2006-09-01 20:13:14 +1000:
> Hey,
>
> I am running PostgreSQL 8.1.4 and I want to create a user from inside a
> function. Is this possible in 8.1?
>
> Ive found quite a few references on google using EXECUTE, but this seems
> relevant to earlier versions, not 8.1.
>
> I have a function like this:
>
> CREATE FUNCTION user_create (un varchar, uid bigint, pw varchar) RETURNS
> VARCHAR LANGUAGE plpgsql AS '
>   BEGIN
>     EXECUTE "CREATE USER " || un || " WITH PASSWORD " || pw;
>
>     RETURN un;
>   END
> ';

    Double quotes can be used only for identifiers (table, column
    names, etc), you want to use single quotes for strings. Now, since
    you already are in a string (the function body), you need to escape
    the single quotes inside. In SQL this is done by doubling the quote
    character, IOW, by putting another single quote just before it:

    CREATE FUNCTION foo() ...
    AS '
    BEGIN
      EXECUTE ''CREATE USER '' || un || '' WITH PASSWORD '' || pw;
      RETURN un;
    END
    ';

    That assumes that the un and pw parameters are always passed already
    quoted, otherwise you'll get errors like this:

    test=# CREATE FUNCTION user_create (un varchar, uid bigint, pw varchar) RETURNS
    test-# VARCHAR LANGUAGE plpgsql AS '
    test'#   BEGIN
    test'# EXECUTE ''CREATE USER '' || un || '' WITH PASSWORD '' || pw;
    test'#     RETURN un;
    test'#   END
    test'# ';
    CREATE FUNCTION
    test=# select user_create('fubar', 0, 'pass');
    ERROR:  syntax error at or near "pass" at character 33
    QUERY:  CREATE USER fubar WITH PASSWORD pass
    CONTEXT:  PL/pgSQL function "user_create" line 2 at execute statement
    LINE 1: CREATE USER fubar WITH PASSWORD pass
                                            ^
    test=#

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE.             http://bash.org/?255991

Re: Create user or role from inside a function?

From
Martijn van Oosterhout
Date:
On Fri, Sep 01, 2006 at 08:13:14PM +1000, Dan wrote:
> Hey,
>
> I am running PostgreSQL 8.1.4 and I want to create a user from inside a
> function. Is this possible in 8.1?
>
> Ive found quite a few references on google using EXECUTE, but this seems
> relevant to earlier versions, not 8.1.

Does it make a difference if you use single quotes rather than double
(taking into account the need to escape then). They mean somewhat
different things...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
Attachment

Re: Create user or role from inside a function?

From
Karsten Hilbert
Date:
On Fri, Sep 01, 2006 at 08:13:14PM +1000, Dan wrote:

> I am running PostgreSQL 8.1.4 and I want to create a user from inside a
> function. Is this possible in 8.1?
...
> I have a function like this:
...
> Executing this function yields:
>
> # SELECT user_create('bob',1234,'bobspassword');
> ERROR:  column "CREATE USER " does not exist
> CONTEXT:  SQL statement "SELECT  "CREATE USER " ||  $1  || " WITH PASSWORD
> " ||  $2 "
> PL/pgSQL function "user_create" line 2 at execute statement
...
> Any tips would be appreciated.

http://cvs.savannah.gnu.org/viewcvs/gnumed/gnumed/server/sql/gmCreateUserFunction.sql?rev=1.5&root=gnumed&view=markup

This might help.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: Create user or role from inside a function?

From
Andreas Kretschmer
Date:
Dan <ml@mutox.org> schrieb:

> Hey,
>
> I am running PostgreSQL 8.1.4 and I want to create a user from inside a
> function. Is this possible in 8.1?
>
> Ive found quite a few references on google using EXECUTE, but this seems
> relevant to earlier versions, not 8.1.
>
> I have a function like this:
>
> CREATE FUNCTION user_create (un varchar, uid bigint, pw varchar) RETURNS
> VARCHAR LANGUAGE plpgsql AS '
>   BEGIN
>     EXECUTE "CREATE USER " || un || " WITH PASSWORD " || pw;
>
>     RETURN un;
>   END
> ';

This works:


CREATE or replace function user_create (un varchar, uid bigint, pw varchar) RETURNS VARCHAR  AS $$
BEGIN
        EXECUTE 'create user ' || un || ' with password ' || quote_literal(pw);
        return $1;
end;
$$ language plpgsql;


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: Create user or role from inside a function?

From
"Dan"
Date:
thanks!

I actually came to a similar solution after Roman's post.

Thanks all for the replies!


> Dan <ml@mutox.org> schrieb:
>
>> Hey,
>>
>> I am running PostgreSQL 8.1.4 and I want to create a user from inside a
>> function. Is this possible in 8.1?
>>
>> Ive found quite a few references on google using EXECUTE, but this seems
>> relevant to earlier versions, not 8.1.
>>
>> I have a function like this:
>>
>> CREATE FUNCTION user_create (un varchar, uid bigint, pw varchar) RETURNS
>> VARCHAR LANGUAGE plpgsql AS '
>>   BEGIN
>>     EXECUTE "CREATE USER " || un || " WITH PASSWORD " || pw;
>>
>>     RETURN un;
>>   END
>> ';
>
> This works:
>
>
> CREATE or replace function user_create (un varchar, uid bigint, pw
> varchar) RETURNS VARCHAR  AS $$
> BEGIN
>         EXECUTE 'create user ' || un || ' with password ' ||
> quote_literal(pw);
>         return $1;
> end;
> $$ language plpgsql;
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect.                              (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly."    (unknow)
> Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

Re: Create user or role from inside a function?

From
"Dan"
Date:
(Sending to the list as I accidently only replied to Roman with my earlier
reply)

Thanks for your assistance. This was a case of user error. To me the
examples I looked at used the double quote (") but on further inspection
they do indeed use double single quotes (').

The quote_literal function was also handy for this.

Thanks again!

Regards,

Dan


>     Double quotes can be used only for identifiers (table, column
>     names, etc), you want to use single quotes for strings. Now, since
>     you already are in a string (the function body), you need to escape
>     the single quotes inside. In SQL this is done by doubling the quote
>     character, IOW, by putting another single quote just before it: