Thread: Use "CREATE USER" in plpgsql function

Hello!<br /><br />I have function wich check user credentials and if test passed function must create new user with
generatedusername and password.<br /><br />Language is plpgsql.<br clear="all" /><br />For example:<br /><br />....<br
/>DECLAREcreds RECORD;<br /> ...<br />SELECT * INTO creds FROM ...<br /><br />creds is Record with fields
userName(VARCHAR)and userPassword(VARCHAR)<br /><br />so when i use CREATE USER creds."userName" WITH PASSWORD
creds."userPassword"<br/><br />i get an error, because creds."userName" is VARCHAR and thus when function runs it will
belook like this:<br />CREATE USER 'user_1' <br />but right command is <br />"CREATE USER user_1" OR " CREATE USER
"user_1""<br /><br />so question is how to "unembrace" this parameter (i mean creds."userName")?<br /><br />Thanks<br
/>--<br />------<br />Alexander<br /> 

Hi,

On 15 September 2010 08:05, Tatarnikov Alexander <cankrus@gmail.com> wrote:
> Hello!
>
> I have function wich check user credentials and if test passed function must
> create new user with generated username and password.
>
> Language is plpgsql.
>
> so question is how to "unembrace" this parameter (i mean creds."userName")?

Show the whole function please.

>
> Thanks
> --
> ------
> Alexander
>



-- 
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802

2010/9/15 Tatarnikov Alexander <cankrus@gmail.com>:
> Thanks for response!
>
> Here is function
>
>             CREATE USER creds."userName" WITH PASSWORD creds."userPassword"
> IN GROUP ta_users; - there is error occured

You can not use variables for non-data entities. Use the dynamic SQL instead:

EXECUTE 'CREATE USER ' || creds."userName" ||   ' WITH PASSWORD ' || creds."userPassword" ||   ' IN GROUP ' ||
ta_users;

Read more here http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

p.s. I suggest you to read it with care
http://wiki.postgresql.org/wiki/Guide_to_reporting_problems before
reporting a problem next time.

>
> DECLARE cred VARCHAR;
> DECLARE passed BOOLEAN;
> DECLARE creds RECORD;
> BEGIN
>         SELECT  (ta_base.user_accounts."password" = $2) INTO passed
>         FROM    ta_base.user_accounts
>         WHERE   ta_base.user_accounts.user_id = $1;
>         if (passed) THEN
>             SELECT * INTO creds FROM "ta_base"."credTable" WHERE
> "ta_base"."credTable"."inUse"=FALSE ORDER BY random() LIMIT 1;
>             INSERT INTO ta_base.logins VALUES (creds."userName",
> creds."userPassword", current_timestamp(2), NULL, NULL, $1, TRUE);
>             UPDATE "ta_base"."credTable" SET "inUse"=TRUE WHERE
> "credId"=creds."credId";
>             CREATE USER creds."userName" WITH PASSWORD creds."userPassword"
> IN GROUP ta_users; - there is error occured
>             cred:=N'pass';
>         else
>             cred:=N'failed';
>         end if;
>         return cred;
> END;
>
> 2010/9/15 Sergey Konoplev <gray.ru@gmail.com>
>>
>> Hi,
>>
>> On 15 September 2010 08:05, Tatarnikov Alexander <cankrus@gmail.com>
>> wrote:
>> > Hello!
>> >
>> > I have function wich check user credentials and if test passed function
>> > must
>> > create new user with generated username and password.
>> >
>> > Language is plpgsql.
>> >
>> > so question is how to "unembrace" this parameter (i mean
>> > creds."userName")?
>>
>> Show the whole function please.
>>
>> >
>> > Thanks
>> > --
>> > ------
>> > Alexander
>> >
>>
>>
>>
>> --
>> Sergey Konoplev
>>
>> Blog: http://gray-hemp.blogspot.com /
>> Linkedin: http://ru.linkedin.com/in/grayhemp /
>> JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802
>
>
>
> --
> ------
> С уважением,
> Татарников Александр
>



--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802

