Finally i'm using: EXECUTE 'CREATE USER '|| quote_ident(creds."userName") ||' WITH PASSWORD '|| quote_literal(creds."userPassword") || ' IN GROUP ta_users';
and it works perfect. Thanks! 2010/9/16 Igor Neyman <iney...@perceptron.com> > > > ------------------------------ > *From:* Asko Oja [mailto:asc...@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 <iney...@perceptron.com>wrote: > >> >> >> > -----Original Message----- >> > From: Tatarnikov Alexander [mailto:cank...@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. > > -- ------ С уважением, Татарников Александр