> -----Original Message-----
> From: Tatarnikov Alexander [mailto:cankrus@gmail.com]
> Sent: Wednesday, September 15, 2010 12:05 AM
> To: pgsql-sql@postgresql.org
> Subject: Use "CREATE USER" in plpgsql function
>
> Hello!
>
> I have function wich check user credentials and if test
> passed function must create new user with generated username
> and password.
>
> Language is plpgsql.
>
> For example:
>
> ....
> DECLARE creds RECORD;
> ...
> SELECT * INTO creds FROM ...
>
> creds is Record with fields userName(VARCHAR) and
> userPassword(VARCHAR)
>
> so when i use CREATE USER creds."userName" WITH PASSWORD
> creds."userPassword"
>
> i get an error, because creds."userName" is VARCHAR and thus
> when function runs it will be look like this:
> CREATE USER 'user_1'
> but right command is
> "CREATE USER user_1" OR " CREATE USER "user_1" "
>
> so question is how to "unembrace" this parameter (i mean
> creds."userName")?
>
> Thanks
> --
> ------
> Alexander
>

It is called "dynamic" sql:

EXECUTE 'CREATE USER creds.' || userName || ' WITH PASSWORD creds.' ||
userPassword;


Read about "dynamic" sql in PG docs:

http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html

Specifically: "38.5.4. Executing Dynamic Commands"

Regards,
Igor Neyman

> -----Original Message-----
> From: Asko Oja [mailto:ascoja@gmail.com]
> Sent: Wednesday, September 15, 2010 2:29 PM
> To: Igor Neyman
> Cc: Tatarnikov Alexander; pgsql-sql@postgresql.org
> Subject: Re: [SQL] Use "CREATE USER" in plpgsql function -
> Found word(s) list error in the Text body
>
> And dynamic SQL leads easily to SQL injection so quoting is
> required there.
>
>         execute 'create user ' || quote_ident(i_username) ||
> ' password ' || quote_literal(i_password);
>
>
> On Wed, Sep 15, 2010 at 5:26 PM, Igor Neyman
> <ineyman@perceptron.com> wrote:
>

That's too "generic".
I was answering specific question.

Now, yes, dynamic sql could be used for SQL injection, if not used
carefully.
But, it exists for a reason.

And in this particular case userName and userPassword retrieved from a
table.
So, care should be taken (appropriate checks to be done) when these
values inserted into the table.

Btw., do you have another answer to OP question?

Regards,
Igor Neyman

---

From: Asko Oja [mailto:ascoja@gmail.com]
Sent: Wednesday, September 15, 2010 2:29 PM
To: Igor Neyman
Cc: Tatarnikov Alexander; pgsql-sql@postgresql.org
Subject: Re: [SQL] Use "CREATE USER" in plpgsql function - Found word(s) list error in the Text body

And dynamic SQL leads easily to SQL injection so quoting is required there.

        execute 'create user ' || quote_ident(i_username) || ' password ' || quote_literal(i_password);

On Wed, Sep 15, 2010 at 5:26 PM, Igor Neyman <ineyman@perceptron.com> wrote:

> -----Original Message-----
> From: Tatarnikov Alexander [mailto:cankrus@gmail.com]
> Sent: Wednesday, September 15, 2010 12:05 AM
> To: pgsql-sql@postgresql.org
> Subject: Use "CREATE USER" in plpgsql function
>
> Hello!
>
> I have function wich check user credentials and if test
> passed function must create new user with generated username
> and password.
>
> Language is plpgsql.
>
> For example:
>
> ....
> DECLARE creds RECORD;
> ...
> SELECT * INTO creds FROM ...
>
> creds is Record with fields userName(VARCHAR) and
> userPassword(VARCHAR)
>
> so when i use CREATE USER creds."userName" WITH PASSWORD
> creds."userPassword"
>
> i get an error, because creds."userName" is VARCHAR and thus
> when function runs it will be look like this:
> CREATE USER 'user_1'
> but right command is
> "CREATE USER user_1" OR " CREATE USER "user_1" "
>
> so question is how to "unembrace" this parameter (i mean
> creds."userName")?
>
> Thanks
> --
> ------
> Alexander
>

It is called "dynamic" sql:

EXECUTE 'CREATE USER creds.' || userName || ' WITH PASSWORD creds.' ||
userPassword;


Read about "dynamic" sql in PG docs:

http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html

Specifically: "38.5.4. Executing Dynamic Commands"

Regards,
Igor Neyman

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[I.N.] Opps.

Missed  quote_ident() in your message, sorry.